Average only cells with a value

November 18, 2016 at 09:48:22
Specs: Windows 7
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.

See More: Average only cells with a value

Report •

#1
November 18, 2016 at 10:33:26
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     50

No error, just numbers.

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


Report •

#2
November 18, 2016 at 11:51:38
Try using the AVERAGEIF() function, something like:

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

MIKE

http://www.skeptic.com/


Report •

#3
November 18, 2016 at 12:33:30
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 "error" ddillard is getting.

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.


Report •

Related Solutions

#4
November 18, 2016 at 13:00:20
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=$11000

a3=50
b3=$0
c3=$4000
d3=$7000

Summary
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


Report •

#5
November 18, 2016 at 13:29:44
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

http://www.skeptic.com/


Report •

#6
November 18, 2016 at 13:39:50
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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#7
November 18, 2016 at 13:42:35
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

http://www.skeptic.com/


Report •

#8
November 18, 2016 at 13:54:50
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.


Report •

Ask Question