MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Nesting IF Statements


Posted by Jan on January 10, 2002 11:52 AM

i have pasted this formula into a cell

=IF(D3=0,D4,IF(D4=0,D5,IF(D5=0,D6)))

it's supposed to check each box above it for a value.

if d3 is 0 insert the value for d4
if d4 is 0 insert the value for d5
if d5 is 0 insert the value for d6

what is wrong with my code? is there an easier way to do this?

thanks again


Posted by Joe Was on January 10, 2002 11:59 AM

If your data is in D1:D6 and the formula is in D7, then the fix is:

=IF(D3=0,D4,IF(D4=0,D5,IF(D5=0,D6,"None!")))

JSW

Posted by Jan on January 10, 2002 12:11 PM

The problem is i can get it to work for the first two cells and then anything after that it will not insert the correct value.

Posted by Joe Was on January 10, 2002 12:54 PM

It works for me I tested it!
Check your cell format, for each data cell, it should be General or Number? JSW

Posted by IML on January 10, 2002 2:24 PM

try array (control shift enter) entering
=INDIRECT("D"&MIN(IF(((D3:D7<>0)*(ROW(D3:D7))),((D3:D7<>0)*(ROW(D3:D7))))))

This will return the first non-zero value, which I think is what you're after. As far as the if statements goes, you'd need to add a bunch of And statements.