I have a very long list of values.
I would like to categorize them into different groups (even the duplicates)
examples of the groups would be:
" Group A (or 1) = 0 < x < 5 "
" Group B (or 2) = 6 < x < 14 "
....
" Group G ( or 7) = x > 70 "
How would i create the formula assuming the values i want to use are all within the same column?
The use of this formula is to find the total quantity of items that fall within a particular price range (the 0 and 5 in the example above would be dollar values) - this is to update total inventory quantities.
any help is appreciated.
-thx
__________FURTHERMORE___________
I saw this response in another thread which seemed to be what i wanted however i am unsure how to properly type in the formula
Begin Quote:
"I'll assume that you've already determined the number of categories you'll use. Let's say you want to use 20 categories. Then they will be:
Category 1: [0 - 100,000) Category 2: [100,000 - 200,000) Category 3: [200,000 - 300,000) ... Category 19: [1,800,000 - 1,900,000) Category 20: [1,900,000 - 2,000,000] Note that the label of each category can be defined as
FLOOR (x / category_size) + 1
This is trivial to define as a computed column in SQL or as a formula in Excel.
Note that the last category is infinitesimally larger than the others, since it is closed on both sides. If you happen to get a value of exactly 2,000,000 you might erroneously classify it as falling into category 21, so you have to treat this exception with an ugly "IF" (in Excel) or "CASE" (in SQL)." /end
I assume that "FLOOR (x / category_size) + 1" is the formula i want to use. however i do not understand how to plug in my cells.