# Excel - AVERAGE Function Problem - Expert Solution

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
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
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..?
Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.