What if statement

Try: -

=IF(AND(Z3=4,V3=1),D28/4,"")

Hi Mudface, thanks for your help. I have an even more complex WHAT IF scenario...I hope you (or someone else) can help me with it. If a simple WHAT IF statement will not work, maybe someone could suggest a macro that will do the trick, here goes:

If Z3=4 and V3=1 then d28/4
or if Z3=4 and V3=2, then d28/5
or if Z3=4 and V3=3, then d28/6
or if Z3=3 and V3=1, then d28/7
or if Z3=3 and V3=2, then d28/8
or if Z3=3 and V3=4, then d28/9
or if Z3=2 and V3=1, then d28/10
or if Z3=2 and V3=3, then d28/11
or if Z3=2 and V3=4, then d28/12
or if Z3=1 and V3=2, then d28/13
or if Z3=1 and V3=3, then d28/14
or if Z3=1 and V3=4, then d28/15

That's it...looks complex to me, but I hope its not that complex to the experts.

Thanks in advance.

Hi,

three methods here, all of which rely on having a lookup table to pull the values from (because what you are essentially doing is a multi-key lookup):

1) Use Dget.

=G2/DGET(A1:C13,3,E1:F2)

Probably the easiest to write, but you do need to make to make sure your data is set up right - see the help file for database functions for the details.

2) Array entered index function:

=G2/INDEX(C2:C13,MATCH(F2,IF(A2:A13=E2,B2:B13),0))

This needs to be entered using control + shift + enter, not just enter. Excel will add curly brackets round the formula if done right.

3) Sumproduct.

=G2/INDEX(C1:C13,SUMPRODUCT((A1:A13=E2)*(B1:B13=F2)*(ROW(A1:A13))),1)

I don't suggest you use this one as it will only work if you can guarentee that your lookup combinations are unique. Posted more for reference than anything.

See here:

http://www.mrexcel.com/board/viewtopic.php?topic=16933&forum=2&3

for more details / complex examples & post back if you need.

Paddy
This message was edited by PaddyD on 2002-09-05 19:50