MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.

=========