Filtering out zeros and blanks

You are looking to use this table in another formula? What is your "wrapping" function for this?

The ultimate goal is to create a rolling 7 day average based on the oil rate column. But I need to filter out the zeros and blanks first and then somehow re-assign the value in days and then spit out the associated rollling 7 day average based on the oil rate

I was just trying to take it one step at a time.

Maybe this will help explain it

This is what is in the table I have

Days Oil Rate
0 1.5
1 3.5
2 0
3 6.0
4 7.1
5 8.2
6
7
8
9 4.2
10 0
11 2.8
12 4.8
13 8.0
14 7.5
15 1.9


I need to Filter Out blanks and Zeros so the days and oil rate columns look like this

Days Oil Rate
0 1.5
1 3.5
2 6.0
3 7.1
4 8.2
5 4.2
6 2.8
7 4.8
8 8.0
9 7.5
10 1.9


and then calculate the Rolling 7 Day Average as a calculated measure I can use in a pivot table


Days Oil Rate 7 Day Average
0 1.5 1.5/1
1 3.5 (1.5+3.5)/2
2 6.0 (1.5+3.5+6.0)/3
3 7.1 (1.5+3.5+6.0+7.1)/4
4 8.2 (1.5+3.5+6.0+7.1+8.2)/5
5 4.2 (1.5+3.5+6.0+7.1+8.2+4.2)/6
6 2.8 (1.5+3.5+6.0+7.1+8.2+4.2+2.8)/7
7 4.8 (3.5+6.0+7.1+8.2+4.2+2.8+4.8)/7
8 8.0 (6.0+7.1+8.2+4.2+2.8+4.8+8.0)/7
9 7.5 (7.1+8.2+4.2+2.8+4.8+8.0+7.5)/7
10 1.9 (8.2+4.2+2.8+4.8+8.0+7.5+1.9)/7


Does that make sense?

Here is the formula I am using that does not take out the zeros and blanks from the calculation or reassign the days values - so I was trying to create a measure that would.

7 Day Average(Daily Oil Rate):=IF(COUNTROWS(VALUES(SourceData[Days])) = 1, CALCULATE(AVERAGEX(VALUES(SourceData[Days]), SourceData[Sum of Oil Rate]),SourceData[Days] <= VALUES(SourceData[Days]) && SourceData[Days]> VALUES(SourceData[Days]) -7),BLANK())

Hmm.. I've used PowerPivot a bit, and that filter function needs to be "wrapped".

Either way, I think i see what you are wanting and you can do it with a worksheet formula. Look at cell D2 in the attachment. You can add dates and values to columns A and B and the average will adjust.

{=AVERAGE(INDEX(B:B,(LARGE(ROW(A:A)*(B:B>0),7))):INDEX(B:B,(LARGE(ROW(A:A)*(B:B>0),1))))} -- This is an array formula, you don't type the curly braces, type the rest and press cntrl+shift+enter