Conditional averaging

Thanks to everyone in this group I have gotten much much better with
SUMPRODUCT. Unfortunately this is not helping with this next
conundrum I am facing. Here is the file:

http://www.batattak-records.com/sample2.xls

There are employees assigned to specific supervisors. Once or twice a
month they are tested and that test is assigned a score. Getting
supervisor averages is not hard. The trick here is that exployees in
their first 30 days of employment are tested but their scores do not
count. They are simply diagnostics.

The Averages tab is where I need to place the averages for the
employees in Active status (non-30 day), 30 Day, and combined averages
(the easy one). An added bonus would be able to see the number of
tests for each supervisor's status group.

Test scores are on the data tab.

Employee status (Active and 30 Day) is on the status tab.

Sure...a pivot table would works wonders here but it has to be 100%
formula based. This is also very similar to the problem that Biff was
kind enough to help me with a while back but taken out several degrees
further.

Hi!

I'm looking at your file......

Can't be done! (with the setup you have)

You need to move your "status" criteria to the Data sheet then it would be a
formula like this entered as an array:

=AVERAGE(IF(Data!A$2:A$16=A3,IF(Data!xxxxxxxx="Active",Data!C$2:C$16)))

xxxxxxx = range where you put the "status" criteria.

Biff

"^'^BatAttaK^'^" <[email protected]> wrote in message
news:[email protected]...
> Thanks to everyone in this group I have gotten much much better with
> SUMPRODUCT. Unfortunately this is not helping with this next
> conundrum I am facing. Here is the file:
>
> http://www.batattak-records.com/sample2.xls
>
> There are employees assigned to specific supervisors. Once or twice a
> month they are tested and that test is assigned a score. Getting
> supervisor averages is not hard. The trick here is that exployees in
> their first 30 days of employment are tested but their scores do not
> count. They are simply diagnostics.
>
> The Averages tab is where I need to place the averages for the
> employees in Active status (non-30 day), 30 Day, and combined averages
> (the easy one). An added bonus would be able to see the number of
> tests for each supervisor's status group.
>
> Test scores are on the data tab.
>
> Employee status (Active and 30 Day) is on the status tab.
>
> Sure...a pivot table would works wonders here but it has to be 100%
> formula based. This is also very similar to the problem that Biff was
> kind enough to help me with a while back but taken out several degrees
> further.

Provided that your 'Status' sheet is sorted by Column B (Employee Name)
in ascending order, try the following array formulas that need to be
confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

Averages!B2, copied down:

=AVERAGE(IF((Data!$A$2:$A$16=Averages!$A2)*(ISNUMBER(MATCH(Data!$B$2:$B$1
6,Status!$B$2:$B$16,0)))*(LOOKUP(Data!$B$2:$B$16,Status!$B$2:$C$16)="Acti
ve"),Data!$C$2:$C$16))

Averages!C2, copied down:

=AVERAGE(IF((Data!$A$2:$A$16=Averages!$A2)*(ISNUMBER(MATCH(Data!$B$2:$B$1
6,Status!$B$2:$B$16,0)))*(LOOKUP(Data!$B$2:$B$16,Status!$B$2:$C$16)="30
Day"),Data!$C$2:$C$16))

Averages!D2, copied down:

=AVERAGE(IF(Data!$A$2:$A$16=Averages!$A2,Data!$C$2:$C$16))

Hope this helps!

In article <[email protected]>,
^'^BatAttaK^'^ <[email protected]> wrote:

> Thanks to everyone in this group I have gotten much much better with
> SUMPRODUCT. Unfortunately this is not helping with this next
> conundrum I am facing. Here is the file:
>
> http://www.batattak-records.com/sample2.xls
>
> There are employees assigned to specific supervisors. Once or twice a
> month they are tested and that test is assigned a score. Getting
> supervisor averages is not hard. The trick here is that exployees in
> their first 30 days of employment are tested but their scores do not
> count. They are simply diagnostics.
>
> The Averages tab is where I need to place the averages for the
> employees in Active status (non-30 day), 30 Day, and combined averages
> (the easy one). An added bonus would be able to see the number of
> tests for each supervisor's status group.
>
> Test scores are on the data tab.
>
> Employee status (Active and 30 Day) is on the status tab.
>
> Sure...a pivot table would works wonders here but it has to be 100%
> formula based. This is also very similar to the problem that Biff was
> kind enough to help me with a while back but taken out several degrees
> further.

Correction...

Averages!B2, copied down:

=AVERAGE(IF((Data!$A$2:$A$16=Averages!$A2)*(ISNUMBER(MATCH(Data!$B$2:$B$1
6,Status!$B$2:$B$16,0)))*ISNUMBER(1/(LOOKUP(Data!$B$2:$B$16,Status!$B$2:$
C$16)="Active")),Data!$C$2:$C$16))

Averages!C2, copied down:

=AVERAGE(IF((Data!$A$2:$A$16=Averages!$A2)*(ISNUMBER(MATCH(Data!$B$2:$B$1
6,Status!$B$2:$B$16,0)))*ISNUMBER(1/(LOOKUP(Data!$B$2:$B$16,Status!$B$2:$
C$16)="30 Day")),Data!$C$2:$C$16))

Hope this helps!

In article <[email protected]>,
Domenic <[email protected]> wrote:

> Provided that your 'Status' sheet is sorted by Column B (Employee Name)
> in ascending order, try the following array formulas that need to be
> confirmed with CONTROL+SHIFT+ENTER, not just ENTER...
>
> Averages!B2, copied down:
>
> =AVERAGE(IF((Data!$A$2:$A$16=Averages!$A2)*(ISNUMBER(MATCH(Data!$B$2:$B$1
> 6,Status!$B$2:$B$16,0)))*(LOOKUP(Data!$B$2:$B$16,Status!$B$2:$C$16)="Acti
> ve"),Data!$C$2:$C$16))
>
> Averages!C2, copied down:
>
> =AVERAGE(IF((Data!$A$2:$A$16=Averages!$A2)*(ISNUMBER(MATCH(Data!$B$2:$B$1
> 6,Status!$B$2:$B$16,0)))*(LOOKUP(Data!$B$2:$B$16,Status!$B$2:$C$16)="30
> Day"),Data!$C$2:$C$16))
>
> Averages!D2, copied down:
>
> =AVERAGE(IF(Data!$A$2:$A$16=Averages!$A2,Data!$C$2:$C$16))
>
> Hope this helps!

On Mon, 13 Jun 2005 18:53:21 -0400, Domenic <[email protected]>
wrote:

>Correction...
>
>Averages!B2, copied down:
>
>=AVERAGE(IF((Data!$A$2:$A$16=Averages!$A2)*(ISNUMBER(MATCH(Data!$B$2:$B$1
>6,Status!$B$2:$B$16,0)))*ISNUMBER(1/(LOOKUP(Data!$B$2:$B$16,Status!$B$2:$
>C$16)="Active")),Data!$C$2:$C$16))
>
>Averages!C2, copied down:
>
>=AVERAGE(IF((Data!$A$2:$A$16=Averages!$A2)*(ISNUMBER(MATCH(Data!$B$2:$B$1
>6,Status!$B$2:$B$16,0)))*ISNUMBER(1/(LOOKUP(Data!$B$2:$B$16,Status!$B$2:$
>C$16)="30 Day")),Data!$C$2:$C$16))
>
>Hope this helps!

You are my new hero!