How do I get an average() for only the cells that have a value. If I have a column for each day for sales and there is a day that has 0 then I get an error in my average() formula.

What error are you getting? The AVERAGE function will ignore blank cells and will include a 0 in the average, but neither case should cause an error.

With this data, =AVERAGE(A1:A5) will return 50, (200/4)

A 1 50 2 50 3 50 4 5 50

With this data, =AVERAGE(A1:A5) will return 40, (200/5)A 1 50 2 50 3 50 4 0 5 50No error, just numbers.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Try using the AVERAGEIF() function, something like: =AVERAGEIF(A1:A5,">0")

MIKE

re: =AVERAGEIF(A1:A5,">0") What if there are enough returns on any given day such that they result in a negative sales figure?

This would be more inclusive, but that is not my main issue:

=AVERAGEIF(A1:A5,"<>0")

My main issue is that I still want to know what

ddillard is getting."error"Is it an actual Excel error or does he just not like the answer that the AVERAGE function returns? The latter is not an error, per se, it's just not the answer he is looking for.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Here is some of the data I am working with. I have each day as a column by week with each location as a row. I then average it over two months from two pivot tables. Column a is my location, b, c, and d are Monday of three different weeks (wk1, wk2, wk3). a2=30

b2=$10000

c2=$8000

d2=$11000a3=50

b3=$0

c3=$4000

d3=$7000Summary

y2= =averageif(b2:d2,">0") which = $9666.66

y3= =averageif(b3:d3,">0") which = #DIV/0!This data is kind of jumbled up here so I hope you can understand. I have a pivot table in column a - d and rows 1-4. four columns and rows are a pivot table. I have a summary in column y - ab and rows 1-4 in each cell of the summary I have the =averageif formula which work fine, so y2 = $9666.66 unless I have a day in my table with a $0 then I get the #DIV/0!. What I want to get is to have it skip the $0 or blank and give me the $5500 ($4000+$7000)/2 or the sum of cell b3:d3/2.

message edited by ddillard

DerbyDad

I get an error in my average() formula.I read that as meaning his result was not as expected,

not that he was receiving an Excel error message.MIKE

Where are your numbers coming from?

~~This is from Microsoft documentation:~~

~~If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value.~~

If cells in average_range cannot be translated into numbers, AVERAGEIFS returns the #DIV0! error value.

If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.Sorry, wrong function.

I would still guess that one or more of your numbers is being interpreted as TEXT not numbers.

MIKE

message edited by mmcconaghy

Also, the error may be somewhere else: If cells in any argument range include Excel error values,

then the first error value encountered is returned.MIKE

Thanks, I had missed a formula in my summary table. The =averageif formula does exactly what I was looking for. Thanks DerbyDad03 that is something to keep in mind if I have a negative sales.

Ask Your Question

Weekly Poll