Count/IFSum formula Question
Hello;
for a financial sheet I am trying to find a formula combination.
the scenario goes as follows: if person A ticks a box in column X, a certain amount of due paiment (column Y) will be reduced by 30k (up until 0). The total sum (column Z) that person A will need to pay is the sum of all relevant columns, including the due paiment in column Y (if it's 0, it's 0 euros).
Additionally, the amount that shows in column Y (that will be reduced by 30k if a box is ticked) is a certain percentage of the sum that is shown in column W.
How can I make it so that I only need to fill in the amount in column W, and potentially tick the box in column X, to resolve this calculation? I have tried a few solutions but it'll only make me do as much work as I'd calculate it myself.
Thank you for your help!
Answers

Are you able to provide some screenshots (sample data if needed) for context?

For the first part:
=IF(X@row = 1, MAX(Y@row  30000, 0), Y@row)
If the checkbox in X is ticked, then 30000 will be subtracted from Y, or 0 if this would be negative.
It's a bit tricky to work out what the next formula regarding column Z would be as the columns involved haven't been specified, but you should be able to do this easily with a SUM formula.
For the percentages, if you're wanting to check that your percentage is acceptable pre/post adjustment then you can have a couple of columns to show this. For example, if you want what percentage Y is of W pre/post modification:
Pre modification:
=Y@row / W@row
Post modification:
=MAX((Y@row  30000) / W@row, 0)
This assumes you want 0 as a minimum, otherwise you can ditch the MAX portion to include negatives:
=(Y@row  30000) / W@row
You can then use a filter in needs be to display acceptable/unacceptable results and/or combine with conditional formatting.
Hope this makes sense, but if I've misunderstood something or you've any problems/questions, then just post! 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!