Averaging

[arjcvg]

For Example I have this data:

per wave
50.00% Wave2
100.00% Wave2
100.00% Wave2
50.00% Wave2
75.00% Wave2
33.00% Wave2
50.00% Wave2
75.00% Wave2
86.00% Wave2
50.00% Wave2
0.00% Wave2
100.00% Wave2
50.00% Wave3
100.00% Wave3
100.00% Wave3
50.00% Wave3
100.00% Wave3
60.00% Wave3
40.00% Wave3
50.00% Wave3
50.00% Wave3
60.00% Wave3
50.00% Wave3
25.00% Wave3
50.00% Wave3
100.00% Wave3
100.00% Wave3
67.00% Wave3
0.00% Wave3
40.00% Wave3
50.00% Wave3
0.00% Wave3
50.00% Wave3
67.00% Wave3


what formula should i use to average the data and for the report t
look like this:

70% 60-70% <60%

WAVE AVERAGE AVERAGE AVERAGE
Wave2 89.33% 0.00% 38.83%
Wave3 100.00% 63.50% 38.85%


THANKS A LOT

 

[Max]

One way ..

Assuming the sample data is in Sheet1, in A1:B35
data from row2 down, viz.:

per wave
50.00% Wave2
100.00% Wave2
100.00% Wave2

etc

and the desired output below is in say Sheet2, in A14

WAVE AVERAGE AVERAGE AVERAGE
Wave2 89.33% 0.00% 38.83%
Wave3 100.00% 63.50% 38.85%

To get the results in B34 ..

Put in B3 and array-enter*:
=IF(ISERROR(AVERAGE(IF((Sheet1!$B$2:$B$35=$A3)*(Sheet1!$A$2:$A$35>0.7),Sheet
1!$A$2:$A$35))),0,AVERAGE(IF((Sheet1!$B$2:$B$35=$A3)*(Sheet1!$A$2:$A$35>0.7)
,Sheet1!$A$2:$A$35)))

Put in C3 and array-enter*:
=IF(ISERROR(AVERAGE(IF((Sheet1!$B$2:$B$35=$A3)*(Sheet1!$A$2:$A$35<=0.7)*(She
et1!$A$2:$A$35>=0.6),Sheet1!$A$2:$A$35))),0,AVERAGE(IF((Sheet1!$B$2:$B$35=$A
3)*(Sheet1!$A$2:$A$35<=0.7)*(Sheet1!$A$2:$A$35>=0.6),Sheet1!$A$2:$A$35)))

Put in D3 and array-enter*:
=IF(ISERROR(AVERAGE(IF((Sheet1!$B$2:$B$35=$A3)*(Sheet1!$A$2:$A$35<0.6),Sheet
1!$A$2:$A$35))),0,AVERAGE(IF((Sheet1!$B$2:$B$35=$A3)*(Sheet1!$A$2:$A$35<0.6)
,Sheet1!$A$2:$A$35)))

*Note: Array-entering a formula means
pressing CTRL+SHIFT+ENTER
instead of just pressing ENTER

Select B33, format as percentage (2 d.p), and copy down to D4

This'll return the results indicated

 

[Biff]

Here's the non-array version:

70% =IF(ISERROR(SUMPRODUCT(--(B1:B34="wave2"),--

(A1:A34>0.7),A1:A34)/SUMPRODUCT(--(B1:B34="wave2"),--
(A1:A34>0.7))),0,SUMPRODUCT(--(B1:B34="wave2"),--
(A1:A34>0.7),A1:A34)/SUMPRODUCT(--(B1:B34="wave2"),--
(A1:A34>0.7)))

60-70% =IF(ISERROR(SUMPRODUCT(--(B1:B34="wave2"),--
(A1:A34>=0.6),--(A1:A34<=0.7),A1:A34)/SUMPRODUCT(--
(B1:B34="wave2"),--(A1:A34>=0.6),--
(A1:A34<=0.7))),0,SUMPRODUCT(--(B1:B34="wave2"),--
(A1:A34>=0.6),--(A1:A34<=0.7),A1:A34)/SUMPRODUCT(--
(B1:B34="wave2"),--(A1:A34>=0.6),--(A1:A34<=0.7)))

<60% =IF(ISERROR(SUMPRODUCT(--(B1:B34="wave2"),--
(A1:A34<0.6),A1:A34)/SUMPRODUCT(--(B1:B34="wave2"),--
(A1:A34<0.6))),0,SUMPRODUCT(--(B1:B34="wave2"),--
(A1:A34<0.6),A1:A34)/SUMPRODUCT(--(B1:B34="wave2"),--
(A1:A34<0.6)))

No joy either way!

Biff