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
I can help you with that problem.
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
[Uploaded an Excel file]
User
23/09/2018 - 08:27
how to upload my file
User
23/09/2018 - 08:28
i have uploaded
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
Yes, please
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
Please check this
[Uploaded an Excel file]
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
Please check the file and confirm does this solve your problem?
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
[Uploaded an Excel file]
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
Please check this file
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
ok sir/madam
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.
