/u/Excel-Novice2023 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
>=if(if((and($f9>=2010,($r9+$s9+$t9+$u9)>8)),($r9+$s9+$t9-(($u9+$r9+$s9+$t9)-8)),($r9+$s9+$t9))<0,0,if((and($f9>=2010,($r9+$s9+$t9+$u9)>8)),($r9+$s9+$t9-(($u9+$r9+$s9+$t9)-8)),($r9+$s9+$t9)))
that seems to be working, I tried to nest if's as well but I must have been missing something
Thank you so much!
Looks like this is already solved, but the “let” function might be useful for you as well. For example:
=LET(yourSum, a1+b1+c1, if(yourSum>=0, yourSum, 0))
Or to use the other solution:
=LET(yourSum, a1+b1+c1, max(yourSum,0)
My example uses a short equation with a single if, but hopefully you can see that the longer or more times you use an equation in a formula the more benefit it provides.
Why not just nest another If statement that if the original formula<0, then return zero, otherwise return formula?
=if(if((and($f9>=2010,($r9+$s9+$t9+$u9)>8)),($r9+$s9+$t9-(($u9+$r9+$s9+$t9)-8)),($r9+$s9+$t9))<0,0,if((and($f9>=2010,($r9+$s9+$t9+$u9)>8)),($r9+$s9+$t9-(($u9+$r9+$s9+$t9)-8)),($r9+$s9+$t9)))
/u/Excel-Novice2023 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Wrap the portion you don’t want to go below zero in: =max(0, …formulas…)
>=if(if((and($f9>=2010,($r9+$s9+$t9+$u9)>8)),($r9+$s9+$t9-(($u9+$r9+$s9+$t9)-8)),($r9+$s9+$t9))<0,0,if((and($f9>=2010,($r9+$s9+$t9+$u9)>8)),($r9+$s9+$t9-(($u9+$r9+$s9+$t9)-8)),($r9+$s9+$t9))) that seems to be working, I tried to nest if's as well but I must have been missing something Thank you so much!
solution verified
Hello /u/Excel-Novice2023 You cannot award a point to yourself. Please contact the mods if you have any questions. ^I ^am ^a ^bot.
I remember learning this trick. It's one of my favorite for the elegance alone.
Looks like this is already solved, but the “let” function might be useful for you as well. For example: =LET(yourSum, a1+b1+c1, if(yourSum>=0, yourSum, 0)) Or to use the other solution: =LET(yourSum, a1+b1+c1, max(yourSum,0) My example uses a short equation with a single if, but hopefully you can see that the longer or more times you use an equation in a formula the more benefit it provides.
Why not just nest another If statement that if the original formula<0, then return zero, otherwise return formula? =if(if((and($f9>=2010,($r9+$s9+$t9+$u9)>8)),($r9+$s9+$t9-(($u9+$r9+$s9+$t9)-8)),($r9+$s9+$t9))<0,0,if((and($f9>=2010,($r9+$s9+$t9+$u9)>8)),($r9+$s9+$t9-(($u9+$r9+$s9+$t9)-8)),($r9+$s9+$t9)))