Archive for the ‘Formulas’ Category
Monday, July 14th, 2008 401 views
Sometimes I have a list of values and want to average them, while excluding zeros or negative numbers, so I use the following popular array formula. "MyRange" is a named range of cells on a worksheet. I discourage the use of magic numbers, but they are included below for visual ...
Posted in Excel, Formulas | No Comments »
Wednesday, February 20th, 2008 118 views
Here is an update to the code I have posted on the site at
Format SSN
I realized that the last part of the formula doesn't check for dashes. Here is the updated formula:
=IF(AND(LEN(A4)>6, LEN(A4)9, NOT(ISERROR(FIND("-",A4)))),A4,LEFT(A4,3)&"-"&MID(A4,4,2)&"-"&RIGHT(A4,4)))
Notice the addition of NOT(ISERROR(FIND("-",A4))) in the second IF formula.
If the formula already has dashes in it, ...
Posted in Excel, Formulas | No Comments »
Monday, January 28th, 2008 21 views
This formula, entered as an array (Ctrl-Shift-Enter) in a single cell, will show you if there any duplicate entries in a given range (in this case, A1:C100). It is wrapped in an IF function to provide a friendly message; a kind of in-cell error handling, if you will. I usually ...
Posted in Excel, Formulas | No Comments »
Saturday, January 26th, 2008 29 views
My offer to teach Excel training classes at my office has been accepted. The class is tentatively titled "Advanced Topics in Excel" and will cover the following topics (among others TBD). I will also be including code and formulas from this site.
Counting/Filtering:
SUMPRODUCT, COUNTIF, SUMIF
Working with dates:
INT/MOD
Text functions:
RIGHT, LEFT, MID
Misc functions:
HYPERLINK
And ...
Posted in Excel, Formulas, Tutorial, VBA | 1 Comment »
Wednesday, January 23rd, 2008 187 views
This formula will show the difference (in hours) between two cells with date/time values.
=IF(INT(B2)-INT(A2)<1,24*(mod(b2,1)-mod(a2,1)),((int(b2)-int(a2))*24)+24*(mod(b2,1)-mod(a2,1)))
Press Ctrl-1 to format the cell, on the Number tab choose "Custom," enter this format:
####.0# "hours"
The cell will show the number of hours passed between both dates/times, neatly formatted with a custom format displaying the word "hours" ...
Posted in Excel, Formulas | 2 Comments »
