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..?
