Function help???
 

Ok. I have three columns of text I'm dealing with here. The first column is a
the "Athletes Name" column, which represents the athletes name, the second
column is "M or F" which represents Male or Female, and the last column is
"Team Name", which represents the team each person is play on. I have to give
an analysis based on how many all male teams, female teams and co-ed teams we
currently have competing based on the information provided. Is there a
function on excel that can pull the teams that only have male athletes,
female athletes and teams comprising both male and female athletes? I hope
this isn't too confusing. Please help...thanks.

set a fourth column D?(A,B,C)
enter in C1
=if(sumProduct(--($C$1:$C$1000=C1),--($B$1:$B$1000="M"))=sumProduct(--($C$1:$C$1000=C1)),"M",if(sumProduct(--($C$1:$C$1000=C1),--($B$1:$B$1000="F"))=sumProduct(--($C$1:$C$1000=C1)),"F","both"))
M indicates all male, F indicates all female, both indicates mix.

"Troy S." wrote:

Ok. I have three columns of text I'm dealing with here. The first column is a
the "Athletes Name" column, which represents the athletes name, the second
column is "M or F" which represents Male or Female, and the last column is
"Team Name", which represents the team each person is play on. I have to give
an analysis based on how many all male teams, female teams and co-ed teams we
currently have competing based on the information provided. Is there a
function on excel that can pull the teams that only have male athletes,
female athletes and teams comprising both male and female athletes? I hope
this isn't too confusing. Please help...thanks.

On Tue, 17 May 2005 10:25:27 -0700, "Troy S." <Troy
wrote:

Ok. I have three columns of text I'm dealing with here. The first column is a
the "Athletes Name" column, which represents the athletes name, the second
column is "M or F" which represents Male or Female, and the last column is
"Team Name", which represents the team each person is play on. I have to give
an analysis based on how many all male teams, female teams and co-ed teams we
currently have competing based on the information provided. Is there a
function on excel that can pull the teams that only have male athletes,
female athletes and teams comprising both male and female athletes? I hope
this isn't too confusing. Please help...thanks.

One way to generate a report would be to use a Pivot Table and Chart. I assume
your columns are named as above, with the M or F column named Sex.

With the cursor in your table, select Data/Pivot Table and Pivot Chart Report

What Kind of Report? Pivot Chart report (with ...

Next/Next and select new worksheet.

When the graph pops up, drag Team to the Category area; Sex to the series field
area, and Sex also to the Data area.

You should get a stacked bar graph; one bar for each team; with different
colors representing M or F


--ron