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

It's pleasure to help you today.

Excelchat Expert
29/05/2018 - 09:04

Please share the sample info in the preview.

User
29/05/2018 - 09:04

Can I send you a google sheet link?

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

OK. Share the google sheet link here.

User
29/05/2018 - 09:05

https://drive.google.com/open?id=1961rCml4TxgTFgFO8pOtZTKjQkDHOj9T

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

Can you please try this link?

User
29/05/2018 - 09:09

https://drive.google.com/open?id=1961rCml4TxgTFgFO8pOtZTKjQkDHOj9T

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

I have same problem again. I will try downloading th file.

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

Please download it.

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

Ok. Please let me know.

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

Please visit Got IT Pro.

Excelchat Expert
29/05/2018 - 09:46

Have a great day ahead.

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