1

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.

4
  • 1
    Average is supposed to deal with missing values automatically. If you just make them blank versus having a * it will do what you expect. That is the simplest approach by far.
    – Eddie Dunn
    Dec 11, 2015 at 15:34
  • Your formula already accounts for your missing values because AVERAGE() ignores non-numeric values. support.office.com/en-us/article/…
    – Kyle
    Dec 11, 2015 at 16:48
  • Yes, I did an extra calculation and realised that you are right Kyle. Well, all this drama for nothing. Thank you very much for your replies!
    – Ulla
    Dec 12, 2015 at 12:32
  • @Ulla Since you figured it out, you should post that as an answer and accept it OR vote to close your own question. Jan 27, 2016 at 14:35

0

You must log in to answer this question.

Browse other questions tagged .