Tricky Sum question

[tommacca]

Hi Guys

I have a the table below which I am trying to work backwards to remove a leave balance essentially I want to deduct LSL hours (45) from the hours column working backwards so once 0 is reached in moves to the next row and deducts from that, in this case we would still be at +5 because the LSL hours taken are 45 and the balance is 50 but in the cicumstance where LSL taken was 60 then it would need to minus the 50 display 0 then move to detract the remaining amout from the following row. Is that possible?

LSL Taken	45.00
Total Days Between Start and End	8674
Range	Days	Percentage	Hours
Pre 16/08/78		0.00%
16/08/78 – 17/08/93	940	10.84%	5.4185
Post 17/08/93	7734	89.16%	44.5815
	8674	100.00%	50.0000

<tbody>
</tbody>

 

[Peter_SSs]

Is this what you mean?

If not, please explain the full expected results and layout for the below example.

Formula in E7 copied down.

Excel Workbook
	A	B	C	D	E
1	LSL Taken	45
2
3	Total Days Between Start and End	8674
4
5	Range	Days	Percentage	Hours	Balance
6	Pre 16/08/78		0.00%
7	16/08/78 17/08/93	940	10.84%	5.4185	5.4185
8	Post 17/08/93	7734	89.16%	44.5815	44.5815
9		8674	100.00%	50	5
Deduct Backwards

 

[tommacca]

Hi,

Sorry it would need to deduct b1 from d8, b1 was then larger than d8 it would need to remove the remaning amount from d7 then if b1 was larger than d8 and d7 it would remove the remaining from d6

I think that makes sense?

hope that helps?

Is this what you mean?

If not, please explain the full expected results and layout for the below example.

Formula in E7 copied down.

Deduct Backwards

*	A	B	C	D	E
1	LSL Taken	45	*	*	*
2	*	*	*	*	*
3	Total Days Between Start and End	8674	*	*	*
4	*	*	*	*	*
5	Range	Days	Percentage	Hours	Balance
6	Pre 16/08/78	*	0.00%	*	*
7	16/08/78 – 17/08/93	940	10.84%	5.4185	5.4185
8	Post 17/08/93	7734	89.16%	44.5815	44.5815
9	*	8674	100.00%	50	5

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 223px;"><col style="width: 66px;"><col style="width: 94px;"><col style="width: 80px;"><col style="width: 68px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E7 =MAX(MIN(SUM(D7:D$9)-B$1,D7),0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

Hi,

Sorry it would need to deduct b1 from d8, b1 was then larger than d8 it would need to remove the remaning amount from d7 then if b1 was larger than d8 and d7 it would remove the remaining from d6

You refer to D8, D7 and D6. Your data does not show rows or columns so I assume you are referring to my screen shot. However I did not have data in D6 so I can only assume you mean D9, D8 and D7 in my previous screen shot.

If that is so, I thought my formula is doing what you want.

Here is my sheet again, adjusted the rows to match your description better but with a different value in B1.

Excel Workbook
	A	B	C	D	E
1	LSL Taken	95
2
3	Total Days Between Start and End	8674
4	Range	Days	Percentage	Hours	Balance
5	Pre 16/08/78		0.00%
6	16/08/78 17/08/93	940	10.84%	5.4185	5
7	Post 17/08/93	7734	89.16%	44.5815	0
8		8674	100.00%	50	0
Deduct Backwards

b1 was then larger than d8

It is, so per your first post ".. in the cicumstance where LSL taken was 60 then it would need to minus the 50 display 0 then move .." my formula displays 0 in E8.

it would need to remove the remaning amount from d7

Remaining amount is larger than D7, so my formula displays 0 in E7.

b1 was larger than d8 and d7 it would remove the remaining from d6

It is. The amount removed from the bottom 2 rows was 50+44.5815 = 94.5815. The amount remaining from 95 is 95 - 94.5815 = 0.4185. So remove that amount from D5. 5.4185 - 0.4185 = 5 as displayed in E6.


Are they the numbers you want as your results?

If they are the right numbers but you want them actually in cells D6:D9 rather than beside in column E, then you would need a macro to do that. Is that what you want?

If the numbers are wrong, as I asked before ...

please explain the full expected results and layout