**Question description:**

*This user has given permission to use the problem statement for this blog.*

I contacted you yesterdaya bout how to use the =AVERAGE function, and for real numbers it works fine, but I tried to get the expert to explain to me how to use the =AVERAGE function when the cells have formulas in them that generate '0' value, so the AVERAGE function would ignore these cells in the averaging method, but got no answer. So, I need to know how use the =AVERAGE function so that when there are formulas in the selected cells to average, that EXCEL would ignore those cells like they have real numbers in them, which seems to work fine. Please advise...

Solved by C. J. in 30 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
04/04/2017 - 05:36

no

Excelchat Expert
04/04/2017 - 05:36

Hi, Welcome to Excel Chat

Excelchat Expert
04/04/2017 - 05:36

Let me guide you with the question

User
04/04/2017 - 05:37

I would asky you to no terminate this session until I have a satisfactory answer to my questions..

User
04/04/2017 - 05:37

not terminate this session

Excelchat Expert
04/04/2017 - 05:37

Okay

User
04/04/2017 - 05:38

Do you understand my question?

Excelchat Expert
04/04/2017 - 05:39

Yes

User
04/04/2017 - 05:40

Currently I am using the standard =AVERAGE(cell,cell,cell) formula

Excelchat Expert
04/04/2017 - 05:41

Use =AVERAGEIF (Cell:Cell,"<>0")

User
04/04/2017 - 05:41

This will solve this issue?

Excelchat Expert
04/04/2017 - 05:41

Yes

Excelchat Expert
04/04/2017 - 05:42

In Cell:Cell insert your range

User
04/04/2017 - 05:42

or individual celss..

User
04/04/2017 - 05:42

cells

User
04/04/2017 - 05:42

with commas between them

Excelchat Expert
04/04/2017 - 05:42

Yes

Excelchat Expert
04/04/2017 - 05:42

at the end write "<>0"

User
04/04/2017 - 05:42

ok, hold on please..

User
04/04/2017 - 05:45

=AVERAGEIF(DC10,DC18,DC26,DC34,DC42,DC50,DC58,DC66,"<>0")

Excelchat Expert
04/04/2017 - 05:45

Yes

User
04/04/2017 - 05:46

I am getting an error message saying this has too many functions

Excelchat Expert
04/04/2017 - 05:46

Wait a minute

User
04/04/2017 - 05:46

too many arguments

User
04/04/2017 - 05:50

Is this formula correct?

Excelchat Expert
04/04/2017 - 05:51

See for this you have to limit your functions

User
04/04/2017 - 05:51

how?

Excelchat Expert
04/04/2017 - 05:52

Like take two cells at a time and then apply

User
04/04/2017 - 05:52

Show me the layout

User
04/04/2017 - 05:52

IF I have 8 cells I want to average

Excelchat Expert
04/04/2017 - 05:52

Ok give me a moment

User
04/04/2017 - 05:52

ok

Excelchat Expert
04/04/2017 - 05:53

See in this case you have to take a range of cells not individual ones

Excelchat Expert
04/04/2017 - 05:54

if your have eight cells so take range as: A1:A8

Excelchat Expert
04/04/2017 - 05:54

then apply; formula will work

User
04/04/2017 - 05:54

But the cells I want to average are not next to each other

User
04/04/2017 - 05:54

There is no way around this

User
04/04/2017 - 05:55

??

Excelchat Expert
04/04/2017 - 05:55

Just a minute

Excelchat Expert
04/04/2017 - 05:57

See you can only take a range as AVERAGEIF formula accompanies for a range of cells

Excelchat Expert
04/04/2017 - 05:58

You can arrange your text aside for once and take average using AVERAGEIF formula

User
04/04/2017 - 05:58

EXCEL does not allow averaging of individual cells

Excelchat Expert
04/04/2017 - 05:58

and then sort out your data again

User
04/04/2017 - 05:58

??

Excelchat Expert
04/04/2017 - 05:59

It allows

User
04/04/2017 - 05:59

It seems this would not be an issue in EXCEL

Excelchat Expert
04/04/2017 - 05:59

you can take as this then: =AVERAGE(Cell,Cell,Cell,Cell)

Excelchat Expert
04/04/2017 - 05:59

Okay?

User
04/04/2017 - 05:59

So, there is no way to tell EXCEL the cells to average are not next to each other in the AC:AD format?

Excelchat Expert
04/04/2017 - 06:00

=AVERAGE(Cell,Cell,Cell,Cell)

Excelchat Expert
04/04/2017 - 06:00

You can apply this formula

User
04/04/2017 - 06:00

This is the same as I've been using

Excelchat Expert
04/04/2017 - 06:00

Average formula is concerned only with real numbers in the cells

User
04/04/2017 - 06:00

and it's not working with formulas in the cells.. again

User
04/04/2017 - 06:01

Ah, so a cell with a formula will not average correctly?

Excelchat Expert
04/04/2017 - 06:01

If you take a cell that has no value in average formula; it won't count it

User
04/04/2017 - 06:01

But if the formula in the cell generates a value>0 why woldn't EXCELL see this as a real number?

Excelchat Expert
04/04/2017 - 06:02

like if a cell A5 has =SUM(A1:A4) then Average formula only takes the result of that sum

Excelchat Expert
04/04/2017 - 06:02

Okay?

User
04/04/2017 - 06:02

Well, the issue was the restricted number of arguments wasn't it?

Excelchat Expert
04/04/2017 - 06:03

See restricted arguments show only when you take alot of cells

User
04/04/2017 - 06:03

Yes, I have formulas in the cells I want to average that generate number results, why won't EXCEL see these numbers as real numbers..?

Excelchat Expert
04/04/2017 - 06:03

you need to take a range instead of indiviudals

User
04/04/2017 - 06:04

ok, I understand..

Excelchat Expert
04/04/2017 - 06:04

Excel do consider them as real number

Excelchat Expert
04/04/2017 - 06:04

you need to take a range instead of indiviudals

Excelchat Expert
04/04/2017 - 06:04

Only argument error will display

Excelchat Expert
04/04/2017 - 06:04

otherwise*

User
04/04/2017 - 06:04

So, I may need to pull the cells out I want to average and place them next to each other, then the average function will work?

Excelchat Expert
04/04/2017 - 06:05

yes

User
04/04/2017 - 06:05

the cells with formulas..?