Help with IF function

[rickeyjt]

Hi,

I want to evaluate a number entered in cell A11 as a multiplier for a formula in cell A20. Cannot figure out how to use IF in this case.

The user enters in a number in cell A11 - either 2, 3, 4, 6, or 8.

These whole numbers stand for a multiplier value that I want to use for a formula in cell A20 to calculate. The A20 result is then used in other cell calculations.
2 = *1
3 = *1.5
4 = *2
6 = *3
8 = *4

Rick

[TMS]

=A11/2 maybe?


Regards, TMS

[rickeyjt]

No.

If data entered in A11 equal 3 then the value I want returned is 1.5. 1.5 is the number I want to use as my multiplier in cell A20.

[daffodil11]

You can just reference the formula above in A20.

A20 = somethingsomething*(A11/2)

Or, if you're really hellbent on using an IF statement:

=IF(A11=2,1,IF(A11=3,1.5,IF(A11=4,2,IF(A11=6,3,IF(A11=8,4)))))*whatever

But nesting IFs needs to be entered as an array, using Ctrl+Shift+Enter

[Tony Valko]

Create a 2 column table like this...

2.....1
3.....1.5
4.....2
6.....3
8.....4

Let's assume that table is in the range A1:B5.

Then, you would use something like this:

=VLOOKUP(A11,A1:B5,2,0)

[Tony Valko]

But nesting IFs needs to be entered as an array, using Ctrl+Shift+Enter

No need to array enter. Just a normal enter will do.

[TMS]

I wonder, are you always so abrupt with strangers that you are looking to for assistance? Does that work for you?

Perhaps worth making the point that your thread has had 39 views and only one response. What do you think the reason for that may be.

I was under the impression that 3/2 was 1.5. Hence, if A11 contains 3, the formula =A11/2 would return 1.5 ... which you would use as your multiplier in the formula in A20. However, I do not know what that formula looks like so I can be of little assistance other than guessing at your requirements.

You may wish to provide a sample workbook with some typical data indicating your desired and expected results.

Regards, TMS

[rickeyjt]

Thank you all so much. The nested IF worked just fine.

[Tony Valko]

You're welcome. We appreciate the feedback!