3 Options, results depend on option selected

As per heading

Cell AE16 will either be 1, 2 or 3

Cell R20, will return the result of A20*$B$5 depending of the value entered in AE16

Sound very simple,. just cant get it to work...


TIA

Hi,
how does the value in AE16 affect the calculation in R20 ?

Originally Posted by arthurbr

Hi,
how does the value in AE16 affect the calculation in R20 ?

Oooopss that part of the formula would help..!!

IF AE16 = 1, then A20*0.27+$B$5
IF AE16 = 2 then A20*0.25+$B$5
IF AE16 = 3 then A20*0.22+$B$5

You could use VLOOKUP in your equation. If you put the 3 options into a table, the formula would be:

=A20*(VLOOKUP(AE16;'Sheet1'!$A$1;$A$3;2;FALSE))+$B$5

Where Sheet1 would be where the table is stored
A1 = 1
A2 = 2
A3 = 3

(your answer options for your previous question, this could be part of your drop down table as well)

B1 = .27
B2 = .25
B3 = .22

You could include even more of the equation in the table as well if you like. I'm just showing it with only the unique portion of the equation in it.

Originally Posted by Mike_Dean

Oooopss that part of the formula would help..!!

IF AE16 = 1, then A20*0.27+$B$5
IF AE16 = 2 then A20*0.25+$B$5
IF AE16 = 3 then A20*0.22+$B$5

Another option

=a20*lookup(ae16,{1,2,3},{.27,.25,.22})+$B$5

Originally Posted by arthurbr

Another option

=a20*lookup(ae16,{1,2,3},{.27,.25,.22})+$B$5

Thanks arthur and boots, will try both options.

This is the start point for what will be a much bigger formula.

Thanks
Mike