# 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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### Jim North

##### MrExcel MVP
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

##### Legend
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

##### MrExcel MVP
Hi Billionzz,

Try:

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

#### lenze

##### Legend
Damon's formula is much cleaner than mine

##### MrExcel MVP
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

#### JohnL

##### New Member
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.

#### Billionzz

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

I used Aladin's solution and it worked great.

Thanks again.
Bill

#### jennpsw

##### New Member
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
6
Views
274
Replies
12
Views
236
Replies
3
Views
275
Replies
5
Views
137
Replies
10
Views
465

### Forum statistics

1,191,317
Messages
5,985,944
Members
439,991
Latest member
NCWalker ### 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