What if statement

[ExcelNovice]

How do I write the following what if statement:

If Z3=4 & V3=1, then D28/4 otherwise nothing.

Thanks for your help as always.

 

[Mudface]

Try: -

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

 

[ExcelNovice]

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.

 

[PaddyD]

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):

Book8
	A	B	C	D	E	F	G
1	Z	V	Divideby		Z	V	d28
2	4	1	4		3	2	100
3	4	2	5
4	4	3	6		Results
5	3	1	7		DGET	12.5
6	3	2	8		ArrayIndex	12.5
7	3	4	9		Sumproduct	12.5
8	2	1	10
9	2	3	11
10	2	4	12
11	1	2	13
12	1	3	14
13	1	4	15
Sheet1

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