IF FORMULA, exclude previous counted ranges, 4+ TIER

**srands** · 02-08-2011, 10:20 AM

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)

The formula I use, is an IF FORMULA, however it DOES NOT exclude previously counted ranges, hence once past CATEGORY A, it doesn't count from zero, just from the total count, which I don't want.

CURRENT FORMULA TO EDIT, TO EXCLUDE PREVIOUS COUNTED RANGES:
=IF(E2<=$L$2,$M$2,IF(E2<=$L$3,$M$3,IF(E2<=$L$4,$M$4,IF(E2<=$L$5,$M$5,IF(E2<=$L$6,$M$6,IF(E2<=$L$7,$M$7,IF(E2<=$L$8,$M$8,IF(E2<=$L$9,$M$9,"NEXT"))))))))

NOTE: Perhaps a formula that specifies MIN (Eg: $K$2) as well as MAX (Eg: $L$2) per nested calculation, ANY OTHER FORMULA SUGGESTIONS?

Formula to calculate CATEGORY would be great.

NOTE: I've posted this question with mrexcel forum as well, but without success (Or in otherwords to fruitless avail) http://www.mrexcel.com/forum/showthread.php?t=527228

Cheers

Stephan

**ChemistB** · 02-08-2011, 11:13 AM

Here's what I would do,
First make a table for VLOOKUP like so
0 ---A
100--B
300--C
500--D Let's say it's in U2:V5

Then in F2 Dragged down
=IF(A2<>"",VLOOKUP(MOD(E2,800),$U$2:$V$5,2,TRUE),"")

Is that what you need?

This should pick the appropriate "range". It doesn't alert you if you go over your 5 Range Limit. If that happens, do you want "Next"?

**srands** · 02-08-2011, 10:29 PM

Hi thanks but your formula =IF(A2<>"",VLOOKUP(MOD(E2,800),$U$2:$V$5,2,TRUE),"") doesn't calc the results I need, so I added some more details below to give you a better idea of what I need, thanks for your help.

I 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.

SCENARIOS:
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

DETAILED INSTRCTIONS: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 OF, 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.

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

see spreadsheet