Excel - General Question on Pivot Table - Expert Solution

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.

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.