Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function / Formula
601
600 595 550 525 505 500 450 400 350 300 Using the above example in an adjacent column I would like to assign the number 1 for all cells = to or greater than 600 , the number 2 to all cells less than 599 but greater than 500, the number 3 to all cells less than 499 but greater than 400, and so on. I realize the if function may work however I am using excel 2003 and I believe the limitation is approx 7. I need a formula that will allow up to approx 50 different nemerical associations Thanx |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function / Formula
To do 50, I would create a chart somewhere. The chart should be in ascending numerical order, and it only needs to indicate the beginning of each range. When you enter the beginning of the next range, that implicitly sets the cap for the prior range. The chart lists the beginning number of each "range" in first column, and the second column the results for that range. Based on what you said so far, the table would look like this: Code: -------------------- Column X Column Y 1 0 7 2 100 6 3 200 5 4 300 4 5 400 3 6 500 2 7 600 1 -------------------- Once your chart is complete, go back to your data set and in I1 enter this formula to compare H1 to this chart: =VLOOKUP(H1,$X$1:$Y$7,2,TRUE) Of course, if your chart is bigger, adjust those chart references. Now copy it down to get your "results". -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=56007 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function / Formula
If there is any kind of regularity in the difference of your ranges (as you
show in your example), then there may be a direct mathematical solution. If you tell us how the ranges progress, then we could determine if such a mathematical formula exists or not. -- Rick (MVP - Excel) "Jerry Bailey" wrote in message ... 601 600 595 550 525 505 500 450 400 350 300 Using the above example in an adjacent column I would like to assign the number 1 for all cells = to or greater than 600 , the number 2 to all cells less than 599 but greater than 500, the number 3 to all cells less than 499 but greater than 400, and so on. I realize the if function may work however I am using excel 2003 and I believe the limitation is approx 7. I need a formula that will allow up to approx 50 different nemerical associations Thanx |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function / Formula
Assuming the same relationship, this might work, but you will have to
adjust for your valid range. =7-INT(A1/100) Dana DeLouis Jerry Bailey wrote: 601 600 595 550 525 505 500 450 400 350 300 Using the above example in an adjacent column I would like to assign the number 1 for all cells = to or greater than 600 , the number 2 to all cells less than 599 but greater than 500, the number 3 to all cells less than 499 but greater than 400, and so on. I realize the if function may work however I am using excel 2003 and I believe the limitation is approx 7. I need a formula that will allow up to approx 50 different nemerical associations Thanx |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function / Formula
I'm pretty sure that won't handle the OP's 50 different numerical
associations<g. We need more information from the OP before a mathematical solution can be suggested (hence, my posting elsewhere in this thread). -- Rick (MVP - Excel) "Dana DeLouis" wrote in message ... Assuming the same relationship, this might work, but you will have to adjust for your valid range. =7-INT(A1/100) Dana DeLouis Jerry Bailey wrote: 601 600 595 550 525 505 500 450 400 350 300 Using the above example in an adjacent column I would like to assign the number 1 for all cells = to or greater than 600 , the number 2 to all cells less than 599 but greater than 500, the number 3 to all cells less than 499 but greater than 400, and so on. I realize the if function may work however I am using excel 2003 and I believe the limitation is approx 7. I need a formula that will allow up to approx 50 different nemerical associations Thanx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula or Function | Excel Discussion (Misc queries) | |||
Help with OR function in IF function formula | Excel Worksheet Functions | |||
What formula/function can I use? | New Users to Excel | |||
need function/formula help!! | Excel Worksheet Functions | |||
A formula/function to return a formula/function | Excel Worksheet Functions |