IF function (more than 7)

[Billionzz]

I’m trying to do the following IF function but as far I can tell IF only works with 7 different selections.

If some one could help me with the following I would appreciate it.

Here is what I am trying to do:

Cell B2 contains the numbers I through 8
Cell D2 contains a pre-calculated number (e.g. 53.012)
Cell F2 is the cell that I need an IF function or something similar – the function would be as follows
=IF(B2=1,D2*1.000)
=IF(B2=2,D2*1.00057)
=IF(B2=3,D2*1.00171)
=IF(B2=4,D2*1.002281)
=IF(B2=5,D2*1.003421)
=IF(B2=6,D2*1.003991)
=IF(B2=7,D2*1.004215)
=IF(B2=8,D2*1.004538)

Thanks for your help
Bill

 

[Jim North]

Although it would be better to have the values in a table rather than buried in a formula, you can duplicate what you have with:

If the value (1-8) is in B9....

=CHOOSE(B9,1,1.00057,1.00171,1.002281,1.003421,1.003991,1.004215,1.004538)

_________________
JRN

Excel 2000; Windows 2000
This message was edited by Jim North on 2002-08-26 13:03

 

[lenze]

Wrong Tool:

Try the CHOOSE Function

=CHOOSE(B2,D2*1.000,D2*1.00057....D2*1.0004538)

You can have up to 29 options

 

[Damon Ostrander]

Hi Billionzz,

Try:

=D2*CHOOSE(B2,1.000,1.0057,1.00171,1.002281,1.003421,...)

 

[lenze]

Damon's formula is much cleaner than mine

 

[Aladin Akyurek]

On 2002-08-26 12:50, Billionzz wrote:

I’m trying to do the following IF function but as far I can tell IF only works with 7 different selections.

If some one could help me with the following I would appreciate it.

Here is what I am trying to do:

Cell B2 contains the numbers I through 8
Cell D2 contains a pre-calculated number (e.g. 53.012)
Cell F2 is the cell that I need an IF function or something similar – the function would be as follows
=IF(B2=1,D2*1.000)
=IF(B2=2,D2*1.00057)
=IF(B2=3,D2*1.00171)
=IF(B2=4,D2*1.002281)
=IF(B2=5,D2*1.003421)
=IF(B2=6,D2*1.003991)
=IF(B2=7,D2*1.004215)
=IF(B2=8,D2*1.004538)

Bill,

( 1.) Make a 2-column list in a worksheet you could name Admin.
( 2.) Select the data cells of this 2 column list.
( 3.) Go to the Name Box on the Formula Bar, type ATable (from association table) or something similar, and hit enter.

Now use this single formula that eliminates the need for zillion IFs.

=D2*VLOOKUP(B2,ATable,2)

Aladin
This message was edited by Aladin Akyurek on 2002-08-26 13:10

 

[JohnL]

You can add the values of the individual IF statements by making your formular as follows:
=sum(IF(Condition 1),1,0),IF(Condition 2),2,0)))
You can string several IF statements together using this method. This constitutes only one level IF statement.

 

[Jim North]

Aladin's is the correct answer (IMHO)

 

[Billionzz]

I would like to thank everyone for all of the help.

I used Aladin's solution and it worked great.

Thanks again.
Bill

 

[jennpsw]

how to use this IF function to group data?
let say:

Carrot
Watermelon
Duck
Chicken
Spinach
Coffee
Beef
Mutton
Banana
Tomato

Group them into Vegetable, Poultry,Meat,Beverage,Fruit

In fact in my work I need to group my datas to 10 different groups. But the max to use IF function is only 7.

Anyone can help please?

Thanks