Quintile Lookup

[Kaye]

I have a list on quintile values for monthly rainfall totals. ie ..

A B C D E F G H
Quin 0 1 2 3 4 5 6
Jan 0-0 1-10 11-23 24-58 59-80 81-90 91 and greater

Row 1 containns the monthly QUINTILE values from 0 to 6, and
Row 2 has the average monthly rainfall range for each month.

Under JAN, there are the other 11 months along with the differing
mthly quintile values, tho that is to be ignored here.

My problem - how can I return the correct quintile value returned for
the monthly rainfall stored in cell named "MonthlyRainfall"?

For example, "MonthlyRainfall" = 25
How do I have the quintile of "3" returned in a cell?

Any ideas would be greatfully appreciated.

Kaye

 

[T. Valko]

Set your ranges to the lower boundary:

0,1,11,24,59,81,91

Then:

A4 = MonthlyRainfall = 25

=LOOKUP(A4,B2:H2,B1:H1)

Biff

 

[Kaye]

Thanks Biff - works at treat.

This a w-a-y smaller than my nested if statement!

Kaye

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Thanks Biff - works at treat.

This a w-a-y smaller than my nested if statement!

Kaye