TRIMMEAN on only certain data

  • Thread starter Thread starter cathcarr
  • Start date Start date
C

cathcarr

Hi there,

I have some data in 2 columns - one shows a team name, and the other
shows how long in minutes it took them to fix the particular problem
that is on that row.
For example:
Ticket 1 Network Outage Team A 310
Ticket 2 Bouncing circuit Team B 900
Ticket 3 Network Outage Team A 1200
etc....

I need to do a trimmed mean of the time it took to repair the faults. I
understand that if I use TRIMMEAN(B2:B999,0.25) that will give me an
average excluding the top and bottom 2.5% for all of the data.

The problem is, I need to be able to do a TRIMMEAN for only Team A, and
another one for only Team B. I know I could split the data up into 2
sheets, but I am trying to make this as easy as possible to produce
each week!

Any assistance would be greatly appreciated!

Catherine
 
One way

=TRIMMEAN(IF(A2:A999="Team A",B2:B999),0.25)

entered with ctrl + shift & enter

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Hi there,

I have some data in 2 columns - one shows a team name, and the other
shows how long in minutes it took them to fix the particular problem
that is on that row.
For example:
Ticket 1 Network Outage Team A 310
Ticket 2 Bouncing circuit Team B 900
Ticket 3 Network Outage Team A 1200
etc....

I need to do a trimmed mean of the time it took to repair the faults. I
understand that if I use TRIMMEAN(B2:B999,0.25) that will give me an
average excluding the top and bottom 2.5% for all of the data.

The problem is, I need to be able to do a TRIMMEAN for only Team A, and
another one for only Team B. I know I could split the data up into 2
sheets, but I am trying to make this as easy as possible to produce
each week!

Any assistance would be greatly appreciated!
Click to expand...

Try this:

Array-enter: =TRIMMEAN(IF(C1:C10="Team A",D1:D10),0.25)

To array-enter a formula, after copying or pasting it in, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula.

Oh, and your formula excludes the top and bottom 25%, not 2.5%.


--ron
 
Back
Top