Conditional formulas with multiple possibilites

[Skibee]

I need to construct a formula with the following parameters:

If L7 is
<=15 return 1.0
between 15.1 and 15.4 return 2.5
between 15.5 and 15.9 return 4.0
between 16.0 and 16.9 return 5.5
between 17.0 and 17.9 return 6.5
between 18.0 and 18.9 return 7.0

and so on.

Any help appreciated.

 

[Skibee]

I forgot to add that I have 13 ranges to include. I have tried
=IF(L7<=15,1,IF(L7<15.5,2.5,IF(L7<16,4,IF(L7<17,5.5)))) etc. but it will
only let me nest 7 IFs and I need 13 in total.

 

[T. Valko]

You also need to create a lookup table. Create a 2 column table like this:

...........A..........B
1.........0........1.0
2....15.1........2.5
3....15.5........4.0
4....16.0........5.5
5....17.0........6.5
6....18.0........7.0

Then:

=VLOOKUP(L7,A1:B6,2)

 

[Skibee]

Thank you very much. That has worked perfectly. Thanks also for laying it
out so clearly.
Liz

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[cconway]

Hello,
I have a rather unique situation that I am having trouble with. Was hoping
someone could point me in the right direction. Here is the situation:

I have a data spreadsheet that different personnel input data into. The
columns affected are J and AM. If there is a value (1 or greater) in Column
J, then there can be no values in Column AM. Otherwise, if there is no value
in Column J (empty cell), then Column AM can be 0, 1, or 99 only. Note that
Column J is blank, or empty, and not "0".

Does anyone have any advice on how to set up the formula for this?

Thank you!!

 

[Harsh Bahal]

Hi Mr. T. Valko,

I was searching for this one and have found it useful. Thanking you for the
same.

 

[T. Valko]

You're quite welcome. Thanks for the feedback!