+ Reply to Thread
Results 1 to 7 of 7

Totalling amounts up to a set cost issue

  1. #1
    Registered User
    Join Date
    04-09-2013
    Location
    Wales
    MS-Off Ver
    Excel 2007
    Posts
    5

    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
    Attached Files Attached Files
    Last edited by sashacarna; 04-09-2013 at 03:51 PM.

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,749

    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. #3
    Registered User
    Join Date
    04-09-2013
    Location
    Wales
    MS-Off Ver
    Excel 2007
    Posts
    5

    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. #4
    Registered User
    Join Date
    04-09-2013
    Location
    Wales
    MS-Off Ver
    Excel 2007
    Posts
    5

    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. #5
    Registered User
    Join Date
    04-09-2013
    Location
    Wales
    MS-Off Ver
    Excel 2007
    Posts
    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. #6
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,749

    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. #7
    Registered User
    Join Date
    04-09-2013
    Location
    Wales
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Totalling amounts up to a set cost issue

    Thankyou so much for all of your help )

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts