Thread: Help creating function for running average on growing data range

hello,

I'm trying to create a function that will take the average of the last 25% of that data entries in a given range. In my example, I have 30 days of data, cells A1:A30. This data is going to grow on a daily basis so I need a function to be able to automatically adjust. I would like to take the average of the last 25% of this data. Days with blanks do NOT count. Also, taking 25% of numbers will not always give a whole number, so I would just like that to round up to the next whole number. In my example, 25% of 30 is 7.5, round that to 8. So this function would take the average of the last 8 cells that contain data (blank cells do not count). The resulting average would be 91.54 which can go in cell C1

Thanks

Possibly an INDEX formula along the lines of this...

=AVERAGE(INDEX(A:A,MATCH(99999,A:A)-8):INDEX(A:A,MATCH(99999,A:A)))

I was thinking the same kinda thing. However, is it possible to exchange the 8 for some kind of percentage? In my case 25%...

Possibly...

=AVERAGE(INDEX(A:A,MATCH(99999,A:A)-INT(0.25*COUNT(A:A))):INDEX(A:A,MATCH(99999,A:A)))

thanks for the advice guys. Right now what I have is the following: I used a count function in D1 to count the data in column A. In D2 I just multiplied .25 times D1. In D3 i used roundup function of cell D2 to give me a whole number . Then in D4 I used an array formula: =SUM((A1:A100)*(ROW(A1:A100)>LARGE((ROW(A1:A100))*(NOT(ISBLANK(A1:A100))),D3+1)))/D3

If all this is possible in one function, that could be sweet...

Just bumping this thread to see if anyone this morning has any insight. thanks.