Excel - COLUMN Function Problem - Expert Solution

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

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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc