IF function - Placing a value in a cell from the last cell with a value
Posted by Jared M. Nunes on March 06, 2001 9:02 PM
I've search the archives here & haven't found exactly what I'm looking for. Here's what I need to learn:
I have three cells (A1, B1, & C1) which may contain a value of 0, 1, 2, 3, 4 or 5, or they may be blank (empty). I need the cell D1 to be equal to C1 if it is not blank. If C1 is blank, then D1 needs to equal B1. If B1 is blank, D1 must equal A1. I got as far as =IF(C1>=0)C1,B1 but that didn't completely work and I can't figure out what to do if B1 is not >=0. Any help would be appreciated.
Thanks.
Posted by Aladin Akyurek on March 06, 2001 9:57 PM
Try:
=IF(ISNUMBER(C1),C1,IF(ISNUMBER(B1),B1,A1))
Aladin
Posted by Carl B on March 06, 2001 9:59 PM
Nested If statement.
=IF(C1>0,C1,IF(B1>0,B1,A1))
Posted by Aladin Akyurek on March 06, 2001 10:11 PM
Carl: Jared is not trying to disregard 0's, just the blanks.
Aladin
Posted by Mark W. on March 07, 2001 5:48 AM
=RIGHT(CONCATENATE(A1,B1,C1))+0
Posted by Mark W. on March 07, 2001 5:54 AM
Or...if you prefer...
=RIGHT(A1&B1&C1)+0
Posted by Bruce on March 07, 2001 12:06 PM
Re: Or...if you prefer...
Mark,
I'm impressed by both of your solutions. But, I don't understand the use of the "+0" at the end of each statement. Your solutions seem to work well without the "+0". Can you expalin to me why you added the "+0"?
Posted by Mark W. on March 07, 2001 12:35 PM
Re: Or...if you prefer...
The concatenation operators produce a text string.
Using a mathmatical operator with a text represent-
ation of a numeric value, coerces it back into a
numeric value. I could have used the VALUE()
function to accomplish the same thing, but +0 is
more abbreviated. Since 0 is the identity value
for addition it doesn't change the results.
=(6=6)+0 will return 1 because a boolean value
can be coerced into a 1 or 0. I prefer to use
this expression instead of =IF(6=6,1,0). It's
primarily just a matter of style.
Posted by Mark W. on March 07, 2001 12:40 PM
...By the way...
Coercion works for date values as well.
="1/1/2001"+0 is the same as =DATE(2001,1,1).
Posted by Bruce on March 07, 2001 12:44 PM
Re: Or...if you prefer...
Thanks Mark! I'm learning more about Excel from you all the time.
Posted by Mark W. on March 07, 2001 12:48 PM
...One more comment...
Coercing a date value such as ="1/1/2001"+0 is
especially useful if you have an array of such
values (e.g., ={"1/1/01","1/2/01","1/3/01"}+0)
and want to convert them all in one fell swoop!
Posted by Jared M. Nunes on March 07, 2001 1:33 PM
Re: IF function - Neither of Mark's suggestions worked
I tried both options and the cell ends up with #VALUE! in it. Did I miss something?
Posted by Jared M. Nunes on March 07, 2001 1:42 PM
This is the one that seems to work the best.
Thanks everyone
Jared
Posted by Jared M. Nunes on March 07, 2001 1:45 PM
OOPS - my mistake
Sorry. This works OK. I forgot to make the Row numbers match (I was on row 2, not row 1)
Now I have 2 or 3 options. Thanks again.
Jared
