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.

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.

"Skibee" wrote:

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.

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)

--
Biff
Microsoft Excel MVP


"Skibee" wrote in message
...
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.

Thank you very much. That has worked perfectly. Thanks also for laying it
out so clearly.
Liz
"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Skibee" wrote in message
...
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.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Skibee" wrote in message
...

Thank you very much. That has worked perfectly. Thanks also for laying
it
out so clearly.
Liz
"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Skibee" wrote in message
...
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.

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!!

Hi Mr. T. Valko,

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

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Skibee" wrote in message
...
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.

You're quite welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Harsh Bahal" wrote in message
...
Hi Mr. T. Valko,

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

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Skibee" wrote in message
...
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.