How to group data in Excel?

Question

I have a vast amount of data in an excel spreadsheet e.g. average weight from 0 to 10,000 or amount from 0 to 1 million

I need to group the entries by categories of let's say:
0-200
201-300
301-400
etc.

The idea is to create Thresholds that could be grouped in a pivot table or an excel chart.
There could be a way with the IF function but excel inly supports 6 consecutive IFs in the same function.

Would there be any way to create a table with thresholds and the link it to a column of my data.

Thank you in advance

Answer 1

This might be something a pivot table would be good for, but I've never used them, so I'll suggest something else...



Create a table somewhere, as you mentioned, with your thresholds. Say in AA1:AB10

AA1:AA10 = the lower limit (i.e. 0, 201, 301...)

AB1:AB10 = upper limit (i.e. 200, 300, 400...)



In your new column next to your data, use a VLOOKUP formula to look at your data (say in B1) and return the threshold it belongs to:



= VLOOKUP( B1, $AA$1:$AB$10, 1, TRUE)



Copy/drag the formula down. The above will result in either "0" or "201" or "301" for each value in column B. If this is all you use, the second column of your lookup table isn't necessary. However, if you want the result to be "0 - 200" or "201 - 300" then use this formula:



= VLOOKUP( B1,$AA$1:$AB$10, 1, TRUE)&" - " &VLOOKUP( B1,$AA$1:$AB$10, 2, TRUE)



The "2" tells it to return a result from the second column (AB). The TRUE tells it to match with B1 or the next closest but lower value. (FYI: FALSE demands an exact match only).

Answer 2

hint .. you may stumble on it a lot less perplexing protecting your information in a database and utilising its speedier aggregation and decision applications in excel, study sumif and countif you'll likely have a summary table with one or 2 columns having bands of values and sumif( row$col formula, information ) on your totals column

Answer 3

hint .. you might find it easier keeping your data in a database and using its faster aggregation and selection functions



in excel, have a look at sumif and countif

you would have a summary table with one or two columns having bands of values and sumif( row$col formula, data ) in your totals column