+ Reply to Thread
Results 1 to 2 of 2

Alter formula to get sum without any value being less than zero.

  1. #1
    Registered User
    Join Date
    03-03-2020
    Location
    PK
    MS-Off Ver
    MS Office 365
    Posts
    56

    Alter formula to get sum without any value being less than zero.

    My goal is to find the % rent for each month to equal $36,868 without a month being negative, like you see in columns Z and AA

    What I'm trying to do is calculate what the rent would be over the Breakpoint (if you look in line 8 you will see the example in yellow that I'm trying to solve. in columns Z and AA I need the numbers to be positive... while the AG still calculates to the 12% over the breakpoint.

    For example, 1,121,616-814,382 = 307,234

    Then 12% of that = is 36,868...

    My goal is to find the % rent for each month to equal $36,868 without a month being negative, like you see in columns Z and AA

    Is there a way to alter the formula of cells U8:AF8 in such a way that no value is less than zero and the total comes out to 36,868 (12% of T8-C8).
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Alter formula to get sum without any value being less than zero.

    Just my guess

    =($T8-$C8)*$D8*H8/SUM($H8:$S8)

    or
    =MAX(0,(H8-$C8/12+SUMPRODUCT($H8:$S8-$C8/12,--($H8:$S8-$C8/12<0))/(12-COUNTIF($H8:$S8,"<"&$C8/12)))*$D8)

    or
    =MAX(0,(H8-$C8/12+(H8-$C8/12)*SUMPRODUCT($H8:$S8-$C8/12,--($H8:$S8-$C8/12<0))/SUMPRODUCT($H8:$S8-$C8/12,--($H8:$S8-$C8/12>0)))*$D8)
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need to alter my formula to inverse my results
    By Modify_inc in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-05-2019, 12:53 AM
  2. [SOLVED] Alter this array formula
    By julhs in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-23-2017, 01:10 PM
  3. [SOLVED] formula returns 0 instead of #N/A.... i want to alter this
    By R.Sloan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-26-2013, 10:44 AM
  4. Hide Formula and cannot alter
    By johnph in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-20-2012, 10:18 AM
  5. Macro to alter a variable formula
    By gakusei4 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-04-2008, 05:34 PM
  6. Alter value in one cell alter value in multiple cells
    By Bryan Noqw in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-11-2008, 05:31 AM
  7. macro to alter a formula
    By Crispy1974 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-21-2007, 10:42 AM

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