• Formula question

    From This Beard Sells Homes@21:1/5 to All on Thu Sep 15 16:52:34 2022
    I have a spreadsheet that calculates 25% of a variable amount of money and dumps it into the corresponding column for that row. Each row can have a dollar amount inputted. But I want that 25% column to stop calculating once it reaches a specific amount
    entered into a cell somewhere else on the sheet.

    Example: Commission on selling a house is $12,000. 25% is $3,000. But when the SUM of that 25% column reaches, for example, $30,000, I want it to start showing zeros.

    To get more complicated, if the sum of the column stands at $29,000 (leaving only $1,000 left for my $30,000 cap) and the next 25% data entry results in an amount like $2,500, I want that particular cell to read "$1,000", not $2,500. Then I want each "25%
    cell" afterward, below in the column to read zero.

    Is this possible?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Stunn@21:1/5 to This Beard Sells Homes on Fri Sep 16 13:58:45 2022
    Hi,

    Three options. All assume that A1 contains 30000; A2 contains 25%; and C2:C20 contain the commission values.

    These are in order of preference, and although it may not seem like it, they are in order of efficiency.

    The first one is a single-cell formula that generates a multi-cell result. Your version of Excel needs to be bang up to date (in the Beta Channel) for this to work (if you get a #NAME error, your version of Excel isn't suitable for this one):

    =LET(commission,$C$2:$C$20*$A$2,subtotal,VSTACK(0,SCAN(0,$C$2:$C$19,LAMBDA(accum,val,accum+(val*$A$2)))),rwval,IF((subtotal+commission)>$A$1,$A$1-subtotal,commission),IF(rwval>0,rwval,0))

    The next two options will need to be put in a cell in an adjacent column, and copied down (or autofilled) into each row required.

    If you're using a standard up-to-date version of Excel 365, this will work:

    =LET(commission,$C2*$A$2,subtotal,SUM($C1:$C$2)*$A$2,IF((subtotal+commission)>$A$1,MAX(0,$A$1-subtotal),commission))

    For any other version of Excel use this one:

    =IF(SUM($C$2:$C2)*$A$2>$A$1,MAX(0,$A$1-SUM($C1:$C$2)*$A$2),$C2*$A$2)

    It may seem odd to say that these are in order of efficiency, but I promise you they are. For instance, say you have 100 commission values to deal with, that first formula is still a single formula giving you all 100 results with one operation. The
    second and third options both immediately require almost 100 times the operations of that first one, and the last has to calculate a SUM twice for each of those 100 operations. Never be fooled by the argument that shorter is always better.

    Steve D.



    On Friday, 16 September 2022 at 00:52:36 UTC+1, This Beard Sells Homes wrote:
    I have a spreadsheet that calculates 25% of a variable amount of money and dumps it into the corresponding column for that row. Each row can have a dollar amount inputted. But I want that 25% column to stop calculating once it reaches a specific amount
    entered into a cell somewhere else on the sheet.

    Example: Commission on selling a house is $12,000. 25% is $3,000. But when the SUM of that 25% column reaches, for example, $30,000, I want it to start showing zeros.

    To get more complicated, if the sum of the column stands at $29,000 (leaving only $1,000 left for my $30,000 cap) and the next 25% data entry results in an amount like $2,500, I want that particular cell to read "$1,000", not $2,500. Then I want each "
    25% cell" afterward, below in the column to read zero.

    Is this possible?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Philip Herlihy@21:1/5 to All on Sat Sep 17 00:24:37 2022
    In article <001dfd74-f6ec-4afd-9687-4a13bc104838n@googlegroups.com>, This Beard Sells Homes wrote...

    I have a spreadsheet that calculates 25% of a variable amount of money and dumps it into the corresponding column for that row. Each row can have a dollar amount inputted. But I want that 25% column to stop calculating once it reaches a specific amount entered into a cell somewhere else on the sheet.

    Example: Commission on selling a house is $12,000. 25% is $3,000. But when the SUM of that 25% column reaches, for example, $30,000, I want it to start showing zeros.

    To get more complicated, if the sum of the column stands at $29,000 (leaving only $1,000 left for my $30,000 cap) and the next 25% data entry results in an amount like $2,500, I want that particular cell to read "$1,000", not $2,500. Then I want each "25% cell" afterward, below in the column to read zero.

    Is this possible?

    An intriguing puzzle. I don't have time to offer a worked (and tested) solution, but this might point you in the right direction.

    Firstly, it's often easier to work out your solution first in separate columns for each step before trying to combine them. It's ok to hide columns you don't want seen, of course - later.

    You need the sum of the "quarter-commission" column calculated for each row as the number of rows grows. You can use =SUM(A$1:A20) to calculate the sum of rows A1 to A20. If you copy (or Fill) to the next row, Excel will adjust that formula to =SUM(A$1:A21) - the dollar sign stops Excel incrementing that particular row number when copying down, but the other one does increment. I bet there are more elegant ways of doing this sum, possibly involving putting your data in a Table, configuring that Table to show Totals, and referring to the Total of the relevant column as a "named range", but I'd need to research that.

    You need (for now, see above) another calculation of the maximum of the sum of the "quarter-commission" column and the constant 30,000. Easy. If the sum (as calculated in the preceding paragraph) happens to be in cell (say) F23, then this calculation is given by =MIN(F23, 30000).

    So, if we've got to (say) the 8th row of your data, we'd have cells:
    A8 is 12,000 //commission (£12,000)
    B8 is A8*25% // quarter-commission: (£3,000)
    C8 is SUM(B$2:B8) //sum of quarter-commissions (assuming row 1 is header-row) (£29,000)
    D8 is MIN(C8, 30000) //sum of quarter-commissions, capped at 30K (still £29,000 so far)
    E8 is MAX(0,30000-SUM(B$2:B7)) // the amount of quarter-commission left under the 30K cap, but not less than zero: "Headroom" (£1,000)
    F8 is MIN(B8,E8) // the lesser of the Quarter-commission and the headroom left at this point under the £30K cap - presumably what's "Payable".

    Owzatt?

    Once you have it working, and you've thrown some odd figures at it to test for things you might have overlooked, you may well be able to combine it into a single formula - but that can make it hard to understand when you or someone else needs to come back to it to make changes! You may well want to arrange the columns in a different order. And any constant (25%, $30,000) should be a named range, possibly on a different tab called "constants". The 30K could be named "Cap" for example.

    Actually, I couldn't resist implementing it. Here is is: https://1drv.ms/x/s!AlXNpaNwJ1cugesWWSCJLCu-Hv03-Q?e=1ftl8E
    (I'll leave it there, on OneDrive, for at least a month.)

    --

    Phil, London

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Sat Sep 17 11:49:55 2022
    Hi,

    Am Thu, 15 Sep 2022 16:52:34 -0700 (PDT) schrieb This Beard Sells Homes:

    I have a spreadsheet that calculates 25% of a variable amount of money and dumps it into the corresponding column for that row. Each row can have a dollar amount inputted. But I want that 25% column to stop calculating once it reaches a specific amount
    entered into a cell somewhere else on the sheet.

    Example: Commission on selling a house is $12,000. 25% is $3,000. But when the SUM of that 25% column reaches, for example, $30,000, I want it to start showing zeros.

    To get more complicated, if the sum of the column stands at $29,000 (leaving only $1,000 left for my $30,000 cap) and the next 25% data entry results in an amount like $2,500, I want that particular cell to read "$1,000", not $2,500. Then I want each "
    25% cell" afterward, below in the column to read zero.

    have a look:
    https://1drv.ms/x/s!AqMiGBK2qniTgfMQbANGG29_Fre2KQ?e=z7DMus


    Regards
    Claus B.
    --
    Windows10
    Microsoft 365 for business

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Philip Herlihy@21:1/5 to All on Sun Sep 18 19:33:59 2022
    In article <tg4582$4spk$1@dont-email.me>, Claus Busch wrote...

    Hi,

    Am Thu, 15 Sep 2022 16:52:34 -0700 (PDT) schrieb This Beard Sells Homes:

    I have a spreadsheet that calculates 25% of a variable amount of money and dumps it into the corresponding column for that row. Each row can have a dollar amount inputted. But I want that 25% column to stop calculating once it reaches a specific
    amount entered into a cell somewhere else on the sheet.

    Example: Commission on selling a house is $12,000. 25% is $3,000. But when the SUM of that 25% column reaches, for example, $30,000, I want it to start showing zeros.

    To get more complicated, if the sum of the column stands at $29,000 (leaving only $1,000 left for my $30,000 cap) and the next 25% data entry results in an amount like $2,500, I want that particular cell to read "$1,000", not $2,500. Then I want each
    "25% cell" afterward, below in the column to read zero.

    have a look:
    https://1drv.ms/x/s!AqMiGBK2qniTgfMQbANGG29_Fre2KQ?e=z7DMus


    Regards
    Claus B.

    Claus's solution is both correct, and beautifully elegant. (My solution ended up wrong at the last stage.) If interested in further commentary, see my reply to my own earlier post in this thread.

    --

    Phil, London

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Philip Herlihy@21:1/5 to All on Sun Sep 18 19:32:05 2022
    In article <MPG.3d8f1615edfa60cc989a1a@news.eternal-september.org>, Philip Herlihy wrote...

    In article <001dfd74-f6ec-4afd-9687-4a13bc104838n@googlegroups.com>, This Beard
    Sells Homes wrote...

    I have a spreadsheet that calculates 25% of a variable amount of money and dumps it into the corresponding column for that row. Each row can have a dollar amount inputted. But I want that 25% column to stop calculating once it reaches a specific amount entered into a cell somewhere else on the sheet.

    Example: Commission on selling a house is $12,000. 25% is $3,000. But when the SUM of that 25% column reaches, for example, $30,000, I want it to start
    showing zeros.

    To get more complicated, if the sum of the column stands at $29,000 (leaving
    only $1,000 left for my $30,000 cap) and the next 25% data entry results in an amount like $2,500, I want that particular cell to read "$1,000", not $2,500. Then I want each "25% cell" afterward, below in the column to read zero.

    Is this possible?

    An intriguing puzzle. I don't have time to offer a worked (and tested) solution, but this might point you in the right direction.

    Firstly, it's often easier to work out your solution first in separate columns
    for each step before trying to combine them. It's ok to hide columns you don't
    want seen, of course - later.

    You need the sum of the "quarter-commission" column calculated for each row as
    the number of rows grows. You can use =SUM(A$1:A20) to calculate the sum of rows A1 to A20. If you copy (or Fill) to the next row, Excel will adjust that
    formula to =SUM(A$1:A21) - the dollar sign stops Excel incrementing that particular row number when copying down, but the other one does increment. I bet there are more elegant ways of doing this sum, possibly involving putting your data in a Table, configuring that Table to show Totals, and referring to the Total of the relevant column as a "named range", but I'd need to research that.

    You need (for now, see above) another calculation of the maximum of the sum of
    the "quarter-commission" column and the constant 30,000. Easy. If the sum (as
    calculated in the preceding paragraph) happens to be in cell (say) F23, then this calculation is given by =MIN(F23, 30000).

    So, if we've got to (say) the 8th row of your data, we'd have cells:
    A8 is 12,000 //commission (£12,000)
    B8 is A8*25% // quarter-commission: (£3,000)
    C8 is SUM(B$2:B8) //sum of quarter-commissions (assuming row 1 is header-row)
    (£29,000)
    D8 is MIN(C8, 30000) //sum of quarter-commissions, capped at 30K (still £29,000 so far)
    E8 is MAX(0,30000-SUM(B$2:B7)) // the amount of quarter-commission left under the 30K cap, but not less than zero: "Headroom" (£1,000)
    F8 is MIN(B8,E8) // the lesser of the Quarter-commission and the headroom left
    at this point under the £30K cap - presumably what's "Payable".

    Owzatt?

    Once you have it working, and you've thrown some odd figures at it to test for
    things you might have overlooked, you may well be able to combine it into a single formula - but that can make it hard to understand when you or someone else needs to come back to it to make changes! You may well want to arrange the columns in a different order. And any constant (25%, $30,000) should be a
    named range, possibly on a different tab called "constants". The 30K could be
    named "Cap" for example.

    Actually, I couldn't resist implementing it. Here is is: https://1drv.ms/x/s!AlXNpaNwJ1cugesWWSCJLCu-Hv03-Q?e=1ftl8E
    (I'll leave it there, on OneDrive, for at least a month.)

    I don't mind admitting I found this a fairly difficult problem - which is what made it fun to attempt it. I produced my 'solution' before I saw those from Steve D ("Stunn") and from Claus Busch.

    I was awed by Steve's solution, and at the same time daunted by it. The capacity to write code as complex as that is seriously impressive, but I figured I just didn't have what it would take (maybe time, or ability, but certainly not both) to understand how any of those solutions worked. That meant I couldn't verify (or maybe couldn't face attempting to verify) that they were correct, according to my understanding of the problem. In this context, which seemingly has real dollars involved, I think it's as important to give the OP confidence that a solution is correct as it is actually to be correct.

    So I turned to Claus Busch's solution. It took long enough for it to dawn on me how that worked (no genius here). But I was blown away by the apparent simplicity. I took his sample dollar values and applied my working to them; the results came out to be different. Who was right? Claus was - my solution seemed to be ok up to the last column/calculation (which I'd called "Payable") but that calculation of mine wasn't right.

    His solution is positively beautiful. It took me a while to figure out why 3000-SUM(of the first to the penultimate values) wouldn't go negative, but the logic is perfect. In that formula, you get EITHER the "quarter-commission" value (which Claus names "commission") OR you get whatever is still needed to reach 3,000 - whichever is the lesser. So if the total of the rows above is 3,000 minus X, then you get X, meaning that expression converges on 3,000. Every time!

    I bow before a master.

    I've updated my own 'solution' online to signal that it's wrong, and included a link to Claus's solution.

    Awesome...

    --

    Phil, London

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)