Using Nested If in Excel 1

[thestampdad]

I am using a Nested IF statement which is not returning the correct value for me.

I have 7 IF's and one cell that it references is empty but this may not always be the case. Once the formula hits this empty cell it does not seem to carry on with the remainder of the formula. The formula below takes the results from another IF stmt. since there would be more than 7 criteria and adds to the next IF stmt.

=AE5+(IF($AH5=1,$AF5+(IF($AK5=1,$AI5+(IF($AN5=1,$AL5+(IF($AQ5=1,$AO5+(IF($AT5=1,$AR5+(IF($AW5=1,$AU5+(IF($AZ5=1,$AX5))))))))))))))

The cells in AW and AU are empty. How can I make the formula complete to the end?

Thanks for any help you can provide.

 

[wwgmr]

Thestampdad

Hi I would suggest in cell AW AU put default 0 so that excel can check to see if it = 1 when its empty you have no Number value for it to check and you will get error. If you highlight the full columns and ctrl + 1 to open format cells then change it to number that should solve it for you.

 

[TonyJollans]

Hi thestampdad,

What do you mean complete? If AW5 is empty there is nothing more to do - you don't specify what to do when the condition is False (i.e. AW5 <> 1, whether empty or not) so it will return False (=0). Could you tell us what you expect to happen? with any particular set of values?

Enjoy,
Tony

 

[Nareik]

this is untested, but 'should work'

=AE7+($AH7=1)* ($AF7+($AK7=1) * ($AI7+($AN7=1) * ($AL7+($AQ7=1) *( $AO7+($AT7=1) * ($AR7+($AW7=1) * ($AU7+($AZ7=1) * $AX7))))))

It relies on the fact that the test xxx=1 will return either 0 (false) or 1 (true).
It is an implicit if statement I suppose therefore in this context.

 

[sfvb]

I'm not sure, but it sounds like you want an additive function instead of a multiplicative one.

(For example, if cell $AZ5=1, then you want to add cell $AX5 to the total, without regard to any of the previous IF statements.)

If that's the case then this should work for you:

=$AE5+($AH5=1)*$AF5+($AK5=1)*$AI5+($AN5=1)*$AL5+($AQ5=1)*$AO5+($AT5=1)*$AR5+($AW5=1)*$AU5+($AZ5=1)*$AX5

 

[thestampdad]

Thank you to all who responded to my question. I have found that the response from sfvb to be the best option for me.

This one allows more than 7 statements to be used since they are not IF statements. This also does not care if a field is blank which is what I was looking for.

Thanks,

Dave.