Home

If statement based on drop down

Highlighted
brian sullivan
New Contributor

If statement based on drop down

Hello, 

 

I'm making a brew sheet and am stuck. I'm decent with excel, but not this advanced.

 

In the sheet, I'm working on the formula for cell F11. The formula is complete, with the exception of my "if" statement. Right now, I have 0.75 about 3/4 the way through the equation. What I need to do is make that a variable based on the cell D3. 

 

I need low to = 0.75

Medium to = 0.90

and High to =1.25

 

Lastly, if someone selects "yes" for D4, I need that output (of the entire equation) to be divided by 2...

 

Could anyone help me out with how I go about doing this?

 

3 Replies

Re: If statement based on drop down

I am not sure if I understand your question fully. But this is how you would do if you want to multiple your equation by 0.75|0.9|1.25 depending on what is set on D3. I am using the SWITCH() formula which is available in Excel 2016.

 

This will also divide it by 2 if D4 says yes. Let me know if this works for you. The formula you need to use in F11 is:

 

=IF(D4="Yes",(((((((182.4601 * F2 -775.6821) * F2 +1262.7794) * F2 -669.5622) *10) *SWITCH(D3,"Low",0.75,"Medium",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",0.9,"High",1.25))/ 50 )* B4)

Re: If statement based on drop down

It looks like that's what I'm going for, however when I put it in the forumla it just says "#NAME?" in D11 for the output. It's not that I want to multiple the whole equation by that, it's that I want the ".75" in the equation to be either .75, .95, or 1.25 based on high medium low, and if someone selects no, then the entire equation is divided by 2.

 

also, I don't know if I set you up with my title "If statement.."...I have no preference for if statements if there's better ways to do this. 

Re: If statement based on drop down

If you are interested, you may try the following formula

 

=((((((182.4601 * F2 -775.6821) * F2 +1262.7794) * F2 -669.5622) *10) *0.75)/ 50 )* B4 * (0.5 + N(D4<>"Yes")*0.5) * INDEX({0.75;0.9;1.25},MATCH(D3,{"Low";"Medium";"High"},0),1)

Note: If you do not select anything in D3, it will come out #N/A. 

 

Switch Function in Excel

Related Conversations
Add 5 if a cell contains text "yes"
Titchard Family in Excel on
8 Replies
IF Statement with Networkdays
R S in Excel on
4 Replies
Problem with nested IF formula #Excel #2018
Laurie McDowell in Excel on
7 Replies
Comparing two ranges of cells in an if function
Joseph Assaf in Excel on
7 Replies
2 lists, 1 if
Bartosz Heller in Excel on
4 Replies
Help with Conditional Formatting
Carly Rose Carriere in Excel on
10 Replies