Formula chooser?

[JKG]

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!

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

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

[JE McGimpsey]

One way:

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



In article <[email protected]>,
JKG <[email protected]> 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!

[JKG]

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 <[email protected]>,
> JKG <[email protected]> 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!
>