I have a column with 30-min averages, and I'd link to make a new column with 24-hour averages. I found this formula very useful:
=AVERAGE(INDEX(D:D,1+48*(ROW()-ROW($E$1))):INDEX(D:D,48*(ROW()-ROW($E$1)+1)))
However, about 15% of the rows have a *
indicating data missing. In these instances, I would obviously like Excel not to divide by 48 to calculate the average, but divide by (48 - missing data). So, for example, if 5 cells have missing data within a 48 cell span, the average should be the sum of cell values divided by 43. But currently it is the sum divided by 48.
So how do I account for missing data in the above formula?
Many thanks for your help.
AVERAGE()
ignores non-numeric values. support.office.com/en-us/article/…