Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas
I know this should be easy but I'm having a difficult time making this
formula. I need a formula that would do the example listed below: For example, if B2 was a number between 1-100, it would return a "1" in B3, and if the number was between 101-250, it would return a "2" in B3 and so on. Any assistance would be greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas
Astrad459 wrote:
I know this should be easy but I'm having a difficult time making this formula. I need a formula that would do the example listed below: For example, if B2 was a number between 1-100, it would return a "1" in B3, and if the number was between 101-250, it would return a "2" in B3 and so on. Did you mean 101-200? If not, then I am not sure what "and so on" would be. For example, when is 3 returned: 251 to what number? Assuming you mean 101-200, the following might do what you want: =max( 0, 1 + int( (B2-1) / 100 ) ) MAX(0,...) is needed to cover the case of B2<=0. If B2 is always 1 or more, you can get away with simply "1 + int(...)" |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas
Hi
A little unclear from your posting, as to what you do want. But if you mean 1 - 100 = 1 101 - 200 = 2 201 - 300 = 3 then try =INT((A1-1)/100)+1 -- Regards Roger Govier "Astrad459" wrote in message ... I know this should be easy but I'm having a difficult time making this formula. I need a formula that would do the example listed below: For example, if B2 was a number between 1-100, it would return a "1" in B3, and if the number was between 101-250, it would return a "2" in B3 and so on. Any assistance would be greatly appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas
That's exactly what I would like to do:
Say cell B2 has a number of 233. I need cell B3 to read "2" because it is within the range of 100-249. If the number were 98, it should read a "1" because it is within the range of 1-99. A number of 250-399 would read "3", and the range goes on with bigger numbers located in B2. The formula below will not work because it is not based purely on 100's. Thank you for your response though. "Roger Govier" wrote: Hi A little unclear from your posting, as to what you do want. But if you mean 1 - 100 = 1 101 - 200 = 2 201 - 300 = 3 then try =INT((A1-1)/100)+1 -- Regards Roger Govier "Astrad459" wrote in message ... I know this should be easy but I'm having a difficult time making this formula. I need a formula that would do the example listed below: For example, if B2 was a number between 1-100, it would return a "1" in B3, and if the number was between 101-250, it would return a "2" in B3 and so on. Any assistance would be greatly appreciated. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas
Astrad459 wrote:
That's exactly what I would like to do: Say cell B2 has a number of 233. I need cell B3 to read "2" because it is within the range of 100-249. [....] A number of 250-399 would read "3", and the range goes on with bigger numbers located in B2. Non sequitur! Previously, you said that 101-250 would return 2, not 100-249. So 100 should convert to 1 and 250 should convert 2, according to your original specifications. Since your specifications are so ambiguous, I suggest that you adapt the following paradigm to your specific needs, whatever they a =MATCH(MAX(0,A1), {0,1,101,251,300}, 1) - 1 The key elements a (a) the "{...}" array should contain each of the lower bounds of your ranges; and (b) "1" in the 3rd argument of MATCH() says "match the largest value in the array less than or equal to the look-up value. The above MATCH() will return 1 greater than your intended results, which is why I subtract 1 afterwards. That allows for A1<=0, A1 is empty, and A1 is text. If you do not need that much robustment -- that is, if A1 is always 1 or greater -- you can use the following simpler paradigm: =MATCH(A1, {1,101,251,300}, 1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change column letters to correct ones in many formulas automatically? | Links and Linking in Excel | |||
how can i get formulas in excel to copy and paste? | Excel Worksheet Functions | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |