Formula Assistance đź™Ź
I have the following data set:
Wellness Annual Total  Wellness Expense  Wellness Remaining  Education Annual Total  Education Expense  Education Remaining 

$1,000  $500  $500  $5,000  $4,500  $500 
I am trying to write a formula for the wellness remaining column, but I have a rule I must follow that is giving me a headache for the formula. Individuals are allowed $1,000 per year for wellness and $5,000 per year for education. However, wellness deducts from educationâ€¦.so if someone spends $4,500 on education then they only have $500 left for either wellness or education but not both.
Is there a formula I can write that will accurately reflect this? please let me know if more information is needed.
Best Answers

Hello @JackofAll
Something like this perhaps?
=IF([Education Expense]@row <= 5000,
MAX(0, 1000  [Wellness Expense]@row  MAX(0, [Education Expense]@row  4500)),
0) 
Explanation:
[Education Expense]@row <= 5000: Checks if education expenses are within the allowed limit.
MAX(0, [Education Expense]@row  4500): Calculates how much of the education budget is affecting the wellness budget. If education spending exceeds $4,500, it deducts the excess from the wellness budget.
MAX(0, 1000  [Wellness Expense]@row  ...): Ensures the remaining wellness budget doesn't go below zero.
Answers

Hello @JackofAll
Something like this perhaps?
=IF([Education Expense]@row <= 5000,
MAX(0, 1000  [Wellness Expense]@row  MAX(0, [Education Expense]@row  4500)),
0) 
Hey @ChrisPalmer this looks great, thank you so much for providing the formula. I think this will work! For the $4500 bolded below, what does it represent?
=IF([Education Expense]@row <= 5000,MAX(0, 1000  [Wellness Expense]@row  MAX(0, [Education Expense]@row  4500)),0)
I ask because the education expense can sometimes consume the whole $5,000 amount and it is not always $4,500. Does that make sense?

Explanation:
[Education Expense]@row <= 5000: Checks if education expenses are within the allowed limit.
MAX(0, [Education Expense]@row  4500): Calculates how much of the education budget is affecting the wellness budget. If education spending exceeds $4,500, it deducts the excess from the wellness budget.
MAX(0, 1000  [Wellness Expense]@row  ...): Ensures the remaining wellness budget doesn't go below zero. 
I get it, thank you so much @Chris Palmer

Absolutely! Happy this worked for you.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!