Formula chooser?

[Guest]

Hi,

I'm looking for a way to simplify a really loooong IF statement.

In one cell, the user chooses a type of calculation. Example:

1-Prior year % to sales (figure already in worksheet)
2-Assigned % (must enter a % in another cell)
3-Fixed Dollar Amt (must enter a $ amt in another cell)

In another cell, you get the actual calculation - If they chose 1, multiply
sales by prior year % , if they chose 2, multiply sales by the assigned %
they entered, if they chose 3, use whatever figure is entered.

Right now, I've got 6 choices, and it's all going through a very complicated
IF statement such as:

=IF(choice=1,do this formula,IF(choice=2, do other formula,IF(choice=3, do
yet another formula,.......

I'm about to have to add a 7th and 8th choice, and I'm wondering if there is
any simpler way to do this.

Any ideas?

Thanks!

 

[JE McGimpsey]

One way:

=CHOOSE(A1,formula1, formula2,formula3,...)

 

[Lilliabeth]

you won't be able to add an 8th choice using IF!

Look in Help for VLookup.

Maybe like this:

In cell A1: enter the number 1
Cell A2: 2
Cell A3: 3


In the corresponding rows in the B col, enter the formulas

Now you have made the lookup table aka table_array.

If the choice they make is in cell A8, the formula will look lik
this:

=VLOOKUP(A8,A1:B3,2,FALSE

 

[Guest]

Son of a gun... I was sure that wouldn't work! I figured if my list in the
CHOOSE statement was a formula, it would return just the text of that formula
(i.e. G28*F26), not the actual result of the formula. That's what VLOOKUP
did. But you're absolutely right - it returns the results of that formula.

It even changes the cell references when you copy it!

WOW! Such a simple solution... THANKS!