function in Excel
Posted by Sheldon Margulies on January 17, 2002 9:04 AM
I want to write a function for a spreadsheet:
if C2 is blank, then leave D2 blank. If C2 is not blank, then D2=(C2-B2)/30. How do I write this?
Posted by Juan Pablo G. on January 17, 2002 9:09 AM
In D2 put this formula
=IF(LEN(C2),(C2-B2)/30,"")
Juan Pablo G.
Posted by Horacio on January 17, 2002 9:17 AM
You can try this... makes more sense for starters.
Write this formula in D2 --> =IF(C2="","",(C2-B2)/30)
Posted by Mark W. on January 17, 2002 9:32 AM
Technically, you can't enter a formula into D2 and leave it blank...
By definition a blank cell is an empty cell, and
by their nature all functions return "something"
(a value). The recommendations below don't leave
cell D2 blank -- they return the null text string
value, "". The practice of mixing data types --
numeric and text -- in a given cell or column can
cause problems later when the cell is used in an
arithmetic expression. That's because =""+1 will
produce the #VALUE! error. I'd strongly recommend
that if C2 is blank you return a 0 in D2 [using
the formula, =IF(ISBLANK(C2),0,(C2-B2)/30)] and
then apply the custom format 0;-0 to D2 so the
zero won't be displayed -- in effect, making D2
"appear" blank.
Posted by Aladin Akyurek on January 17, 2002 12:12 PM
Re: Technically, you can't enter a formula into D2 and leave it blank...
I agree if one consistently applies the rule of never returning a "" using a formula (I leave aside returning #N/A, e.g., by lookup functions). When this rule is in force, we can simplify the formula:
=IF(C2,(C2-B2)/30,0)
or depending on the intent:
=IF(AND(B2,C2),(C2-B2)/30,0)
If the rule is NOT in force, I'd be conservative and suggest (as Juan did)
=IF(LEN(C2),(C2-B2)/30,x)
or depending on the intent again:
=IF(AND(LEN(B2),LEN(C2)),(C2-B2)/30,x)
where x is either that dreaded "" or 0, continuing the "practice of mixing data types" in accordance what we know: people keep "mixing data types".
BTW: The LEN or ="" test survives a formula returned "", ISBLANK does not.
=========
