Formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Astrad459 - 16 Oct 2006 18:36 GMT

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.

Reply to this Message

joeu2004@hotmail.com - 16 Oct 2006 18:58 GMT

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

Reply to this Message

Roger Govier - 16 Oct 2006 18:58 GMT

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

Signature

Regards

Roger Govier

>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:
[quoted text clipped - 6 lines]
>
> Any assistance would be greatly appreciated.

Reply to this Message

Astrad459 - 16 Oct 2006 19:42 GMT

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.

> Hi
>
[quoted text clipped - 16 lines]
> >
> > Any assistance would be greatly appreciated.

Reply to this Message

Sandy Mann - 16 Oct 2006 21:46 GMT

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)

Signature

HTH

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

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

> That's exactly what I would like to do:
>
[quoted text clipped - 28 lines]
>> >
>> > Any assistance would be greatly appreciated.

Reply to this Message

joeu2004@hotmail.com - 17 Oct 2006 00:07 GMT

> 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 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)

Reply to this Message