switch function help?

[sully343]

Hello,

Coming to you guys looking for a little help. I attached the spreadsheet for reference.

The cell I'm working on is F11. It has an equation in it that I need to link to two drop down selection.

About 3/4 of the way through the function is "0.75"

That is a fixed value I put, that I need to make a variable based on the drop down in D3.

I need it to be:
High = 1.25
Medium = 0.90
Low = 0.75

Then lastly, if "yes" is selected in the D4 drop down, the entire equation gets cut in half (divided by 2)...


I was given this code to use in F11, however when I use it it gives me a #NAME ! Error...

[FONT=arial]IF(D4="Yes",(((((((182.4601 * F2 -775.6821) * F2 +1262.7794) * F2 -669.5622) *10) *SWITCH(D3,"Low",0.75,"Medium"[/FONT]<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">[FONT=arial],0.9,"High",1.25))/ 50 )* B4)/2,((((((182.4601 * F2 -775.6821) * F2 +1262.7794) * F2 -669.5622) *10) *SWITCH(D3,"Low",0.75,"Medium"[/FONT]<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">[FONT=arial],0.9,"High",1.25))/ 50 )* B4)[/FONT]

Thanks for any help, this is greatly appreciated.

 

[Jonmo1]

Switch is a function only available in XL2016 with an Office365 subscription
It's basically a shortcut for nesting ifs..

SWITCH(D3,"Low",0.75,"Medium"<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">,0.9,"High",1.25)
It's saying IF D3 = Low, then 0.75, if D3 is Medium, then 0.9, if D3 is High, then 1.25.

You'll have to replace that with
IF(D3="Low",0.75,IF(D3="Medium",0.9,IF(D3="High",1.25)))

 

[Jonmo1]

Or maybe
LOOKUP(D3,{"High","Low","Medium"},{1.25,0.75,0.9})

 

[sully343]

Thanks for your reply. I tried to switch that out and it didn't work...I'd like it to work in all excels, since my plan was to share the sheet with other people who would like to use it...I uploaded the file to my drop box:

https://www.dropbox.com/s/v9071shk19n41rg/brewlog.xlsx?dl=0

maybe you could take a look at it?

 

[Jonmo1]

This is all else being equal, assumption that the SWITCH function is the only problem with the formula...

Change both occurrences of
SWITCH(D3,"Low",0.75,"Medium"<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">,0.9,"High",1.25)
to
IF(D3="Low",0.75,IF(D3="Medium",0.9,IF(D3="High",1.25)))

So original
IF(D4="Yes",(((((((182.4601 * F2 -775.6821) * F2 +1262.7794) * F2 -669.5622) *10) *SWITCH(D3,"Low",0.75,"Medium"<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">,0.9,"High",1.25))/ 50 )* B4)/2,((((((182.4601 * F2 -775.6821) * F2 +1262.7794) * F2 -669.5622) *10) *SWITCH(D3,"Low",0.75,"Medium"<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">,0.9,"High",1.25))/ 50 )* B4)

change to

IF(D4="Yes",(((((((182.4601 * F2 -775.6821) * F2 +1262.7794) * F2 -669.5622) *10) *IF(D3="Low",0.75,IF(D3="Medium",0.9,IF(D3="High",1.25))))/ 50 )* B4)/2,((((((182.4601 * F2 -775.6821) * F2 +1262.7794) * F2 -669.5622) *10) *IF(D3="Low",0.75,IF(D3="Medium",0.9,IF(D3="High",1.25))))/ 50 )* B4)



Note, there are a lot of spaces in that formula, possibly resulting from your copy paste from excel to forum procedure.
You may need to remove them.

 

[sully343]

This is all else being equal, assumption that the SWITCH function is the only problem with the formula...

Change both occurrences of
SWITCH(D3,"Low",0.75,"Medium"<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">,0.9,"High",1.25)
to
IF(D3="Low",0.75,IF(D3="Medium",0.9,IF(D3="High",1.25)))

So original
IF(D4="Yes",(((((((182.4601 * F2 -775.6821) * F2 +1262.7794) * F2 -669.5622) *10) *SWITCH(D3,"Low",0.75,"Medium"<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">,0.9,"High",1.25))/ 50 )* B4)/2,((((((182.4601 * F2 -775.6821) * F2 +1262.7794) * F2 -669.5622) *10) *SWITCH(D3,"Low",0.75,"Medium"<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">,0.9,"High",1.25))/ 50 )* B4)

change to

IF(D4="Yes",(((((((182.4601 * F2 -775.6821) * F2 +1262.7794) * F2 -669.5622) *10) *IF(D3="Low",0.75,IF(D3="Medium",0.9,IF(D3="High",1.25))))/ 50 )* B4)/2,((((((182.4601 * F2 -775.6821) * F2 +1262.7794) * F2 -669.5622) *10) *IF(D3="Low",0.75,IF(D3="Medium",0.9,IF(D3="High",1.25))))/ 50 )* B4)



Note, there are a lot of spaces in that formula, possibly resulting from your copy paste from excel to forum procedure.
You may need to remove them.

Viola! That worked perfectly. Thank you very much!

 

[Jonmo1]

You're welcome.

 

[Tetra201]

Late to the party, but I would suggest replacing

SWITCH(D3,"Low",0.75,"Medium",0.9,"High",1.25)

with

VLOOKUP(D3,{"Low",0.75;"Medium",0.9;"High",1.25},2,0)

One function call, one reference call, similar syntax, same functionality.

 

[Jonmo1]

One notable difference is that will result in #N/A if D3 is anything other than one of the 3 values, and the overall result of the formula is also #N/A
Conversely, The IF will result in FALSE (therefore the overall result of the formula is 0 instead of #N/A)

I know the original switch function did the same thing, but it's worth noting, and perhaps that would be an improvement.