Cumulative totals

Hi

I would really appreciate your help if the below is indeed do-able.

This problem has come at the end of a big exercise whereby I've managed to construct a spreadsheet that automates three payment processes based on various VLookups to another file. However, I've come to a stage where I need to pre-empt a payment allocation based on cumulative totals.

I'll try and explain clearly below but I've also attached a spreadsheet showing the intended result (along with a copy without the output so that someone can add in the formula - again, if it exists.

So, this all centres around a code allocated to our clients and a declaration that they complete. Say client code "Apple1" (col E) sends in instructions to pay on a position of "650,000" (col F). Within our existing spreadsheet we have an available position of "800,000" which can be seen by totalling column G for client code "Apple 1".

So that's the first requirement - for all rows on my existing spreadsheet for client code "Apple1", I want to add the cumulative total of column G ("800,000") I'd like this captured in column H, as per my example.

We can then make payment based on their declaration, up to their total available amount. However, we need to pay them in stages according to our existing allocations (col G). Therefore, taking the first example, their declaration show's an available position of 650,000, their total position is 800,000 and the available nominal amount for that row (col G) is 200,000 - so they can be paid on that full amount therefore, 200,000 should be shown in cell I3.

So now, they've got 450,000 left to be paid and cell G4 again shows a position of 200,000, so once again, cell I4 should show 200,000.

The client "Apple1" now has a payment amount remaining of 250,000 remaining but in this instance, cell G5 is for 300,000 - so because the remainder of their available payment is less than the amount in cell G5, we should post the available payment amount in that field - 250000.

Therefore, if you add up all of column I for client "Apple1" it comes to 650,000 - the amount we have on our declaration - even though the total of their available position is 800,000.

I've included another client in the mix "Sauce2" who should show 200,000 and 0 respectively in cells I6 and I7.

Apologies if I've confused things but as i mentioned above, I've included an excerpt of my spreadsheet showing the values I'd want to see (and an example without those values for the formula to be added to.)

Thanks in advance for your help on this.

Hi,

You said

Originally Posted by sh3896

So now, they've got 450,000 left to be paid and cell G4 again shows a position of 200,000, so once again, cell I4 should show 200,000.

Thanks in advance for your help on this.

however G4 shows 300,000.

Can you clarify. It would also assist if you could identify the arithmetic and which cells produce the values in the pay amount, and the conditions.

So far I've assumed I3 is if H3 - F3 is less than G3, then G3, otherwise ????

and there you stumped me because of the G4 reference.

Incidentally, you can use SUMIF() for the Total Nominal values.

i.e. in H3

Please Login or Register  to view this content.

Rgds

Hi

Thanks for coming back to me.

Apologies, I made a mistake which accounts for the confusion - cell G4 is indeed for 300k. I've reattached my spreadsheet with correct values.

Essentially, I want to work out how to calculate the values in columns H and I.

Column H is the total of column G in relation to whatever is in coloumn E - so, where column E shows "Apple 1", all the column Gs for that code add up to 800,000 - so I'd expect to see 800,000 in cells H3, H4 and H5.

However, coumn I represents how we wish to allocate payment in respect of what is available. We can only pay up to the amount in column F (again in relation to col E) and we can only pay incrementally by up to the amount in column G.

So

If column F shows 650,000 and column H shows 800,000 that means we can pay up to 650,000 of the available 800,000.

G3 shows 200,000, so we can post that value into I3.
G4 shows 300,000 (*** total 500,000) so we can post that value into I4
G5 shows 300,000 (but we only have a remaining amount of 150,000) so we post 150,000 into I5

I hope this explanation helps.

Thanks again - this is the last piece in my jigsaw and there's a e-beer in it for whoever cracks this for me...

Hi Steve,

See the attached.

I've added a helper column J which simplifies the formulae somewhat. I also moved the original data out of the way so that the H formulae weren't affected by duplicate codes.

HTH

sh3896, do not use terms such as "Please Help" in your thread titles - it adds no value to your post and reduces the effectiveness of the search feature on the board.

On this occasion I have modified for you - please ensure going forward your titles comply with the requirements set out in the Forum Rules - to which you agreed on registration.

TIA.
D.O.

Richard - that's brilliant, thanks so much. It's 7pm here and I can now leave the office happpy!!!

DonkeyOte, I hear you...

Thanks again.

Hi ,

Glad to help.

Just realised I hadn't generalised the H12 cell which would be better as:

Please Login or Register  to view this content.

Hi Richard et al

I've been trying to integrate your solution into my own spreadsheet with little success so far. I'm sure I'm just missing something obvious but I've been through your formula in order to change the relevant field settings - but I'm obvioulsy doing something wrong as the output is not as it should be.

I've attached the spreadsheet with this posting and have taken out all the sensitive data just leaving the relevant columns in the place where they'll actually be.

Would appreciate it if you would take a look to see where I'm going wrong. The column I''m struggling with is highlighted blue.
I've included the original solution in the second workbook for ease of reference.

Once again, thanks for all your help thus far - it's been much appreciated...

Hi Steve,

Unfortunately I'm away in India at the moment without access to Excel so can't check this out until I return next weekend. Hopefully someone will step in before then, but if not please remind me when I'm back.

Rgds

Just managed to see the workbook with a viewer.

Change the Y3 cell to

Please Login or Register  to view this content.

Thanks Richard

However, when I do that it causes a problem if the declaration amount is greater than the nominal but less than the total nominal.

In my example, in row 3 the nom is 200,000, total nom (for the three connected rows) is 800,000 and the Declaration is for 400,000. It posts the full 400,000 against the first row - which should have a 200,000 limit (bases on the nominal field, O3)

It works perfectly within the original spreadsheet you updated - but I've been having these kind of issues when i've copied it over.

Appreciate you're obvioulsy busy with other things but if you do get the chance it would be great to finalise this one.

new file attached.

Once again, thanks for your help...

hi

Just wondering if anyone has an answer to this problem...

on the last attachment, theres a solution on one worksheet but I cannot apply it to the real file on the other worksheet...

thanks

Hi Steve,

In Y2 and copied down try:

Please Login or Register  to view this content.

Rgds

Hi

Me again...sorry!!!

I reattach the spreadsheet where I would appreciate help in applying the solution to the columns highlighted (that are in the correct order - the spaces repsresent columns that aren't needed for this issue, so I've removed them.)

However, in the second worksheet where I attach the original solution provided very kindly by Richard, i've highlighted a cell that is returning a minus value, when in fact the value it shoudl return is "100000"

(as a reminder, the logic is:

-where the "code" is the same
-the "pay amount" per row must not exceed the total in the "nominal" column for that row
- but, the "pay amount" for each "code" - taking into account all the rows with the same code - can be paid up to the amount in the "declaration" column.
- so there should never be any minuses!!! )

Thanks to Richard for his help so far and to eanyone that can help me put this to bed.

Hi Steve,

In this latest s/s, in I12 copied down

Please Login or Register  to view this content.

Rgds

Hi Richard

Thanks for coming back to me, really appreciate your patience.

There seems to be a problem if the "declaration" amt = zero. If that's the case, irrespective of what the "nominal" or "total nominal" is, the "Pay Amount" column should be "0".

Please see updated file.

Thanks

...for some reason it's not letting me upload the file...!

Hi Richard

I've managed to apply the formula you provided - I'd not rolled on the indirect function you used.

Thanks for your help - and sorry this went on so long.

Regards