How to ignore, or return a 0 with a blank cell in an IF formula
Hello!
I'm trying to calculate my savings year-to-date. I thought i had the formula, but soon realized that having blank dates (maybe we don't have a start selling date, or the project is still in development), it'll still return a value when it should really be $0. I have no doubt there is a more elegant way to do my original calculation, but this is what i have:
My thought process is IF the start selling date is in the past, then take the estimated annual savings, divide by 12 and then multiply it by the # months in 2020 it's been selling for. In this case we were running the report for Feb so that is where the 2 comes from.
How can i get update this formula it to return a $0 or stay blank if the start selling date is blank? Or is there a better way to go about this formula in the first place!!
Thank you so much community!!
Best Answers
-
Hi Courtney,
Glad you got it working!
Another possible improvement would be to use @row so you don''t need to think about row numbers.
Something like.
=IF(AND(NOT(ISBLANK([Start Selling Date]@row)), [Start Selling Date]@row < TODAY()), ([Estimated Annual Savings]@row / 12 * (MONTH(TODAY()) - [month of 2020 when sale became active-1]@row)), 0)
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I'm still new to this, and did not know about @row until you just mentioned it. I think this will be helpful! Thanks!!
-
Excellent!
You're more than welcome!
✅Remember! Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
I figured it out!!
=IF(AND(NOT(ISBLANK([Start Selling Date]11)), [Start Selling Date]11 < TODAY()), ([Estimated Annual Savings]11 / 12 * (MONTH(TODAY()) - [month of 2020 when sale became active-1]11)), 0)
-
Hi Courtney,
Glad you got it working!
Another possible improvement would be to use @row so you don''t need to think about row numbers.
Something like.
=IF(AND(NOT(ISBLANK([Start Selling Date]@row)), [Start Selling Date]@row < TODAY()), ([Estimated Annual Savings]@row / 12 * (MONTH(TODAY()) - [month of 2020 when sale became active-1]@row)), 0)
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I'm still new to this, and did not know about @row until you just mentioned it. I think this will be helpful! Thanks!!
-
Excellent!
You're more than welcome!
✅Remember! Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!