Hello--i can better explain the problem with an example:

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:B80, 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:B80, C2:C80), 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:B80, C2:C80), D2:D8, ""))}, 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(D2:D8, 0,-2)0, OFFSET(D2:D8, 0, -1)0), D2:D8,
""))}, 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)