VLOOKUP, MINIMUM to zero everytime?

[srands]

Hi I need to EDIT/REPLACE the following formula so that it counts from zero in each CATEGORY:

=LOOKUP(E4,{0,101,301,501,801},{"A","B","C","D","NEXT"})

At the moment the SPREADSHEET formula to replace the COLUMN F formula, WRONGLY counts 0 to 100, then 101 to 300, then 301 to 500, then 501 to 800, then 801 (Or something like that).

I WANT THE FORMULA TO COUNT AS FOLLOWS to CALC WHICH CATEGORY THE ITEMs ARE, PER ROW:
0 to 100, 0 to 300, 0 to 500, 0 to 800.

DESCRIPTION:
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.

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)

SEE SPREADSHEET IN PROGRESS

Cheers for your help as this conundrum has been troubling me for sometime!

Stephan

 

[srands]

Thanks! Those formulas work great, two other issues:

1) In the last £ per Range letter, cell M21. It can exceed it's maximum.
what formula do I need to so that in excess of 800, will only be in cell N22?

2) In maths terms what is being expressed in COLUMN U?
Or in an equation what is this doing?

SPREADSHEET

Cheers, for your help

Stephan

 

[srands]

Re: VLOOKUP, with MINIMUM & MAXIMUM per range?

Hi, ALSO previous to my previous email, one other question, in COLUMN J (SPREADSHEET) there is a MINIMUM figure per each range, at the moment it is presumed this is always ZERO.

Q1. What formula is needed to consider MIN values in J2:J21?
NOTE: Categories only, to be filled in order.

EG:

RANGE GROUPS:

MIN MAX CAT
20 100 A
5 300 B


ROWS:

ROW 2, 50, cat A
ROW 3, 10, cat B

OK, that would be great, for this spreadsheet to be perfect.

CHEERS STEPHAN

PREVIOUS QUESTION
"1) In the last £ per Range letter, cell M21. It can exceed it's maximum.
what formula do I need to so that in excess of 800, will only be in cell N22?

2) In maths terms what is being expressed in COLUMN U?
Or in an equation what is this doing?
What would this be in a IF FORMULA?

SPREADSHEET"

 

[srands]

Hi AlphaFrog, here is my most current version (1st tab, and 2nd/3rd tab is examples of the real purpose/intension)

I've resolved the problem of the last cell count (M21) exceeding the absolute maximum of all 5 RANGES.

Q. What would be useful is to have formula to consider MINIMUM values (COLUMN J) for each CATEGORY (for every RANGE).
Any formula suggestions?
Could the formulas in this other spreadsheet on the 1ST TAB, be useful to replace/include current formulas with the same layout?

Or in my most recent TIER CATEGORY TYPE spreadsheet, the following I've edited to consider MINIMUM values, but they are not as accurate the VLOOKUP formula that considers remainding values, but perhaps formulas can be edited?,
See my category spreadsheet tabs as following (6th tab, 7th tab & 8th tab):
CAT TIER "if" MIN&MAX 1col MAX
CAT TIER "if" MINMAX 6cell
CAT TIER"if"MINMAX 1cellNOTxl07

Cheers

Stephan

EXAMPLE OF FUSEBOX MCB CALCULATOR for LIGHTING CIRCUTS ON CATEGORY TIER SPREADSHEET 2ND & 3RD TAB

EXAMPLE OF SIMPLE ELEC/WATER/GAS CALCULATOR ON MONEY TIER SPREADSHEET 2ND/3RD/4TH TAB

MY VERY COMPREHENSIVE ELECTRICITY WATER GAS ENERGY UTILITY METER COST CALCULATOR, 1ST TAB, SCROLL DOWN

 

[AlphaFrog]

I thought the minimum for a given range was the max+1 of the previous range. So I don't understand what you want. I did look at your examples but I still don't get it.

If the min is not the max+1 of the previous, what happens if there is overlap or a gap between two ranges?

 

[srands]

Hi AlphaFrog . Thanks, I know what I'm asking is a little hard to follow.

YOU MAY ASK HOW IS WHAT I WANT, is any DIFFERENT FROM MY MOST CURRENT VERSION?
~ Currently the MIN per row is not considered.
~ Currently the MAX is just per CATEGORY.

The additional items I want added to spreadsheet are:
~ MINIMUM value for each ITEM ENTRY (PER ROW of datalist), COLUMN J (not functional yet)
~ MAXIMUM value for each ITEM ENTRY (PER ROW of datalist), COLUMN K (not functional yet)
~ MAXIMUM value PER CATEGORY, COLUMN L (Functional already)

To describe in other words:

5 RANGES i to iv, each with : 4 CATEGORIES of: A, B, C & D.

For each CATEGORY there is to be a MINIMUM and MAXIMUM value per row, and a MAXIMUM value per CATEGORY.

See this revised spreadsheet of just 2 relevant examples (lookup & if). To edit.

The spreadsheet sorts from the data list (entered row by row) into the CATEGORIES (In order only, from cat A to D, from Range i to v).
If row higher then any MAXIMUM value for any RANGE CATEGORY then this row is the final row, and this exceeded row is TOTALLED in CELL N22 (functional).

RANDOM EXAMPLE:

RANGE i CATEGORY TABLE example:
CATEGORY MIN per row MAX per row MAX per CATEGORY
A 30 35 100
B 40 60 120
C 80 90 150
D 50 100 100

ROWs datalist example:
row2, 40, CAT B, B running total = 40
row3, 80, CAT C, C running total = 80
row4, 50, CAT D, D running total = 50
row5, 50, CAT D, D running total = 100

TOTAL so far:
range i total = 270

Can you see the logic?

Cheers

Stephan

 

[srands]

Hi AlphaFrog, in short, perhaps you can suggest the right formula for COLUMN U so it can be edited to include all of the following:

MIN per row, COLUMN K
MAX per row, COLUMN L
MAX per CATEGORY, COLUMN M

The formula in COLUMN U is at the moment, for example in cell U3:
=IF(MIN($E$2:$E$46)>SUM(L2,U2),U2,LOOKUP(SUM(L2,U2),$E$2:$E$46,$E$2:$E$46)+1)

See spreadsheet with VLOOKUP formula for editing.

This formula, at the moment only accounts for: MAX per row.

Cheers for your help on this, as lookup is not something I know about and haven't found any good descriptions/explanations as yet.

Thanks

Stephan

mail@srands.co.uk