# Totalling amounts up to a set cost issue

1. ## Totalling amounts up to a set cost issue

Hi all,

Apologies if this has been answered else where but I can't seem to find anything.
I have a spreadsheet monitoring spend of a project there are a few different elements but I have simplified what I need for this purpose.
Each project has a with hold amount that we will pay up to until certain criterias are satisfied and I need a formula to calculate payments up to this threshold but a period claim could take them over the threshold but will require part payment up to the threshold (sorry if I am not being clear).

Please see the attached work sheet.

The formula is obviously doing what I have told it to as in period 8 the sum becomes greater than the with hold threshold so it is returning a £0.00, but what do I need to amend in this to show £6,000 in the period 8 to be paid cell in oder to take the actual amount to be paid to the withold threshold?

Many thanks in advance for your help and I apologise if I am not making sense I have been staring at this for hours now and just don't know what I need to do.

Sasha

2. ## Re: Totalling amounts up to a set cost issue

Try This

``Please Login or Register  to view this content.``
``Please Login or Register  to view this content.``

3. ## Re: Totalling amounts up to a set cost issue

Thanks for your reply, if I use that formula in period 8 only it returns £6000, which is great, but all the other periods have a false. Each project has it's own claiming structure so i will never know which period they are going to reach the threshold to put a different formula in that specific period.

4. ## Re: Totalling amounts up to a set cost issue

Ignore me i missed the last argument off!!! It has been along day That has worked, thank you so much for your help

5. ## Re: Totalling amounts up to a set cost issue

One final question, if the person reaches their withold threshold in Period 4 for example, they will continue to submit claims we just won't pay them so what would I add in for any values after they have reached the with hold to show as a £0.00 rather than a minus figure?

6. ## Re: Totalling amounts up to a set cost issue

``Please Login or Register  to view this content.``
the max function will ensure that no minuses are shown. Max( 0, any -ve no) >>>> 0.

Enjoy.

7. ## Re: Totalling amounts up to a set cost issue

Thankyou so much for all of your help )