# IF function (more than 7)

#### Billionzz

##### Board Regular
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

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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

Wrong Tool:

Try the CHOOSE Function

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

You can have up to 29 options

Hi Billionzz,

Try:

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

Damon's formula is much cleaner than mine

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)

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

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.

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

I used Aladin's solution and it worked great.

Thanks again.
Bill

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.

Thanks

Replies
10
Views
160
Replies
0
Views
311
Replies
0
Views
261
Replies
5
Views
433
Replies
8
Views
565

1,221,122
Messages
6,158,061
Members
451,461
Latest member
Rayc266

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back