Formula Assistance 🙏

JackofAll

I have the following data set:

Wellness Annual Total	Wellness Expense	Wellness Remaining	Education Annual Total	Education Expense	Education Remaining
Wellness Annual Total	$1,000	Wellness Expense	$500	Wellness Remaining	$500	Education Annual Total	$5,000	Education Expense	$4,500	Education Remaining	$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.

=Chris Palmer

Hello @JackofAll

Something like this perhaps?

=IF([Education Expense]@row <= 5000,
MAX(0, 1000 - [Wellness Expense]@row - MAX(0, [Education Expense]@row - 4500)),
0)

=Chris Palmer

JackofAll

Sep 27, 2024

https://community.smartsheet.com/discussion/comment/455619#Comment_455619

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.

=Chris Palmer

Hello @JackofAll

Something like this perhaps?

=IF([Education Expense]@row <= 5000,
MAX(0, 1000 - [Wellness Expense]@row - MAX(0, [Education Expense]@row - 4500)),
0)

JackofAll

https://community.smartsheet.com/discussion/comment/455619#Comment_455619

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?

=Chris Palmer

https://community.smartsheet.com/discussion/comment/455731#Comment_455731

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.

JackofAll

https://community.smartsheet.com/discussion/comment/455749#Comment_455749

I get it, thank you so much @Chris Palmer

=Chris Palmer

https://community.smartsheet.com/discussion/comment/455779#Comment_455779

Absolutely! Happy this worked for you.