1

I am trying to figure out the average difference in monthly sales versus previous years, and I have an IF function within an Average function. I want to skip zero values in the average function if the a years sales are zero (therefore wouldn't include an increase). Below is the start of the formula I've written. 

=AVERAGE((IF(C3724<>0,(C3724-C3723),  ,(C3725-C3724),(C3726-C3725),(C3727-C3726),(C3728-C3727),(C3729-C3728),(C3730-C3729))

Below is a copy of the data, to give an idea of what I am trying to accomplish.

        J   F   M   A   M   J   J   A   S   O   N   D   TOTAL

2007    0   0   0   0   0   0   0   0   0   0   0   0      0
2008    0   0   0   0   0   0   0   0   0   0   0   0      0
2009    0   0   0   0   0   0   0   0   0   0   10  0     10
2010    0   0   0   0   12  10  10  0   14  0   0   4     50
2011    6   0   0   0   0   0   0   10  0   10  0   0     26
2012    15  0   13  0   12  0   14  0   0   0   0   10    64
2013    0   0   0   0   0   10  0   0   0   0   12  0     22

As you can see with this model, there are a lot of zero values, and I want to skip those, so I can get a more attainable average INCREASE in sales for this model. How would I skip these in my Average function?

Improve this question
1
  • 2
    Your question is not clear. Please edit and include the expected result.
    – teylyn
    Sep 11, 2014 at 20:44

1 Answer 1

Reset to default
1

For the data that you are trying to average, you could manually calculate it using SUM and COUNTIF.

ie

=SUM([range here])/COUNTIF([range here],"<>0")
Improve this answer
5
  • I'm not looking for the average sales, but actually the average year over year increase. I don't want to include the difference where there were no sales in a current year, hence why I used the <>0 in my IF statement. There are about 700 models, so I am hoping to not have to write a new formula for each model that skips zeros.
    – JimmyGlenn
    Sep 11, 2014 at 20:29
  • @JimmyGlenn, I'm not sure I follow. You want to find the average increase from one year to the next? Why wouldn't you include 0s in that?
    – Holloway
    Sep 11, 2014 at 20:32
  • I guess I am confusing myself, or just not looking at it properly. For this model, there weren't sales until 2009, so I want to not look at 2008 and 2007. I am hoping to create one formula to use on all models, not write a custom one for each. Most of the models I am looking at were usually introduced in the middle of the year, and not introduced until the past few years.
    – JimmyGlenn
    Sep 12, 2014 at 13:43
  • What do the numbers in the table above represent? Sales or increase in sales?
    – Holloway
    Sep 12, 2014 at 14:06
  • They represent sales.
    – JimmyGlenn
    Sep 12, 2014 at 14:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .