DIV/0 Error

[Eva Shanley]

I have a spreadsheet that compares last year's numbers
with this year's numbers. I'm looking for the % of change
between the 2 years, however, if the divisor is 0 I
naturally get a #DIV/0 error. For example, if F10(last
year) = 0 and J10(this year) = 1, dividing J10/F10 results
in the error. Is there any way to come up with the
percent instead of the error? Thanks!

 

[JulieD]

Hi Eva

how about something like:
=if(F10=0,J10,J10/F10)

Cheers
JulieD

 

[JE McGimpsey]

What "percent" would you expect to get?

The % change from 0 to 1 is undefined if referenced to 0 (i.e., 100% of
0 is 0, 200% of 0 is 0, ... - there's no number that can be mulitplied
by 0 to get 1).


I'd suggest something like:

=IF(F10=0,"NM",F10/J10 - 1)

where NM stands for Not Meaningful.

 

[Niek Otten]

And what, in your opinion, should the percentage be?

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Eva Shanley]

JE,
My sentiments exactly; many times users here have no idea
what they want, they only know they want it.