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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!