I have a column H of temperature values, and I want to create a new column T, with the average of the column A values with a step of 6. The command I used is this one:
=AVERAGE(OFFSET($H$2;(ROW()-ROW($T$2))*6;;6;))
The problem is that even with one missing value in column H, it returns #value! How can I overcome this and instead of getting an error message to get the average of the rest temperature values?
I tend to avoid volatile functions such as OFFSET, etc in favor of non-volatile INDEX whenever possible so H2:H7 becomes,
INDEX(H:H,(ROW(1:1)-1)*6+2):INDEX(H:H,(ROW(1:1)-1)*6+7)
The AVERAGE requires an array formula to avoid resulting in errors when processing ranges that include errors. AGGREGATE produces the error controlled processing without an array formula.
=AVERAGE(IF(ISNUMBER(INDEX(H:H,(ROW(2:2)-1)*6+2):INDEX(H:H,(ROW(2:2)-1)*6+7)),INDEX(H:H,(ROW(2:2)-1)*6+2):INDEX(H:H,(ROW(2:2)-1)*6+7)))
'alternate
=AGGREGATE(1, 7, INDEX(H:H,(ROW(1:1)-1)*6+2):INDEX(H:H,(ROW(1:1)-1)*6+7))
Finish the INDEX formula with ctrl+shift+enter instead of just enter. AGGREGATE is entered normally.