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

Courtney Loukusa

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å

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

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å

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

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å

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

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å

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!