I want a spreadsheet to make CUMULATIVE calculations from TIER ranges, per many rows of data.
The formula needs to fill (As much possible, without going over the max), the CATEGORIES only in order of RANGE GROUPS (5 ranges, each with 4 different categories):
"A", then "B", then "C", then "D", then if possible count starts again at "A", etc.
(Note: How to do this in a formula(s) from either a:
~ RUNNING TOTAL or ~ CUMULATIVE TOTAL. To exclude value of already counted CATEGORIES
LOOKUP with min & max? OR an IF with nested cells to subtract previously counted ranges from RUNNING TOTAL?).
Also CATEGORY max values maybe the same, how to include these, without calc going to next BIGGEST category.
In the DATA RANGE the total (variable) individual value will decide the category of that row, from the following RANGES, then next cumulative unless greater then the max of that category.
CATEGORY MIN MAX
A 0 100
B 0 300
C 0 500
D 0 800
DATA TABLE EG:
ROW 2, MISC ITEM, £50 X1 = £50, Running absolute total = £50, CATEGORY = A (Range A total so far = £50)
ROW 3, MISC ITEM, £30 X1 = £30, Running absolute total = £80, CATEGORY = A (Range A total so far = £80)
ROW 4, MISC ITEM, £30 X1 = £30, Running absolute total = £110, CATEGORY = B (Range B total so far = £30)
ROW 5, MISC ITEM, £50 X4 = £200, Running absolute total = £310, CATEGORY = B (Range B total so far = £230)
ROW 6, MISC ITEM, £80 X1 = £80, Running absolute total = £390, CATEGORY = c (Range c total so far = £80)
Formula to calculate CATEGORY would be great.
IN OTHER WORDS:
I just need a formula to FILL the ranges (5 ranges each with 4 different categories) only in ORDER as per RANGE/CATEGORY min & max table, so filling in previous ranges is NOT possible, only current, if ITEM too large then next CATEGORY/RANGE that it fits in, etc.
RANGE MINs & MAXs: MIN =K2:K22 MAX L2:L22: see spreadsheet.
NOTE: I need to change these range min and max values as & when.
What would you want to happen if the first item cost £400 and the second item £50?
Item 1 & 2, C range i (Running Total £450),
If items 3, 4 and 5 cost £30, £50 and £100, where should these go?
Item 3, C range i (Running Total £480)
Item 4, D range i (Running Total £150)
HERE IS A DRAFT OF MY SPREADSHEET IN PROGRESS, COLUMN's F, G & O2:O21 are the formula's that need REPLACING/EDITING
Cheers for any help, much appreciated, as this dilemma has been troubling me for sometime! I have also posted this question with other XL forums, without any success so far, and even after searching through all the XL4kids websites, I haven’t found a formula that would edit to work with my tier category/range!
OTHER Q’s POSTED: