# Excel - AVERAGE Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need to calculate average number of male and female directors for each company in the Excel sheet.
Solved by D. Y. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 29/05/2018 - 09:03
Hello!
Excelchat Expert 29/05/2018 - 09:03
Hi
Excelchat Expert 29/05/2018 - 09:04
Excelchat Expert 29/05/2018 - 09:04
Please share the sample info in the preview.
User 29/05/2018 - 09:04
Excelchat Expert 29/05/2018 - 09:04
Is it possible to paste the data in the preview?
User 29/05/2018 - 09:05
I don't think so
User 29/05/2018 - 09:05
It's a larger file
Excelchat Expert 29/05/2018 - 09:05
User 29/05/2018 - 09:05
User 29/05/2018 - 09:05
Here it is :)
Excelchat Expert 29/05/2018 - 09:06
Unable to open. Getting an error. There is problem with document.
Excelchat Expert 29/05/2018 - 09:07
Please try using the option of google sheet while posting the session. That helps. We have 2 options, either excel or google sheet.
Excelchat Expert 29/05/2018 - 09:07
I can write base example if required in the preview.
User 29/05/2018 - 09:07
I didn't really get that
Excelchat Expert 29/05/2018 - 09:08
It can be calculated using averageif formuala.
User 29/05/2018 - 09:09
User 29/05/2018 - 09:09
User 29/05/2018 - 09:09
I am insisting on sending the file
User 29/05/2018 - 09:09
Not only because I do not know how to do it
User 29/05/2018 - 09:09
But also because I have trouble opening large file
User 29/05/2018 - 09:09
Don't have a great PC tbh
Excelchat Expert 29/05/2018 - 09:10
User 29/05/2018 - 09:10
That would be great thanks :)
Excelchat Expert 29/05/2018 - 09:10
Ok. I have opened the file.
Excelchat Expert 29/05/2018 - 09:11
E and D columns contains Gender and company name
Excelchat Expert 29/05/2018 - 09:11
You want to split the data of M and F count for each company?
Excelchat Expert 29/05/2018 - 09:11
is it based on the function? or for all functions?
User 29/05/2018 - 09:12
Yes exactly, I want to know how many F and M are in the positions that are filtered
User 29/05/2018 - 09:12
Also what percentage that is
User 29/05/2018 - 09:12
Only for the
Excelchat Expert 29/05/2018 - 09:12
That is filtered?
User 29/05/2018 - 09:12
Filtered functions
User 29/05/2018 - 09:12
I already filtered the doc I believe
User 29/05/2018 - 09:12
So only directors, board members, owners and CFs are included
Excelchat Expert 29/05/2018 - 09:12
There are many items filtered.
Excelchat Expert 29/05/2018 - 09:13
I can do one thing.
Excelchat Expert 29/05/2018 - 09:13
I will create pivot table and slicer.
Excelchat Expert 29/05/2018 - 09:13
You can use the slicer to filter the data.
User 29/05/2018 - 09:14
How do I do that?
Excelchat Expert 29/05/2018 - 09:14
I am working on it. I will send you the file.
User 29/05/2018 - 09:14
Sure (Y)
Excelchat Expert 29/05/2018 - 09:17
Attachment exceeds 20 MB.
Excelchat Expert 29/05/2018 - 09:18
I will share with dropbox link.
User 29/05/2018 - 09:18
Alright
User 29/05/2018 - 09:18
That's fine with me :)
Excelchat Expert 29/05/2018 - 09:19
https://www.dropbox.com/s/hmtdleoy3uaoacs/MediumCompaniesThesis_v1.xlsx?dl=0
Excelchat Expert 29/05/2018 - 09:19
Excelchat Expert 29/05/2018 - 09:20
You can use the filter at the top in the pivot table and select what ever you want.
User 29/05/2018 - 09:21
So
User 29/05/2018 - 09:21
I should
User 29/05/2018 - 09:21
Filter
User 29/05/2018 - 09:21
It and send it back?
Excelchat Expert 29/05/2018 - 09:21
No. The data updaes automatically based on your filter.
User 29/05/2018 - 09:23
So where can I see the results
User 29/05/2018 - 09:23
I mean where
User 29/05/2018 - 09:23
Can I see the
User 29/05/2018 - 09:24
Directors per gender per company and %?
Excelchat Expert 29/05/2018 - 09:24
There is a second tab.
Excelchat Expert 29/05/2018 - 09:24
Where it shows the count.
Excelchat Expert 29/05/2018 - 09:24
And at the top you will see a funnel icon.
User 29/05/2018 - 09:25
Alright
User 29/05/2018 - 09:25
And this is
User 29/05/2018 - 09:26
Unfiltered correct?
User 29/05/2018 - 09:26
It includes all of the positions?
Excelchat Expert 29/05/2018 - 09:26
yes.
User 29/05/2018 - 09:26
Can the file I sent be also made this way? Because it takes about 5 minutes to eliminate one position
Excelchat Expert 29/05/2018 - 09:27
Based on your filter, you can see the updated values.
Excelchat Expert 29/05/2018 - 09:27
No need to filter in the first tab.
Excelchat Expert 29/05/2018 - 09:27
Just remove the filter in the first tab and apply only in second tab.
Excelchat Expert 29/05/2018 - 09:27
you can choose from the list.
User 29/05/2018 - 09:28
Wait
User 29/05/2018 - 09:28
I see the second tab
User 29/05/2018 - 09:28
Is filtered
User 29/05/2018 - 09:28
My filters are there
User 29/05/2018 - 09:28
How do I apply them to the first tab?
User 29/05/2018 - 09:28
There all of the positions are included
User 29/05/2018 - 09:28
?
Excelchat Expert 29/05/2018 - 09:29
they both are different.
Excelchat Expert 29/05/2018 - 09:29
first one is just data.
Excelchat Expert 29/05/2018 - 09:29
Second tab is consolidated tab and you can review the numbers based on the filtered items.
Excelchat Expert 29/05/2018 - 09:30
If you want to filter for the first tab, you have to do it separately.
User 29/05/2018 - 09:32
I am a bit confused
User 29/05/2018 - 09:32
You said I can review numbers based on the filtered items
User 29/05/2018 - 09:32
But on the
User 29/05/2018 - 09:32
Second tab
User 29/05/2018 - 09:32
There are no numbers
Excelchat Expert 29/05/2018 - 09:32
Both sheets are independent in filters point of view.
Excelchat Expert 29/05/2018 - 09:33
There are numbers in B and C columns?
User 29/05/2018 - 09:35
We considered different tabs to be first and second
User 29/05/2018 - 09:35
I though second tab meant WRDS
User 29/05/2018 - 09:35
Now I see what you mean
User 29/05/2018 - 09:35
Now my questions is
Excelchat Expert 29/05/2018 - 09:35
User 29/05/2018 - 09:35
How do I update the numbers in the new tab
User 29/05/2018 - 09:36
To reflect only the needed positions
User 29/05/2018 - 09:36
Because I cannot click them
User 29/05/2018 - 09:36
One by one
User 29/05/2018 - 09:36
It takes about 5 minutes for Excel to process
User 29/05/2018 - 09:36
One click for one position
User 29/05/2018 - 09:36
And there are hundreds of them
Excelchat Expert 29/05/2018 - 09:36
The numbers are pulled from first tab in pivot table tab, at the top there is a filter option.
Excelchat Expert 29/05/2018 - 09:37
I have called position as filter option.
User 29/05/2018 - 09:37
I understand that part, I also know I can filter them again in the new pivot tab
Excelchat Expert 29/05/2018 - 09:37
So, you can click on the funnel and choose the list. that filters automatically.
Excelchat Expert 29/05/2018 - 09:37
Ok.
User 29/05/2018 - 09:38
I know that, what I repeatedly keep saying is that
User 29/05/2018 - 09:38
It takes Excel about 5 minutes to process one click
User 29/05/2018 - 09:38
One position elimination
User 29/05/2018 - 09:38
In the filter section
Excelchat Expert 29/05/2018 - 09:38
As the data huge, it takes same time.
User 29/05/2018 - 09:38
Can the pivot be done directly with the filters in mind?
Excelchat Expert 29/05/2018 - 09:39
There is an option at the top for you quickly select.
Excelchat Expert 29/05/2018 - 09:39
When you click on funnel, you will see a text box.
Excelchat Expert 29/05/2018 - 09:39
type the desired text and the positions are filtered, you can simply choose them.
Excelchat Expert 29/05/2018 - 09:40
Ex: director, deputy, etc.
Excelchat Expert 29/05/2018 - 09:40
So, it list all the positions that are containing the word in the text box.
User 29/05/2018 - 09:41
Yes I know that
User 29/05/2018 - 09:41
But when I type director
User 29/05/2018 - 09:41
I first have to unselect all (this makes Excel crash)
User 29/05/2018 - 09:42
And the select every single one that contains those key words
Excelchat Expert 29/05/2018 - 09:43
If you want to deselect All (All).
Excelchat Expert 29/05/2018 - 09:43
As the data is huge, excel has limitations to handle it.
Excelchat Expert 29/05/2018 - 09:44
Deselecting all, will not be a problem.
Excelchat Expert 29/05/2018 - 09:44
I tried it. It works well. once it is done, you can choose the keywords and select one by one. That is the only option available.
User 29/05/2018 - 09:46
I tried that now
User 29/05/2018 - 09:46
It actually
User 29/05/2018 - 09:46
Works quickly
User 29/05/2018 - 09:46
Thank you very much!
Excelchat Expert 29/05/2018 - 09:46
Yes.
User 29/05/2018 - 09:46
I appreciate the help
Excelchat Expert 29/05/2018 - 09:46
Thank you .
Excelchat Expert 29/05/2018 - 09:46
You are welcome.
Excelchat Expert 29/05/2018 - 09:46
Excelchat Expert 29/05/2018 - 09:46
Excelchat Expert 29/05/2018 - 09:46
Please leave great feedback after ending the session.

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.