Return Result If Number Is Within Range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MDW - 20 Oct 2006 14:55 GMT

I run into this problem a lot, and each time I have to kludge together a
solution that seems overly complicated. Maybe someone knows of a more elegant
way?

Say I've got the following table:

Score Range | % Adjustment
601+ | 5%
501-600 | 4.5%
426-500 | 4%
376-425 | 3%
etc.

Say I have a score of 580...this needs to correspond to a 4.5%. I know I can
get that answer by doing some nested SUMPRODUCT functions...but is there an
easier way?

TIA.

Signature

Hmm...they have the Internet on COMPUTERS now!

Reply to this Message

Teethless mama - 20 Oct 2006 15:14 GMT

=IF(A1<376,0,IF(A1<426,0.03,IF(A1<501,0.04,IF(A1<601,0.045,0.05))))

or

use VLOOKUP function will do the trick

> I run into this problem a lot, and each time I have to kludge together a
> solution that seems overly complicated. Maybe someone knows of a more elegant
[quoted text clipped - 14 lines]
>
> TIA.

Reply to this Message

Pete_UK - 20 Oct 2006 15:41 GMT

Set up a table like this somewhere on your worksheet (assume it is
X1:Y5):

0 0%
376 3%
426 4%
501 4.5%
601 5%

If your score is in A1, enter this formula in B1:

=VLOOKUP(A1,X$1:Y$5,2)

and this will return 4.5% if A1 = 580. You will need to add the other
values below 376/3% to the beginning of your table, and adjust the
range appropriately - the first column is in ascending order and is the
lowest number of the range.

Hope this helps.

Pete

> I run into this problem a lot, and each time I have to kludge together a
> solution that seems overly complicated. Maybe someone knows of a more elegant
[quoted text clipped - 14 lines]
>
> TIA.

Reply to this Message