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 amountentered 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.25% cell" afterward, below in the column to read zero.
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 "
Is this possible?
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?
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 amountentered 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.25% cell" afterward, below in the column to read zero.
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 "
Hi,amount entered into a cell somewhere else on the sheet.
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
"25% cell" afterward, below in the column to read zero.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
have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgfMQbANGG29_Fre2KQ?e=z7DMus
Regards
Claus B.
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.)
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 298 |
Nodes: | 16 (2 / 14) |
Uptime: | 05:57:21 |
Calls: | 6,677 |
Files: | 12,219 |
Messages: | 5,340,252 |