Formula chooser?
 

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!

One way:

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



In article ,
JKG wrote:

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!

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 like
this:

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


--
Lilliabeth
------------------------------------------------------------------------
Lilliabeth's Profile: http://www.excelforum.com/member.php...o&userid=27741
View this thread: http://www.excelforum.com/showthread...hreadid=475052

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!


"JE McGimpsey" wrote:

One way:

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



In article ,
JKG wrote:

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!