Conditional averaging

  • Thread starter Thread starter ^'^BatAttaK^'^
  • Start date Start date
B

^'^BatAttaK^'^

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
 
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!
 
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!
 
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!
Click to expand...

You are my new hero!
 
Back
Top