Rid of "#DIV/0!" in average formula

rthakur

Board Regular
Joined
Feb 22, 2005
Messages
121
{=AVERAGE(IF(I6:I36<>0,I6:I36,""))}

Above formula gives out "#DIV/0!" value if there is not data (in other words, zero value). Is there a way to modify above formula to get a 0 (zero) or a blank if there is no data (numeric only)?

Thank you!


Regards,
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
rthakur said:
{=AVERAGE(IF(I6:I36<>0,I6:I36,""))}

Above formula gives out "#DIV/0!" value if there is not data (in other words, zero value). Is there a way to modify above formula to get a 0 (zero) or a blank if there is no data (numeric only)?

Thank you!


Regards,

Try,

=SUM(I6:I36)/MAX(1,COUNTIF(I6:I36,">0"))
 

rthakur

Board Regular
Joined
Feb 22, 2005
Messages
121
Thank you for your prompt response Brian.

It is working!!!


Regards,
 

Watch MrExcel Video

Forum statistics

Threads
1,118,811
Messages
5,574,454
Members
412,595
Latest member
slim313
Top