+ Reply to Thread
Results 1 to 6 of 6

Adding Cells, if Sum is Greater than - How much greater?

  1. #1
    Registered User
    Join Date
    05-04-2012
    Location
    San Diego
    MS-Off Ver
    Excel 2010
    Posts
    2

    Adding Cells, if Sum is Greater than - How much greater?

    Good Morning,

    I am looking to make a formula that will add up a range of cells, minus a few other cells, then compare it to 1.75 -- if the sum of those cells is greater than 1.75 -- I want it to reply with the how much greater the value is than the 1.75

    So far example.

    Cells K2:DD2 - Cells K2, L2, M2, N2, O2 = 2.75

    I want it to see that 2.75 > 1.75 and the result displayed in the cell would be 1 (it's 1 point greater than its comparison) If it is NOT greater than 1.75, I want it to display 0

    I think it would be something like this:

    =(IF(AND(SUM((K2:DD2)-K2,L2,M2,N2,O2)),>1.75),1.75-(SUM(K2:DD2)-K2,L2,M2,N2,O2)),0)

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Adding Cells, if Sum is Greater than - How much greater?

    I dunno that you need that AND statement in there.

    Please Login or Register  to view this content.
    Shouldn't that do it?



    This is unsolicited but if it was up to me though it would look more like this:
    Please Login or Register  to view this content.
    And H1, I1, and J1 would have text strings to label the values next to them for the user.

    BTW SUM(K2:DD2) - SUM(K2:O2) is equivalent to SUM(P2:DD2) but that might just be an artifact of the example and not representative of the real case.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Adding Cells, if Sum is Greater than - How much greater?

    try =if(sum(what-you-want-to-add)>1.75,sum(what-you-want-to-add)-1.75,0)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    05-04-2012
    Location
    San Diego
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Adding Cells, if Sum is Greater than - How much greater?

    Thank you FDibbins, that did work - I gave you reputation from another thread, so it won't let me do it again but much appreciated!

    @ben it was just artifcat of the example-- and I need the AND in there for other arguments, but I know how to do the other arguments and didn't want to waste any time, much appreciated to the both of you.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Adding Cells, if Sum is Greater than - How much greater?

    glad to help, shout if you need anything else

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Adding Cells, if Sum is Greater than - How much greater?

    or simply
    =max(sum(what-you-want-to-add)-1.75,0)
    Josie

    if at first you don't succeed try doing it the way your wife told you to

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1