Expert Solution - Excel AVERAGE Problems

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

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

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!