Return Result If Number Is Within Range
 

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.
--
Hmm...they have the Internet on COMPUTERS now!

Return Result If Number Is Within Range
 

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

or

use VLOOKUP function will do the trick



"MDW" wrote:

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.
--
Hmm...they have the Internet on COMPUTERS now!

Return Result If Number Is Within Range
 

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

MDW wrote:
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.
--
Hmm...they have the Internet on COMPUTERS now!