Combine an "IF" and "OR" statement

P

Picman

I need to calculate the % difference between 2 years of sales, however one
year or the other may be a zero value (ie new customer this year or old one
last year that has no sales this year). Zero sales last year with sales this
year = 100% or sales last year with zero sales this year = -100%, or if there
are sales in both years calculate the % difference.
 
R

Roger Govier

Hi

Assuming your data is in column A and B, In C1
=IF(COUNT(A1:B1)<2,"n/a",B1/A1*100)

This will put n/a in the cell if either (or both) year(s) has no value
Change the "n/a" to "" if you want a blank cell to appear.
 
L

Lars-Åke Aspelin

I need to calculate the % difference between 2 years of sales, however one
year or the other may be a zero value (ie new customer this year or old one
last year that has no sales this year). Zero sales last year with sales this
year = 100% or sales last year with zero sales this year = -100%, or if there
are sales in both years calculate the % difference.

Last year in column A, this year in column B.

Try this formula in e.g. column C:

=IF(A1=0,100%,IF(B1=0,-100%,(B1-A1)/A1*100%))

Hope this helps / Lars-Åke
 
S

Shane Devenshire

Hi,

If you will never have both years without sales, then try this

=IF(A1,(B1-A1)/A1,1)

Where A1 is the old year sale's and B1 the new year's sales
 
P

Picman

That worked great, thank you very much.

Lars-Ã…ke Aspelin said:
Last year in column A, this year in column B.

Try this formula in e.g. column C:

=IF(A1=0,100%,IF(B1=0,-100%,(B1-A1)/A1*100%))

Hope this helps / Lars-Ã…ke
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top