Formulas

A

Astrad459

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.
 
J

joeu2004

Astrad459 said:
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.
Click to expand...

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(...)"
 
R

Roger Govier

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
 
A

Astrad459

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.
 
S

Sandy Mann

Hi Astrad459,

You still don't give the whole set of ranges that you want to test for.
With the upper limit of every range, ie from the example you have given 99,
249, 399, try:

=SUM(--(B2>{0,99,249,399,499,549,749,999}))

(The 499,549,749 & 999 are my *guesses* at further range limits)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
J

joeu2004

Astrad459 said:
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.
Click to expand...

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 are:

=MATCH(MAX(0,A1), {0,1,101,251,300}, 1) - 1

The key elements are: (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)
 
Top