Nesting IF function

[Excel Neophyte]

I am looking for a formula for Excel 2007. The formula I currently have is only partially working. Essentially I want the formula for cell Q6 to read: IF G10 is blank then add M6+G9, IF G9 is blank then add M6+G8, IF G8 is blank then add M6+G7, IF G7 is blank then add M6+G6.

=IF(G10="",M6+G9,IF(G9="",M6+G8,IF(G8="",M6+G7,IF(G7="",M6+G6,G10+M6))))

At this time if G10 is blank it works, however if G9, G8, or G7 is blank Q6 returns a #VALUE! because it is reading that part of the formula as the “if false” part.

I apologize that I haven’t added an example snapshot, but I haven’t had any luck being able to include one.

 

[vlady]

maybe this one

=IF(G10="",M6+G9,IF(G9="",M6+G8,IF(G8="",M6+G7,IF(G7="",M6+G6,""))))

 

[Excel Neophyte]

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]C
[/TD]
[TD]G
[/TD]
[TD]J
[/TD]
[TD]M
[/TD]
[TD]Q
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]JAN
[/TD]
[TD][/TD]
[TD][/TD]
[TD]FEB
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]$5
[/TD]
[TD]$5
[/TD]
[TD][/TD]
[TD]$5
[/TD]
[TD]#value!
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]$5
[/TD]
[TD]$10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]$5
[/TD]
[TD]$15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
Sorry, this chart was the best I could do to provide an example. I deleted columns that were not relavent to this problem.

What I want the function to do is look at G10, if there's something there then M6+G10, if it is blank then look at G9, if there's something there then M6+G9, if it's blank then look at G8, etc. until it reaches G6.

Currenty, if there's nothing in G10 it's fine as illustrated below, but if there's nothing in G10 & G9 it stops looking & returns #value!

How do I make it keep looking at the cell above until it either reaches a cell with a value?

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]C
[/TD]
[TD]G
[/TD]
[TD]J
[/TD]
[TD]M
[/TD]
[TD]Q
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]JAN
[/TD]
[TD][/TD]
[TD][/TD]
[TD]FEB
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]$5
[/TD]
[TD]$5
[/TD]
[TD][/TD]
[TD]$5
[/TD]
[TD]$25
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]$5
[/TD]
[TD]$10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]$5
[/TD]
[TD]$15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]$5
[/TD]
[TD]$20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[AndyPandy27]

This does work, but I'm sure a more advanced Excel guru would provide a more succinct formula:

=IF(AND(G10="",G9="",G8="",G7="",G6<>""),M6+G6,IF(AND(G10="",G9="",G8="",G7<>""),M6+G7,IF(AND(G10="",G9="",G8<>""),M6+G8,IF(AND(G10="",G9<>""),M6+G9,M6+G10))))

Cheers,

AP

 

[TheCman81]

You could try this, it's a bit long but seems to work fine

=IF(G10="",IF(G9="",IF(G8="",IF(G7="",IF(G6="",G6+$M$6),G7+$M$6),G8+$M$6),G9+$M$6),G10+$M$6)

 

[AndyPandy27]

Hi TheCman81,

I've tried your suggestion, but it doesn't work if G10, G9, G8 and G7 are all blank.

I amended it to:

=IF(G10="",IF(G9="",IF(G8="",IF(G7="",IF(G6<>"",G6+$M$6),G7+$M$6),G8+$M$6),G9+$M$6),G10+$M$6)

and that seems to work ok.

 

[AndyPandy27]

Hi TheCman81,

I've tried your suggestion, but it doesn't work if G10, G9, G8 and G7 are all blank.

I amended it to:

=IF(G10="",IF(G9="",IF(G8="",IF(G7="",IF(G6<>"",G6+$M$6),G7+$M$6),G8+$M$6),G9+$M$6),G10+$M$6)

and that seems to work ok.

I'm actually wrong about this correction - if all cells G6:G10 are blank, this would result in "FALSE" being returned, my apologies.

I have slightly improved my initial response though, the following is a little shorter:

{=IF(AND(G7:G10="",G6<>""),M6+G6,IF(AND(G8:G10="",G7<>""),M6+G7,IF(AND(G9:G10="",G8<>""),M6+G8,IF(AND(G10="",G9<>""),M6+G9,M6+G10))))}

Please note this is an array formula so should be entered using CTRL+SHIFT+ENTER (do not add curly brackets yourself, Excel will do this automatically).

Cheers,

AP

 

[Jonmo1]

Try

=IF(COUNT(G6:G10),LOOKUP(9.99999999999999E+307,G6:G10)+M6,"")

 

[TheCman81]

Sorry I missed out 1 part of a true statement

=IF(G10="",IF(G9="",IF(G8="",IF(G7="",IF(G6="","",G6+$M$6),G7+$M$6),G8+$M$6),G9+$M$6),G10+$M$6)

 

[Excel Neophyte]

Andy,

Thanks! This works perfect. I also tried your array formula & it also works great. Much appreciation!