Average of last 5 values but to exclude any blank cells.

[tb2000]

Hi
I wonder if anyone can assist.
I have a scenario where I have a rolling list of sales figures which get added to each week that passes.

I need a formula that will calculate the last 5 weeks of sales and generate an average - which I think I have an idea how to do.
The sticking point is that so as not to skew the averages, when there has been an exceptionally busy or quiet day for a reason we know about I exclude the sales from that week.
This then interferes with the averages as it either takes it as a zero and lowers it or seems to stop formulas from working.

So to summarise:

Average of last 5 weeks sales
Excluding any blanks
Dynamic enough to always pick up the last 5 values in the list (i.e. the last 5 weeks)

Any assistance greatly appreciated.
Tim

[nathansav]

=sumif(A:A,">=" & today()-35)/countif(A:A,">=" & today()-35)

i havent tested as not in excel at the minute. SOmething like this, this doesnt exclude 0's, but you can use the IFS extensions of SUM and COUNT to exclude the 0's also.

[Tony Valko]

Where is the data located? In a row? A column?

Will there always be at least 5 numbers to average? If not, what should happen?

[tb2000]

The data will be in columns with the last 5 entries (weeks) always needing to be averaged.
The scenario is that the column will increase weekly as the next weeks sales for that particular day are added but the figure required by the formula is the average of the last 5 entries in the column (but not including any blanks that may be present within that last 5 entries)

Does that make sense?

[Tony Valko]

Try this...

Data Range

	A	B	C
1	Sales	------	AVG last 5
2	98		63.4
3	86
4
5	53
6	80
7	58
8	89
9
10	59
11	28
12
13
14	81
15	60

This array formula** entered in C2:

=IFERROR(AVERAGE(IF(ROW(A2:A20)>=LARGE(IF(A2:A20<>"",ROW(A2:A20)),MIN(COUNT(A2:A20),5)),IF(A2:A20<>"",A2:A20))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

It will average the last (bottom-most) 5 entries. If there aren't 5 entries it will average what's available.

[tb2000]

Tony that's amazing and works perfectly - thank you so much.

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[RTJones]

What would this array formula look like if I wanted to average the FIRST 5 VALUES instead of the LAST FIVE ???

[bebo021999]

What would this array formula look like if I wanted to average the FIRST 5 VALUES instead of the LAST FIVE ???

Not yet tested, but try to replace ">=LARGE" with "<=SMALL"

[FDibbins]

RTJones welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[RTJones]

Ford: SORRY for not knowing better. As you can see, I'm very new to this. Also, I couldn't figure out how to start my own thread (newbie!!!!).

Note....I replaced ">=LARGE" with "<=SMALL" as suggested and it worked perfectly!!!! THANK YOU

[TPL57]

Tony, Noticed you were a Pens fan, ditto here. I just wanted to thank you for you post. I have been working on this since 11am today and came across your post. This is exactly what I needed. Thank you so much! Go Pens!!!!!

[FDibbins]

TPL57, thanks for the feedback, always appreciated

[Beefyman61]

Where is the data located? In a row? A column?

Will there always be at least 5 numbers to average? If not, what should happen?

I wanted to know what can be done if there are not always going to be 5 numbers to average? There will always be at least 1 number but could be anywhere between 1 and 5.
Would like excel to return the average even if its just 1 number
I switched it to Columns for use on a table Capture.PNG