Sum up to certain value ???

Roelf

New Member
Joined
Nov 2, 2018
Messages
6
5UEDTlB


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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,683
Welcome to the forum.

Try:

BCDEFGHIJKLMNOPQRSTUVWXY
3010203040506070809101112
4MonTueWedThuFriSatSunMonWedThuFriTarget20Carry Over
5O/T20.516O/TD/TPPHPPH O/TO/TD/TPPHPPH O/T
6D/T1118.51.50009.556
7PPH5
8PPH O/T6

<tbody>
</tbody>
Sheet5

Worksheet Formulas
CellFormula
P6=MIN($S$4-SUM($O6:O6),SUM(INDEX($C5:$N8,COLUMNS($P:P),0)))
U6=SUM(INDEX($C5:$N8,COLUMNS($U:U),0))-P6

<tbody>
</tbody>

<tbody>
</tbody>




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

Roelf

New Member
Joined
Nov 2, 2018
Messages
6
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,683
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! :p
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 . . . :coffee:
 

Roelf

New Member
Joined
Nov 2, 2018
Messages
6

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,683
Can you give an example where it doesn't work as you expect?
 

Roelf

New Member
Joined
Nov 2, 2018
Messages
6

ADVERTISEMENT

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

Board Regular
Joined
Jun 29, 2018
Messages
131
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?
 
Last edited:

Roelf

New Member
Joined
Nov 2, 2018
Messages
6
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,683
Yeah, that's a much tougher proposition. Try this:


BCDEFGHIJKLMNOPQRSTUVW
3123456789101112
4MonTueWedThuFriSatMonWedThuFriTarget20Carry Over
5O/T5555O/TD/TPPHPPH O/TO/TD/TPPHPPH O/T
6D/T5155005000
7PPH
8PPH O/T
9
106

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
P6=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)
T6=SUM(INDEX($C5:$N8,COLUMNS($U:U),0))-P6

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
P10{=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)}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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.
 
Last edited:

Forum statistics

Threads
1,148,108
Messages
5,744,875
Members
423,907
Latest member
zerocool88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top