Excel formula request

[RodentMeat]

First off, I apologize if this is the wrong place to put this. I'm new!

Anywho. I am in need of a formula that is too complex for me to think of. The jist is:

Say, there's a number in cell F3. What I want to do is compare that number to a series of ranges (and by range, I mean stuff like 0 through 9, etc.), see which range the number falls in, and put a number in another cell based on that range. Complicated, no? I'll put it in context. If the number in cell F3 is between 0 and 9, put a 0 in cell I3. In the same function, If the number in cell F3 is between 10 and 19, put a 1 in cell I3. The function would continue on with different ranges and different outputs, all arriving in the same cell.

I hope all that makes sense. I'm working on an important spreadsheet and really need to figure out a function like this.

[jaclaz]

The problem might be the NUMBER and DIMENSION of ranges you need to compare to.

If I am not mistaken, IF statement allows a max of 7 levels of nesting, so, for two ranges (up to seven) you can do something like (in cell I3):

=IF(F3-10<0,0,IF(F3-20<0,1,"Out of Range"))

for three ranges:

=IF(F3-10<0,0,IF(F3-20<0,1,IF(F3-30<0,2,"Out of Range")))

and so on...

Another way could be to parse the number:

=IF(LEN(TEXT(F3,0))>1,left(TEXT(F3,0),1),0)

The above will work for TEN ranges 0÷9 to 90÷99

A better approach could be to "dynamically" find the length to be parsed:

=IF(LEN(TEXT(F3,0))>1,left(TEXT(F3,0),LEN(TEXT(F3,0))-1),0)

The above will work for all ranges as long as they are in 10 "steps"

Otherwise you will need to build a table like this say in colums M and N (in the example with 5000 "pairs"):

0 0
1 0
2 0
....
10 1
11 1
....
325 This can be anything
326 This can be anything
...
4217 A suffusion of yellow
4218 A suffusion of yellow

then use:

=VLOOKUP(F3,M1:N5000,2,False)

The table can also be built on another sheet, of course.

Please take note of the last parameter of the above formula, if it is 0 or False, it will search for an EXACT match, thus you will need to build a table with ALL possible values, but if it is 1 or True, it will get the nearest value it can find LESSER than the searched one, thus you can build a table with just the starting figure of each range. For the latter to work, the table MUST be ordered with increasing values.

jaclaz

Edited June 6, 2007 by jaclaz

[RodentMeat]

I tried the last piece of code involving the long table and it works~! Thanks a bunch, jaclaz.

[jaclaz]

Was there a doubt it wouldn't work?

Thanks a bunch, jaclaz.

No prob, mate.

jaclaz

Edited June 6, 2007 by jaclaz

[spacesurfer]

Goodness gracious, jaclaz. You don't need ANY of that.

Just use the floor function. One elegant formula will do that:

Put this in I3:

=floor(F3/10,1)

It will divide a value in F13, say 18, by 10. You get 1.8. The FLOOR function floors the value of 1.8 to 1. All values between 1.0 to 1.9 will be floored to 1, including 1.0 and 1.9. All values between 2.0 and 2.9 will be floored to 2, including 2.0 and 2.9.

Hope that's a simpler solution than building ranges and using VLOOKUP.

(There's also a CEILING function that will increase the value to the next greatest integer, meaning 1.8 will become 2. 2.4 will become 3. This is different from rounding.)

Ha! I was a math wiz.

Edited July 2, 2007 by spacesurfer

[jaclaz]

Yes, spacesurfer, you are perfectly correct, the

=FLOOR(F3/10,1)

function would work, as well as these other (simpler) two :

=ROUNDDOWN(F3/10,0)
=INT(F3/10)

but only in this particular case, (where a numeric value is requested as end result).

I tried to give a more "general" solution, including the ability to output text or whatever is in the lookup table, i.e. to possibly replicate the behaviour of the I-ching calculator found by Douglas Adams' character Dirk Gently:

'The device also functioned as an ordinary calculator, but only to a limited degree. It could handle any calculation which returned an answer of anything up to "4".'

'"1 + 1" it could manage ("2"), and "1 + 2" ("3") and "2 + 2" ("4") or "tan 74" ("3.4874145"), but anything above "4" it represented merely as "A Suffusion of Yellow".

The calculator is now available on-line as a java script:

http://www.thateden.co.uk/dirk/

And, just for the record , I was a math wiz too.

jaclaz