Question description:
This user has given permission to use the problem statement for this
blog.
Hi. workbook about 10 sheets with linked entries between sheets. specific column question.... all cells in column have either a zero or a number... I can get the sum of and the average of that column but I can't get a true average because it wants to include the cells with zeros.... Anyone????
Solved by Z. J. in 12 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
10/09/2018 - 08:34
Welcome to ExcelChat!
Excelchat Expert
10/09/2018 - 08:34
I see that your problem is about averaging the results and zero values
Excelchat Expert
10/09/2018 - 08:34
I will help you to solve the problem and explain how it works later
Excelchat Expert
10/09/2018 - 08:35
As I understood your problem, you want to avoid including zeros in the average calculations, is that right?
User
10/09/2018 - 08:36
Yes
User
10/09/2018 - 08:36
I just did a ssmall sample
Excelchat Expert
10/09/2018 - 08:36
Great, I will put the solution in A14 in a while
Excelchat Expert
10/09/2018 - 08:37
The solution formula is
Excelchat Expert
10/09/2018 - 08:37
=averageif(A1:A9,"<>0")
Excelchat Expert
10/09/2018 - 08:38
So there is a function in Excel, which allows to include only values which meet certain conditions
Excelchat Expert
10/09/2018 - 08:38
In our case the condition is "not zero", which we described in the formula with the sign of inequality and zero
Excelchat Expert
10/09/2018 - 08:39
So averageif is a pretty simple function - we write the range and then condition, that is it
User
10/09/2018 - 08:39
WOW!!!!! I was so close tring on my own but I only included the greater than sign....:)
User
10/09/2018 - 08:40
Is the comma required after the A9?
Excelchat Expert
10/09/2018 - 08:40
Now you know one more tool on your way to automation
Excelchat Expert
10/09/2018 - 08:40
Yes, it is the separator of the function agruments
Excelchat Expert
10/09/2018 - 08:41
The first argument is our range - A1:A9
Excelchat Expert
10/09/2018 - 08:41
The second our condition
Excelchat Expert
10/09/2018 - 08:41
The condition should be typed in the quotes
User
10/09/2018 - 08:41
I was sooooo close LOL
Excelchat Expert
10/09/2018 - 08:42
Yes, it happens that users know how to make the right logic, but they don't know the right syntax of the function
User
10/09/2018 - 08:42
It's been several years since I used a lot of what I am using in a particular workbook these past three days....
Excelchat Expert
10/09/2018 - 08:42
So now you know it and I am sure you will create great solutions based on it
Excelchat Expert
10/09/2018 - 08:43
Never too late to learn again :)
User
10/09/2018 - 08:44
My workbook is for a tiny sewer plant operation. One already existed from previous employee but with many flaws.... In Feb of this year I got most of those flaws fixed and the whole work looking and functioning well.
Excelchat Expert
10/09/2018 - 08:44
Do you have more questions about the averageif function?
Excelchat Expert
10/09/2018 - 08:45
Well, if you have more Excel problems, feel free to reach us
Excelchat Expert
10/09/2018 - 08:45
It might save you a lot of time
User
10/09/2018 - 08:45
On Friday the facility was inspected by State and we came up with a different system that would better suit our needs.... hence my work.... This was the ONLY solution I was having issues with
User
10/09/2018 - 08:46
THANK YOU SO MUCH!!!!
Excelchat Expert
10/09/2018 - 08:46
You are welcome!
Excelchat Expert
10/09/2018 - 08:46
I wish you a great day then! You may end the session and leave a feedback :)
User
10/09/2018 - 08:46
Have a great week
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.
