Averging data when criteria is met

[cherman]

I have data that looks like this:

WW_Yr OpenDays Sminor Smedium Scritical Sundefined

WW01 14 1 0 0 0
WW01 1 0 1 0 0
WW02 15 1 0 0 0
WW02 129 0 0 1 0
WW03 17 0 0 0 0
WW04 13 1 0 0 0

etc.

This data starts in cell A1 and goes across to column M and the number of
rows can be different.

I can have 1 or more records with the same WW_Yr value. The other 5 columns
will have either the number "1" or "0". There will only be a "1" in one of
the fields and the rest will be "0".

I want to create a dynamic table based on this data, with the data starting
in cell O3, 4 columns wide and 26 rows.

I will add to the the 1st column to the sheet from code, which will have the
26 distinct WW_Yr values from my source data above.

The next column needs to be an average of OpenDays for each of the 26 WWW_Yr
value where Sminor = 1, or it needs to be a 0 if no records are found to meet
this criteria. It will work the same for Smedium, Scritical & Sundefined.

Any help on how I can do this averging with these conditions and build out
my table would be much appreciated.

Thanks,
Clint

 

[Bernard Liengme]

Clint:
You have asked this in the programming newsgroup but it can all be done with
formulas
With the data you show starting in A1:
With O3 having the value WW01, O4 having WW02, etc
In P3 enter
=SUMPRODUCT(--($A$1:$A$200=$O3),--(C$1:C$200=1),$B$1:$B$200)/SUMPRODUCT(--($A$1:$A$200=$O3),--(C$1:C$200=1))
Carefully note the use of absolute and semi-absolute references. This allows
us to copy the formula across to column S and down to row 28

However, we get DIV0! errors in many cases, so we need to adapt this to
=IF(SUMPRODUCT(--($A$1:$A$200=$O3),--(C$1:C$200=1)),SUMPRODUCT(--($A$1:$A$200=$O3),--(C$1:C$200=1),$B$1:$B$200)/SUMPRODUCT(--($A$1:$A$200=$O3),--(C$1:C$200=1)),0)

Note that the test SUMPRODUCT(--($A$1:$A$200=$O3),--(C$1:C$200=1)) will
compute to 0 or 1 which Excel will interpret as =IF(this_value<>0, do this,
do_that)

I have used A1:A200 but you can use any range. It does not matter if there
are empty rows. Except in Excel 2007 you cannot use full column references
(e.g B:B)

If you are using Excel 2007, we can use a more simple formula in P3:
=IFERROR(AVERAGEIFS(B:B,A:A,$O3,C:C,1),0)
This can be copied down the column, but when you copy across to Q3 you must
change C:C to D, and so on.

best wishes