If Then function with Multiple conditions

Hello, complete newbie here.

I want to build a formula for reading a chart and automatically updating a field according to the chart.
For example, if number is between 1 and 5, use field1.
If number is between 6 and 15, use field 2.
If number is between 16 and 30, use field 3.
etc.
If number is greater than 100, use this formula.

I know this can be done, I just don't know how to do it. Please help!

Thank you.

Sounds like you need 'nested ifs' DaBrot.

something like the attached perhaps?

nested ifs.xls

You can avoid the nested IFs by setting up a table with the lower values in each range and then using MATCH in conjunction with INDEX. If you give some further details of the cells you use and the full range of values for each field then I'll be able to give you a more fulsome reply.

Hope this helps.

Pete

The range of values are as follows:

1-5: 1
6-15: 2
16-30: 3
31-45: 4
46-60: 5
61-75: 6
76-90: 7
91-100: 8
above 100: 8+roundup((value-100)/25)

I have other tables as well, so all the reference tables are stored in a different sheet (same file). Fortunately, most of the others are similar to the situation of "above 100" so I can write the formula for that.

I did ask for details of the cells you use - I don't know what you mean by value for above 100, or indeed where that number comes from, or even what your "fields" relate to.

Anyway, just list the start values of those ranges, let's say starting from X1, i.e.

X1: 1
X2: 6
X3: 16
X4: 31
X5: 46
X6: 61
X7: 76
X8: 91

Then you can have a formula like this:

=IF(value>100,8+ROUNDUP((value-100)/25,0),INDEX(range,MATCH(value,$X$1:$X$8)))

Hope this helps.

Pete

Bonjour,
I don't know details of the range of values. you can see attached file for reference..

Okay, see Sheet2 of the attached for an alternative way of achieving your aims.

Hope this helps.

Pete