Calculating percentage change when one or both factors are zero

[jmlyon954]

I am working on a budget template and having issues with the #DIV/0! error. I found the below formula in a thread from 2019 that works great when one factor is zero. Unfortunately, It's a 5-year capital plan comparison, and some years have zeros for the new and old values giving me the #DIV/0! error. Could anyone please help me use the below formula and add that if both factors are zero, it will result in a 0.00% change?

I would greatly appreciate any help. Thank you.

=IF(AND(OLD VALUE=0,New Value>0),1,IF(MIN(OLD VALUE,NEW VALUE)<=0,(NEW VALUE-OLD VALUE)/ABS(OLD VALUE),(NEW VALUE/OLD VALUE)-1))

 

[Joe4]

Welcome to the Board!

One simple way is to simply wrap your formula in an IFERROR formula (in which you tell Excel what to return in the event of an error), i.e.
=IFERROR(your formula,0)

 

[jmlyon954]

Welcome to the Board!

One simple way is to simply wrap your formula in an IFERROR formula (in which you tell Excel what to return in the event of an error), i.e.
=IFERROR(your formula,0)

You are the best! It worked. Thank you so much.

 

[Joe4]

You are welcome.
Glad I was able to help!