Formula that will show increase/decrease percentage without #Value or #DIV/0!

Question

I figure percentage of increase of sales both increase and decrease.  Sometimes there are not sales for a customer, while the year before they had sales.  Viceversa, a customer will have sales last year, but not this year.

I need a formula that 2010 sales minus 2009 sales divided by 2009 will show some type of number always either positive or negative.  If there is nothing entered in either column, don't show anything.

Is there a formula that would fit the bill for this? 

Any assistance would be most helpful.

Annette

Answer 1

Annette wrote:

I need a formula that 2010 sales minus 2009 sales divided by 2009 will show some type of number always either positive or negative.  If there is nothing entered in either column, don't show anything.

If 2009 sales is in A2 and 2010 sales is in B2, then:

=IF(N(A2)*N(B2)=0,"",B2/A2-1)

formatted as Percentage.

That always "doesn't show anything" if there is "nothing" (no sales), including zero, in either column, as you requested.

The N() function returns 0 even if the cell contains the null string (""), like the above formula might return.

But I would say that if there were sales in 2009 and no sales in 2010, there is a change of -100%.  If you agree, I would suggest:

=IF(N(A2)=0,"",N(B2)/A2-1)

Answer 2

.. > I need a formula that 2010 sales minus 2009 sales divided by 2009 will show some type of number always either positive or negative.  If there is nothing entered in either column, don't show anything.

Something like this would be your base formula in C2: =(B2-A2)/A2

You could use a COUNT to check that the precedent cells A2, B2 both contain real nums,

otherwise return a blank: ""  :

=IF(COUNT(A2:B2)<2,"",(B2-A2)/A2)

Answer 3

=IF(COUNTA(F2:G2)<>0,yourformula,"")

---

Don Guillett MVP Excel SalesAid Software *** Email address is removed for privacy ***