help with AVERAGE problem

Dwise85

New Member
Joined
Nov 16, 2018
Messages
17
Hi,

I am trying to find the average in a column. If there is not data in that column I went to reference the data in another cell and use that as "the average" for that column. Any help would be greatly appreciated!

for example:
column 1 cells A1:A8 are averaged in A9, column 2 cells B1:B8 are averegd in B9, column C cells C1:C8 are averaged in C9 but all cells are blank and returing a #div/0!. if this is the case I want to use the data that is in E6 and and use that as the average if this is the case.



 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146

ADVERTISEMENT

Hi,

You can just wrap your formula with IFERROR:

=IFERROR(AVERAGEIF(D2:D8,"<>0"),H6)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,240
Welcome to the forum.

How about:

Code:
=IFERROR(AVERAGE(D2:D8),$H$6)

AVERAGEIF may not be required, since AVERAGE ignores empty cells.
 

Dwise85

New Member
Joined
Nov 16, 2018
Messages
17

ADVERTISEMENT

Awsome guys thanks heaps!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,278
Office Version
  1. 2013
Platform
  1. Windows
So simply

Code:
=AVERAGE(B2:B8)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,450
Messages
5,528,819
Members
409,839
Latest member
akashsadhu
Top