How to ignore, or return a 0 with a blank cell in an IF formula

Courtney Loukusa · 03/17/20

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!!

Andrée Starå · 03/18/20

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!

Courtney Loukusa · 03/18/20

I'm still new to this, and did not know about @row until you just mentioned it. I think this will be helpful! Thanks!!

Andrée Starå · 03/18/20

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!

Courtney Loukusa · 03/17/20

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)

Andrée Starå · 03/18/20