Remainder to be Subtracted from the next Instance Found

[DragonJad]

Hi All,

I'm having difficulties figuring out what function to use in the OUT column (B) to calculate the following:

If the number in OUT (G) is greater than the TOTAL C then it should only show the MAX of TOTAL (C) otherwise subtract the remainder from the next instance of the colour (A)
Blue is 5 TOTAL - therefore the first instance of Blue is 3 and the next should be 2, not 3 each.

Would this be a nested IF with MOD? Any advice would be appreciated! Thank you

 

[StephenCrump]

Like this?

	A	B	C	D	E	F	G
1	Colour	Out	Total			Colour	Out
2	Yellow	2	2			Yellow	7
3	Green	1	1			Green	2
4	Yellow	5	16			Blue	5
5	Blue	3	3
6	Blue	2	3
7	Yellow	0	5
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=MIN(C2,VLOOKUP(A2,F$2:G$4,2,)-SUMIF(A$1:A1,A2,B$1:B1))

 

[DragonJad]

You Sir, receive 100 respect points for this!
I was so very wrong with all the functions I thought it could be! Thank you very much