Question description:
This user has given permission to use the problem statement for this
blog.
I want to calculate an average of some values in a pivot table, however I cannot do that as some rows contain blanks.
Solved by I. C. in 58 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
30/05/2018 - 10:38
Hello!
Excelchat Expert
30/05/2018 - 10:38
Hello
Excelchat Expert
30/05/2018 - 10:38
Welcome to excelchat
User
30/05/2018 - 10:39
Thans, nice to meet you
Excelchat Expert
30/05/2018 - 10:39
I believe you want help with average, correct?
User
30/05/2018 - 10:40
Exactly
User
30/05/2018 - 10:40
https://drive.google.com/open?id=1okNrlGB0A1AYqO7ei1JHoMvYlJcUT6zM
User
30/05/2018 - 10:40
This is the file
User
30/05/2018 - 10:40
It's quite big
User
30/05/2018 - 10:40
But the operation I need help with is quite simple
User
30/05/2018 - 10:40
You will see two average rows
User
30/05/2018 - 10:40
On the side, the formulas are already there
Excelchat Expert
30/05/2018 - 10:40
Okay, give me a few minutes please.
User
30/05/2018 - 10:41
But I cannot continue calculating because some firms do not have any info on gender
User
30/05/2018 - 10:41
:)
User
30/05/2018 - 10:41
Sure, take your time!
Excelchat Expert
30/05/2018 - 10:42
this is a big file indeed.
Excelchat Expert
30/05/2018 - 10:43
Still downloading..
Excelchat Expert
30/05/2018 - 10:43
at 40 MB, how big is this!!!
User
30/05/2018 - 10:44
I think it is 50MB
User
30/05/2018 - 10:44
But what I need is really
User
30/05/2018 - 10:44
Simple
User
30/05/2018 - 10:44
Sorry for the large file :(((((
Excelchat Expert
30/05/2018 - 10:44
Not a problem. Just finished downloading.
Excelchat Expert
30/05/2018 - 10:47
Finally, its opened now. Please tell me where to look.
User
30/05/2018 - 10:47
The first sheet
User
30/05/2018 - 10:47
Where the pivot table is
User
30/05/2018 - 10:47
There are rows M and F
User
30/05/2018 - 10:47
Indicating gender
Excelchat Expert
30/05/2018 - 10:47
Okay, I'm there.
User
30/05/2018 - 10:47
Then there are average columns
User
30/05/2018 - 10:48
However some firms have no males nor females
User
30/05/2018 - 10:48
I would like to exclude
User
30/05/2018 - 10:48
Those
User
30/05/2018 - 10:48
So the DIV/0 does not come up
User
30/05/2018 - 10:48
That's one thing
User
30/05/2018 - 10:48
And the other thing would be an total average for both F and M at the bottom of the table
Excelchat Expert
30/05/2018 - 10:49
To be sure, we are talking about sheet1?
User
30/05/2018 - 10:50
Exactly
Excelchat Expert
30/05/2018 - 10:52
I'm sorry, you have said average column, but I can't find it. I know you have told everything.
Excelchat Expert
30/05/2018 - 10:53
Can you please say something like calculate this in this column?
User
30/05/2018 - 10:54
I didn't quite get that
User
30/05/2018 - 10:54
What
User
30/05/2018 - 10:54
Is your
User
30/05/2018 - 10:54
Question?
Excelchat Expert
30/05/2018 - 10:54
Which is the average column?
User
30/05/2018 - 10:54
Columns F and G
User
30/05/2018 - 10:54
% of M and F in each firms
User
30/05/2018 - 10:54
And the average should be at the bottom
User
30/05/2018 - 10:54
Of F and G :)
User
30/05/2018 - 10:54
Hope this clears it up
Excelchat Expert
30/05/2018 - 10:57
Please extend the session time.
Excelchat Expert
30/05/2018 - 10:58
=IF(AND(B5="",C5=""),"",B5/E5)
Excelchat Expert
30/05/2018 - 10:58
Apply this formula in column F, this would remove div/0 errors.
Excelchat Expert
30/05/2018 - 10:59
If you are still there, you can write in the preview window.
Excelchat Expert
30/05/2018 - 11:00
You were asked whether you need more time or not a few minutes ago.
Excelchat Expert
30/05/2018 - 11:00
You won't be able to write here but you can answer in the sheet.
Excelchat Expert
30/05/2018 - 11:01
The file is too big, it would probably take an hour if I try to upload it.
Excelchat Expert
30/05/2018 - 11:02
I don't know what you mean but if you want to apply a formula in a whole column, you can use the fill handle.
Excelchat Expert
30/05/2018 - 11:03
Fill handle is the tiny square box at the bottom left corner of a cell when you select that cell. Double clicking it will copy the formula down the whole column.
Excelchat Expert
30/05/2018 - 11:04
Does that make sense?
Excelchat Expert
30/05/2018 - 11:06
Let me know if you need further help on fill handle.
Excelchat Expert
30/05/2018 - 11:09
yes there is. but I can see you are calculating percentage, not average. And thus you don't need to exclude.
Excelchat Expert
30/05/2018 - 11:11
"Fills untill the next empty cell" it is not a problem. it is just how fill handle works.
Excelchat Expert
30/05/2018 - 11:16
Let's be clear, if you want to get the average, the formula AVERAGEIF will give you the average ignoring empty cells.
Excelchat Expert
30/05/2018 - 11:18
secondly, each time the fill handle encounter a empty cell, the fill would stop. So, you would need to use the fill again where it stopped.
Excelchat Expert
30/05/2018 - 11:19
The formula I gave you giving me the percentage of female 29% at the bottom ignoring the blank cells.
Excelchat Expert
30/05/2018 - 11:19
I hope this clears things.
Excelchat Expert
30/05/2018 - 11:20
Yeah, insert a new column after column A. since column A doesn't contain any empty cells, the fill handle will go all the way down.
Excelchat Expert
30/05/2018 - 11:25
Sorry, keeping the huge file open on my pc causing my pc to hang time to time.
Excelchat Expert
30/05/2018 - 11:27
=AVERAGEIF(E4:E7,"<>0")
Excelchat Expert
30/05/2018 - 11:28
The formula At F4
Excelchat Expert
30/05/2018 - 11:28
This is calculating average ignoring empty cells.
Excelchat Expert
30/05/2018 - 11:29
We have only 9 more minutes remaining.
Excelchat Expert
30/05/2018 - 11:30
After that we would get disconnected.
Excelchat Expert
30/05/2018 - 11:33
It should fill if column A has data on it.
Excelchat Expert
30/05/2018 - 11:34
We are approaching the last minutes, I wish I could send you the file but my internet speed would take almost an hour to upload that file.
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.
