Finding the right Microsoft Excel formula

November 9, 2006 11:01 AM

I've got an Excel spreadsheet with numbers ranging from 2,000 to 600,000. I want Excel to look at each cell and essentially categorize all of those into five categories in a new column. If the value is less than 10,000, I want Excel to give it a 1, if the value is between 10,000 and 50,000 I want it to be 2, if the value is between 50,000 and 100,000, I want it to return 3, if it's between 100,000 and 500,000, I want a 4, and if it's above 500,000, I want a 5. I tried the IF function, but I can't figure out how to a) make it recognize multiple variables, and b) I don't know how to tell it to look for numbers within a range. The Excel help section about arrays and lookups is fairly mystifying. Anyone got an idea? (I know I could sort the data and do this manually, but I'd like to learn how to do this Excel's way.) Thank you!
posted by stonefruit to computers & internet (14 answers total)

Nested if statements:

=IF(A1<10001,1,IF(A1<50001,2,IF(A1<100001,3,IF(A1<500000,4,5))))
posted by grateful at 11:11 AM on November 9, 2006

In a situation like this, I would write a Visual Basic function and call it from the cell.

Plus, there are more good books about coding in Visual Basic than there are good books about writing intricate excel formualas.
posted by gmarceau at 11:21 AM on November 9, 2006

grateful has it; using only cell formulas, there's no way to directly say (if A<X<B then Y) -- you have to do it as (if A<X then (if X<B then Y)).

Personally, I'd go gmarceau's route and create a custom function because it would be easier to maintain, but if you're just looking for quick and dirty, nested IFs are the way to go.
posted by Doofus Magoo at 11:34 AM on November 9, 2006

If you're looking for a way to view large chunks of data easily and figure out how they compare, this post shows an awesome way to put mini-bar-graphs into a column beside the data (and it's super easy). It makes it really easy to digest. There's also this follow-up post with more tips.

Also, you could accomplish this sort of thing with conditional formatting. You could have each range of values represented by a different font or background color. Conditional Formatting is under the Formatting menu.
posted by dripdripdrop at 11:46 AM on November 9, 2006

I think that the LOOKUP function can accomplish this, but don't have the time to experiment with it.

There are two versions... array and vector. I think the vector one is the one you want.
posted by FauxScot at 11:59 AM on November 9, 2006

The nested IF function will do exactly what you want. I use it all the time for this kind of thing. Just use grateful's formula and copy it down for the rest of your data.
posted by richmondparker at 12:04 PM on November 9, 2006

Writing a VB function for this seems like overkill.

Nested if's will work fine, but it could also be accomplished with a lookup table. The advantage of a lookup table is that you can change categorizations at will, without having to adjust any formulas.

Somewhere in your spreadsheet, say, starting in cell A1 of a new tab named Sheet2, you could place the following table (first column indicates lower category limit; second column is the category name):

0          1
10000      2
50000      3
100000     4
500000     5

Assuming your numbers are in column A of Sheet1, and your data range begins at cell A1, you'd enter the following formula into cell B1 of Sheet1:

=VLOOKUP(A1,Sheet2!$A$1:$B$5,2,TRUE)

Now, copy that formula all the way down to the end of your data range. You should now see an appropriate category number beside each number in column A.
posted by treepour at 12:18 PM on November 9, 2006

Yep, grateful's method is fine for what you have. Allow me to suggest an alternate solution--which I don't really recommend for your specific case, since grateful's method will do just fine--but is more scalable and would be nice if you had, say, 50 different ranges to categorize instead of 5.

Create a table in a blank area of your worksheet, or in a separate worksheet, that looks like this:

  2000       1
 10000       2
 50000       3
100000       4
500000       5

Let's say this table is in cells A51 - B55, and the first value you want to convert is in A1. The formula you can use here is:

=VLOOKUP(A1,$A$51:$B$55,2,TRUE)

VLOOKUP and HLOOKUP are your friends. If you want to be a power Excel user, learn and love these functions.
posted by DevilsAdvocate at 12:28 PM on November 9, 2006

Er, on failing to preview, exactly what treepour said.
posted by DevilsAdvocate at 12:29 PM on November 9, 2006

Vlookup.

set up a table as follows in row1-6, column A-C

0 10000 1
1000150000 2
50001 100000 3
100001 500000 4
500001 999999 5
1000000 1000000 6

in a column next to your column that contains the data, use VLOOKUP as follows:

=VLOOKUP(your value,$A$1:$C$6,3)

It will return the proper value. Use the $ signs and you can copy it into other cells without adjustment.

The other methods, while workable, are not as elegant or as general or as easy to change.
posted by FauxScot at 12:38 PM on November 9, 2006

Vlookup.

set up a table as follows in row1-6, column A-C

0 10000 1
10001 50000 2
50001 100000 3
100001 500000 4
500001 999999 5
1000000 1000000 6

in a column next to your column that contains the data, use VLOOKUP as follows:

=VLOOKUP(your value,$A$1:$C$6,3)

It will return the proper value. Use the $ signs and you can copy it into other cells without adjustment.

The other methods, while workable, are not as elegant or as general or as easy to change.
posted by FauxScot at 12:38 PM on November 9, 2006

sorry for the double post!
posted by FauxScot at 12:39 PM on November 9, 2006

I think it can safely be said that the eagerness with which the VLOOKUP recommenders posted their responses is a testament to the power & usefulness of the technique . . . !
posted by treepour at 4:01 PM on November 9, 2006

These are all fantastic suggestions -- thank you! I tried both nested IFs and VLOOKUP, and of course they both work. Thanks to everyone - love the hive mind at work.
posted by stonefruit at 4:13 PM on November 9, 2006