# Excel - AVERAGE Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

how to calculate average of 4 numbers which is taken from other cells
Solved by A. L. in 19 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 23/09/2018 - 08:19
hi
User 23/09/2018 - 08:19
sir
Excelchat Expert 23/09/2018 - 08:19
Hello. Welcome to Excelchat, I see that your question is about calculating the average for 4 numbers.
Excelchat Expert 23/09/2018 - 08:20
User 23/09/2018 - 08:20
i need a help
Excelchat Expert 23/09/2018 - 08:20
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
Excelchat Expert 23/09/2018 - 08:20
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert 23/09/2018 - 08:20
Please tell me more on the problem you are facing.
Excelchat Expert 23/09/2018 - 08:21
Please respond so that I can assist you.
User 23/09/2018 - 08:22
i need to calculate average of 4 numbers... all those four numbers have taken from another cell... but now while calculating average , it shows error.. it is taking all those four numbers as text... and i need to include one more condition that, if that four numbers include zero, no need to include zero for calculating the average
User 23/09/2018 - 08:23
In A1 cell value 3, B1 value 0, C1 Value 0 and D value 0
User 23/09/2018 - 08:24
in A 6, I HAVE GIVEN A6 =A1,
User 23/09/2018 - 08:24
B1=B6, C6=C1 and D6 =D1
Excelchat Expert 23/09/2018 - 08:24
I’m going to build a formula for you on F6 for the average from A6 to D6. This should take me few minutes to solve. If prompted for extension of session, please do so.
User 23/09/2018 - 08:25
you can take
Excelchat Expert 23/09/2018 - 08:25
=Averageif(A6:D6,"<>0")
Excelchat Expert 23/09/2018 - 08:25
This is the formula I have used.
User 23/09/2018 - 08:26
precisely two things.... 1. text to number and 2. if zero comes, no need to include for calculating average
Excelchat Expert 23/09/2018 - 08:26
This will neglect all the zero
Excelchat Expert 23/09/2018 - 08:26
regarding text to numbers, is it possible for you to share the file?
Excelchat Expert 23/09/2018 - 08:26
these are numbers which you have build for me in the sample data.
User 23/09/2018 - 08:26
ok sir
Excelchat Expert 23/09/2018 - 08:27
Are you still there?
User 23/09/2018 - 08:27
yea
User 23/09/2018 - 08:27
User 23/09/2018 - 08:28
Excelchat Expert 23/09/2018 - 08:28
Ok
Excelchat Expert 23/09/2018 - 08:28
So let's suppose the number is stored as text in A1
Excelchat Expert 23/09/2018 - 08:29
for the sample data on right.
User 23/09/2018 - 08:29
I cant see anything n document preview
User 23/09/2018 - 08:29
if its visible only i can explain to you
Excelchat Expert 23/09/2018 - 08:29
Excelchat Expert 23/09/2018 - 08:29
I can see the excel.
Excelchat Expert 23/09/2018 - 08:30
Let me explain you on how we can use the numbers stored as text
User 23/09/2018 - 08:30
in that excel I 88 need average of E 88, F88, G88 and H88
Excelchat Expert 23/09/2018 - 08:30
OK
User 23/09/2018 - 08:31
and it should exclude zero if zero comes
User 23/09/2018 - 08:31
i am getting error as it shows text
Excelchat Expert 23/09/2018 - 08:31
OK
Excelchat Expert 23/09/2018 - 08:31
Give me a minute to check
User 23/09/2018 - 08:31
ok
Excelchat Expert 23/09/2018 - 08:33
Excelchat Expert 23/09/2018 - 08:34
Instead of =E80, I used =Value(E80)
User 23/09/2018 - 08:34
OK sir
Excelchat Expert 23/09/2018 - 08:34
User 23/09/2018 - 08:35
1 minute
Excelchat Expert 23/09/2018 - 08:35
and for average I used =AVERAGEIF(E88:H88,"<>0") which means it will average the range from E88 to H88 only if it is ><0. So it will ignore all 0.
Excelchat Expert 23/09/2018 - 08:36
and you will see I 93 is still showing an error because there is no value to average as all values are 0.
Excelchat Expert 23/09/2018 - 08:37
Excelchat Expert 23/09/2018 - 08:37
We can amend this formula further with an If statement to avoid error
Excelchat Expert 23/09/2018 - 08:38
Excelchat Expert 23/09/2018 - 08:38
=IF(SUM(E88:H88)=0,0,AVERAGEIF(E88:H88,"<>0"))
User 23/09/2018 - 08:38
ok sir
User 23/09/2018 - 08:38
thank you
User 23/09/2018 - 08:38
problem solved
Excelchat Expert 23/09/2018 - 08:38
Great!
User 23/09/2018 - 08:38
thank you so much
Excelchat Expert 23/09/2018 - 08:38
Thanks for coming to Excelchat.
Excelchat Expert 23/09/2018 - 08:38
Also, if you are satisfied with my solution, please go ahead and end the session using the button on the top right hand side and requesting you to provide good feedback and review so that I can continue my services to others.
User 23/09/2018 - 08:38
Excelchat Expert 23/09/2018 - 08:38
Thank you and have a great day ahead.
User 23/09/2018 - 08:38
Thank you

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

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.