Calculate Percent Change while avoiding Divide/0 error.

[bycrofbl]

Alcon,

Thank you for taking the time to help.

I am having an issue setting up what I thought was going to be a simple function. I am attempting to create a table that will automatically calculate a change in percent from one week to the next. I am having issues because of zero’s. I am getting a divide by zero error which is throwing everything off.

My original code was

Please Login or Register  to view this content.

However after looking up the problem I came up with this

Please Login or Register  to view this content.

Need help.xlsx


I did CBQ (Can’t Be Quantified) because there cannot be a percent change when going from zero up. However this does not work for me when I have two zero’s in back to back weeks because that would equal no change. So I am seeking to have a function that would eliminate my divide by zero issue but allow for my back to back zero’s to display a no change text.

See attached sheet looking at cell Q16 shows the where I am running into the issue. Hopefully this was written clear enough but if you need further clarification I will do my best. Thanks again for any help I can get.

[shg]

You can't meaningfully compute periodover-period vchange when the prior period is not postive, and the financial journals don't. Seem for example, http://online.wsj.com/public/resourc...s/doe-help.htm

[humdingaling]

Please Login or Register  to view this content.

[shg]

You can't meaningfully compute period-over-period vchange when the prior period is zero or negative, and the financial journals don't. See, for example, http://online.wsj.com/public/resourc...s/doe-help.htm

[bycrofbl]

Hum, Thank you very much that did the trick.

[bycrofbl]

SHG, I understand that its not very meaningful, however its something that my bosses do what and something i need to be able to display. Thank you for the time.