Hi I want a spreadsheet to make cumulative calculations from tier ranges, per many rows of data.
The formula needs to fill the CATEGORIES in order of RANGE GROUP i):
"A", then "B", then "C", then "D", then if possible count starts again at "A"
(Note: How to do this in a formula(s) from either a:
~ RUNNING TOTAL or ~ CUMULATIVE TOTAL. To exclude value of already counted CATEGORIES.
In the DATA RANGE the variable total individual value will decide the category of that row, from the following RANGES.
TIER RANGES:
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.
Cheers
Stephan
The formula needs to fill the CATEGORIES in order of RANGE GROUP i):
"A", then "B", then "C", then "D", then if possible count starts again at "A"
(Note: How to do this in a formula(s) from either a:
~ RUNNING TOTAL or ~ CUMULATIVE TOTAL. To exclude value of already counted CATEGORIES.
In the DATA RANGE the variable total individual value will decide the category of that row, from the following RANGES.
TIER RANGES:
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.
Cheers
Stephan