Formulas

All about formulas and functions

Figure Those Pesky Fractions in Excel

You’ve probably seen a conversion chart that takes US fractions and converts them to a decimal equivalent. However, when you need to know the decimal equivalent of 7/16 or 5/32, there’s no chart to be found. And if you’re working on a project that requires more than a few conversions, using a calculator or conversion …

Read more

Stock Price Formula in a Spreadsheet

Sadly, Excel doesn’t have a stock price formula, but Google Docs has just the thing: a GoogleFinance Function. It updates a stock price when you open a Google Docs spreadsheet. The GoogleFinance Function has two arguments, symbol and attribute. The symbol argument represents the stock symbol for company or mutual fund. If the attribute argument …

Read more

The TEXT Function in Excel

The TEXT Function converts a value to text and lets you specify the display formatting by using special format strings. I often use this function to show the day of the week beside a column of dates. Another common use is to format a number in a concatenated text string.

Read more

The WEEKDAY Function in Excel

Excel 2010 has new return_type arguments for the WEEKDAY Function. How useful they are is debatable, but you never know when you might come across a particular need. Excel Weekday Function The Excel WEEKDAY Function in has two arguments: serial_number and return_type. The serial_number argument is simply a number that represents a date. The return_type argument …

Read more

Convert Seconds to a Time Format in Excel

The last several posts have shown how to convert and aggregate a given number of seconds that represent song duration. A simple example because song duration never exceeded one hour. But what if your data has values greater than 3600 seconds? How would you convert those seconds into an hour:minute:second format? Luckily, the previously mentioned …

Read more

Using the SUM Function for a Running Total in Excel

I’ve exported an iTunes playlist that shows the number of seconds per song and converted those seconds to a minutes:seconds format as you can see in column N in the spreadsheet below. I now want a running summary in column O to see the total time after each song. The SUM Function works perfectly when …

Read more

Putting the TIME, INT and MOD Functions to Work in Excel

Converting seconds into a minutes:seconds format is rather simple once you know the component parts of the formula. We’ll use three functions. (click to review prior post) TIME(hour,minute,second) INT(number) MOD(number,divisor) For the TIME Function, the three arguments are: hour = 0 minute = INT(number) second = MOD(number, divisor) Which leaves us with: TIME(0, INT(number), MOD(number,divisor)) …

Read more

The TIME Function in Excel

I’ve been looking at converting a number of seconds into a minutes:seconds format. My example is converting 274 seconds by using the INT Function to extract 4 minutes and the MOD Function to extract 34 seconds. In this post I’m using those values inside the TIME Function. The Time Function has three arguments: Hour, Minute, …

Read more

The MOD Function in Excel

The Excel MOD Function simply returns a remainder, or fractional part, after a number has been divided. To understand the MOD Function better, a look at how the INT Function can be used may provide some insight. First, the calculation example is 274 seconds converted to 4:34.  The INT Function was used in a previous post, …

Read more