"Formula"

[Marwan69]

Hi Folks,

I'm trying to take the average of a series. However, there're a few empty cells with no values which returns "#DIV/0!".

How can fix that with excel formulas?

Many thanks,
Marwan

 

[DataBlake]

what is the range? can you post an example of your averages? what does you current formula look like? what version of excel are you running?

if i use =average(B2:E2) where c2 is blank it still calculates an average
even if there is a 0 it should work
(excel 2016)

the only time i get your error is if all values are blank

 

[GWteB]

#DIV/0! is the result of a division by zero in that particular cell.
As BlakeSkate stated, empty cells in the range with the Average formula don't raise the DIV/0 error.

 

[AhoyNC]

See if this helps.
If you have Excel ver 2010 or later try:

Book1
	A	B	C
1	Data		Average
2	2		2.625
3	1.5
4	3
5	#DIV/0!
6	4
Sheet2

Cell Formulas
Range	Formula
C2	C2	=AGGREGATE(1,6,A2:A6)
A5	A5	=45/0

 

[Marwan69]

what is the range? can you post an example of your averages? what does you current formula look like? what version of excel are you running?

if i use =average(B2:E2) where c2 is blank it still calculates an average
even if there is a 0 it should work
(excel 2016)

the only time i get your error is if all values are blank

what is the range? can you post an example of your averages? what does you current formula look like? what version of excel are you running?

if i use =average(B2:E2) where c2 is blank it still calculates an average
even if there is a 0 it should work
(excel 2016)

the only time i get your error is if all values are blank

Hi BlakeSkate

Am using Version 2013 and the idea is I have a range from A1 to E1 and so on for the other ranges. For the entire range sometimes there's only one number which yields this value error when using Average.

 

[Marwan69]

See if this helps.
If you have Excel ver 2010 or later try:

Book1
	A	B	C
1	Data		Average
2	2		2.625
3	1.5
4	3
5	#DIV/0!
6	4
Sheet2

Cell Formulas
Range	Formula
C2	C2	=AGGREGATE(1,6,A2:A6)
A5	A5	=45/0

Thanks so much AhoyNC!

I have also incorporated the IFERROR when vale returns "#DIV/0!"

=IFERROR(AGGREGATE(1,6,D4,F4,H4,J4,L4,N4,P4),"").

Appreciated and happy new year to all of you

 

[AhoyNC]

You're welcome. Thanks for the feedback. You may want to see what cell or cells are causing the error and try to correct the error before passing the data to the AVERAGE or AGGREGATE function.

 

[Marwan69]

You're welcome. Thanks for the feedback. You may want to see what cell or cells are causing the error and try to correct the error before passing the data to the AVERAGE or AGGREGATE function.

??