Trouble with an "IF" statement

[derek davis]

Please see example attached. My issue is with column R. The if statement only appears to work correctly for row 10, but rows 8,9 returns "false" I am at a loss and could do with help from one of you guys to correct this issue. I am using Excel 2007 if this helps.

Thank you.

 

[AliGW]

In R3 copied down:

=IF(Q3="","",IF(Q3="1 photo",Photo,IF(Q3="2 photo",Photo*2,IF(Q3="3 photo",Photo*3))))

I don't know why you had included AND, but your parenthesis was incorrect and you had a leading space in the second criterion.

 

[vletm]

derek davis
... and there would be FALSE-part with You '3 photo' as below
... IF(Q3="3 photo",Photo*3,"hmm?")

 

[derek davis]

Many thanks guys I can see my error now. I appreciate your fast response.

Thanks
Del

 

[AliGW]

No problem.

 

[Peter Bartholomew]

Have you considered using an Excel Table?
Tables were introduced in Excel 2007 and have the advantage that the ranges you wish to sum adjust as the table changes size. Just tabbing over the last field on the final column automatically opens up another row and the formulas extend with it.

I have taken a different approach to the nested formula and used number formatting to remove the circular reference in 'TotalMth Hours'.

=IF(RIGHT([@Expenses],5)="photo", VALUE(LEFT([@Expenses],2)*Photo),"")