Count/IFSum formula Question

Eva Group Deneve

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!

Paul Newcome

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

Nick Korna

Hi @Eva Group Deneve,

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! 🙂