Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displays the number in text. (One thousand two hundred thirty four | Excel Worksheet Functions | |||
how can I return a value in excel that looks at a range of number | Excel Worksheet Functions | |||
Function giving a result for number within a range | Excel Worksheet Functions | |||
Convert number into words | Excel Discussion (Misc queries) | |||
Conversion | Excel Worksheet Functions |