B

#### Bermie

24h 1wk change

Person A 1 0 -1

Person B 0 1 1

Person C 0 0 0

Person D 0.25 0.25 0

Person E 0.5

Person F 1 0.5 -0.5

Person G 0 0.75 0.75

What I am trying to do is make three equations:

1 to calculate the average of the values in the 24h column, IF the value is

NOT equal to zero, which I have accomplished with the array formula:

{=AVERAGE(IF(B2:B8>0, B2:B8, ""))}

here's where things get difficult--now, I want to find the average of the

values in the 1wk column, only if either the values in the 24h OR 1 wk column

are not zero, so the value, in this case, would be=0.5 (from averaging Person

A, B, D, F, and G's 1 wk values). I tried with the following formula:

{=AVERAGE(IF(OR(B2:B8>0, C2:C8>0), C2:C8, ""))}, but it returns 0.4167

(using the same function, but with countif instead of average, it returns 6

instead of returning 5, so it's adding an extra person's value), so i need

help in retifying this error.

Also, even more difficult:

Finally, i'd like to find the average of the amount of change, ONLY IF

either the 24h or the 1 wk values are not zero, so, in this case, the value

should equal 0.05 (the average change of person A, B, D, F, and G), however,

with the formula:

{=AVERAGE(IF(OR(B2:B8>0, C2:C8>0), D28, ""))}, i get 0.04167 (again, the

count function gives me 6 instead of 5). I also tried it with the formula:

{=AVERAGE(IF(OR(OFFSET(D28, 0,-2)>0, OFFSET(D28, 0, -1)>0), D28,

""))}, to the same avail.

Basically, how do i tell it to ONLY COUNT/AVERAGE a value in column D

(change) if either the 24h column (B) or the 1wk column (C) is not zero (will

never be negative, which is why I don't have <>0 at the end. I tried it with

the not(X=0) function as well, to no avail)?

When I trace the function, all of the true/false variables are

correct--however, I believe it is actually computing an overall true/false

for the entire "if" and then running the function to calculate the average

for every variable in the respective column (e.g., D2:8) instead of

calculating the average for the cell where the two preceding fit my criteria

(e.g., D2,D3,D5,D7,D8). I basically want it to test the if for two cells in

a row (B2, C2), and, if true, then include D2 in the calculation for the

average of column D, when there is a value other than zero for the preceding

columns (which is why I tried the OFFSET function, but I still got the same

end result).

Also, it does not make a different if I change the order of functions in

terms fo the end results I obtain:

=IF(OR(x,y), AVERAGE(z), "") gives me the same value as

=AVERAGE(IF(OR(x,y), z, "")

Any help is GREATLY appreciated as I am about to lose my mind (keep in mind,

i'm actually working with about 1000 rows on my spreadsheet, so it's not

something I can really do by hand, like in this example of only 7 rows)