Easier way than multiple IF statements?

[SamPer]

Hello,

I have spreadsheet where I am providing calculations based on 3 variables where the user can select, via drop down list, between a few options. Variables 1 and 2 have its own % assigned to it, and Var3 simply indicates if the calculation should be considered.

For Instance =

Starting Value = 10
Starting Phase = 1

Var1 = Summer (10%) OR Winter (100%)
Var2 = Home (50%) OR Office (100%)
Var3 = Phase # (Any number greater than 1 through 10)

User Selection:
Var1 = Summer
Var2 = Office
Var3 = 1

Result should be: (10*.1*1) =1

However, if the Starting Phase were to change to anything other than 1 (Var3), the Result should be 0.

Is there a faster way than multiple embedded IF statements to obtain a clean result?

TIA!

 

[Eric W]

Try:

=Var1*Var2*Var3*(StartingPhase=Var3)

Where each of Var1, Var2, Var3, and StartingPhase are cell references.

 

[SamPer]

Thanks for the reply. Unfortunately its a little more involved than this. I tried to simplify the problem but I don't think I conveyed my actual issue. There are a series of calculations behind the "5,6,7,8" values, but in short, this is what I mean:

I was able to get all combinations but Var1 = Office, Var2 - Winter to work. Its still giving me a "7" Any ideas?

Thanks!

 

[SamPer]

I'm not able to edit my response, but I was able to figure it out.

=IF($I$4=$N28,IF($I$2="HOME",IF($I$3="SUMMER",T28,U28),IF($I$2="OFFICE",IF($I$3="SUMMER",V28,W28))),"")

 

[Eric W]

There are a lot of ways to do this, depending on your sheet:

Book4 (version 1).xlsb
	A	B	C	D	E	F	G
1
2			Var1	OFFICE
3			Var2	Winter
4			Ph	2
5
6
7			Home		Office		RESULT
8		PH	SUMMER	WINTER	SUMMER	WINTER
9		2	5	6	7	8	8
10							8
11							8
Sheet9

Cell Formulas
Range	Formula
G9	G9	=INDEX(C9:F9,(D2="OFFICE")*2+(D3="WINTER")+1)*(B9=D4)
G10	G10	=IF(B9=D4,IF(D2="Home",IF(D3="Summer",C9,D9),IF(D3="Summer",E9,F9)),"")
G11	G11	=IF(B9=D4,HLOOKUP(D3,OFFSET(C8,0,MATCH(D2,C7:E7,0)-1,2,2),2,0),"")

None of these have error handling, which may be ok if you select the values from a drop-down.

 

[SamPer]

There are a lot of ways to do this, depending on your sheet:

Book4 (version 1).xlsb
	A	B	C	D	E	F	G
1
2			Var1	OFFICE
3			Var2	Winter
4			Ph	2
5
6
7			Home		Office		RESULT
8		PH	SUMMER	WINTER	SUMMER	WINTER
9		2	5	6	7	8	8
10							8
11							8
Sheet9

Cell Formulas
Range	Formula
G9	G9	=INDEX(C9:F9,(D2="OFFICE")*2+(D3="WINTER")+1)*(B9=D4)
G10	G10	=IF(B9=D4,IF(D2="Home",IF(D3="Summer",C9,D9),IF(D3="Summer",E9,F9)),"")
G11	G11	=IF(B9=D4,HLOOKUP(D3,OFFSET(C8,0,MATCH(D2,C7:E7,0)-1,2,2),2,0),"")

None of these have error handling, which may be ok if you select the values from a drop-down.

Thank you! Thats so helpful and looks much cleaner.