Sum up to certain value ???

[Roelf]

Hi there, it's been a while since had such a hard time figuring out what I need to do with certain formulas. My question is this. I have a sheet with days and the overtime that is worked on those days. The overtime is split up in 4 categories. OT, DT, PPH, PPH OT. Now the target for the overtime to be worked is 20, anything above that will be moved into "carry over" cells corresponding to the category that it was worked on. The problem I am having is, as the overtime gets worked it must be filled in dynamically into the for categories, but must not exceed 20 in total, anything above the 20, irrespective of the category, must be moved to carry over. I have link an example image of the sheet and what needs to happen. I have searched high and low and tried many solutions, but to no avail. I really hope someone can shed some light on what need to happen to get the formulas right.

I have asked on other forums also but no seems to understand my request. Direct link to image. https://imgur.com/5UEDTlB

First post BTW, hope the link and text is in order

Roelf

 

[Eric W]

Welcome to the forum.

Try:

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Wed	Thu	Fri	Target	Carry Over
O/T	O/T	D/T	PPH	PPH O/T	D/T	PPH	PPH O/T
D/T
PPH
PPH O/T

<tbody>
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01[/TD]
[TD="align: right"]02[/TD]
[TD="align: right"]03[/TD]
[TD="align: right"]04[/TD]
[TD="align: right"]05[/TD]
[TD="align: right"]06[/TD]
[TD="align: right"]07[/TD]
[TD="align: right"]08[/TD]
[TD="align: right"]09[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]O/T[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]18.5[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9.5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]P6[/TH]
[TD="align: left"]=MIN($S$4-SUM($O6:O6),SUM(INDEX($C5:$N8,COLUMNS($P:P),0)))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]U6[/TH]
[TD="align: left"]=SUM(INDEX($C5:$N8,COLUMNS($U:U),0))-P6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




Put the P6 formula in and drag to S6, then put the U6 formula in and drag to X6.

 

[Roelf]

Wow this worked perfectly, amazing how simple it can be ... You helped me so much with this ! ! My GF asks if she can send cookies xd ...........

 

[Eric W]

I've found that "simple" can be very hard to achieve, but sometimes another set of eyes really helps. Glad it works for you.

As far as the cookies, I wish there were a way to send them!
I'm a bit of a cookie-holic, I was actually eating one when I read your message! Maybe there's one left in the box . . .

 

[Roelf]

Hi there, sorry it's been a while before my response, I have done some testing and there seems to be a slight problem, at a glance it was allocating the hours correctly, but somehow it wants to make the 1.5 category full first, then move over to the other categories, I tried messing around with the formula but could not figure it out as to why it was happening, can we maybe have a look at it again ?

 

[Eric W]

Can you give an example where it doesn't work as you expect?

 

[Roelf]

Can you give an example where it doesn't work as you expect?

I have uploaded a sample of how it should work with a brief explanation as to why, hope it makes sense. By the looks of what is happening, the target category first gets filled with the OT, then only DT or any other category, where as is should be, no matter where the data lies, it gets allocated as such.

Link to image
https://imgur.com/a/NEvfIHv

 

[KolGuyXcel]

Just a quick question.

Among the 4 rows 5 through 8, will only 1 of them be filled in each column from C to N? Or could possibly be more than one rows?

 

[Roelf]

Just a quick question.

Among the 4 rows 5 through 8, will only 1 of them be filled in each column from C to N? Or could possibly be more than one rows?

Any one of the four categories per day. So if the guy works OT on one day, none of the other will be filled in on that day, this is applicable right across the board. So in short the answer is yes to your original question ... Does this help in any way ?

 

[Eric W]

Yeah, that's a much tougher proposition. Try this:

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
Mon	Tue	Wed	Thu	Fri	Sat	Mon	Wed	Thu	Fri
O/T	O/T	D/T	PPH	PPH O/T	D/T	PPH	PPH O/T
D/T
PPH
PPH O/T

<tbody>
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]Target[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]

[TD="align: right"]Carry Over[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]O/T[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]P6[/TH]
[TD="align: left"]=SUM(OFFSET($C$5,MATCH(P$5,$B$5:$B$8,0)-1,0,1,$P10))-IF(INDEX($C$5:$O$8,MATCH(P5,$B$5:$B$8,0),$P10)=0,0,SUM(OFFSET($C$5:$C$8,0,0,,$P10))-$R$4)[/TD]
[/TR]
[TR]
[TH]T6[/TH]
[TD="align: left"]=SUM(INDEX($C5:$N8,COLUMNS($U:U),0))-P6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]P10[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF(SUBTOTAL(9,OFFSET(C5:C8,0,0,,COLUMN(C4:N4)-COLUMN(C4)+1))>R4,COLUMN(C4:N4)-COLUMN(C4)+1),1),COLUMN(N4)-COLUMN(C4)+2)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



I had to add a helper formula in P10 (which you can put anywhere, and hide if you want) which basically tells you which column is the one that first exceeds the target.
Then put the P6 formula in and drag to the right. Put the T6 formula in and drag to the right. The T6 formula is the same as before. Note that the P10 formula is an array formula.

This is fairly complicated, someone else could come up with something simpler. I'll look at it a bit more too.