10 Day running Average Formula ignore blank/zero

[itzvicc]

10 Day Running Average Formula ignore blank/errors.

I know I could use =average(B10:B19) in cell C19, and in cell C20 =average(B11:B20).
The challenge becomes later in the column of numbers B10:B50 when we have a blank.

So if in cell B30 it is blank that is ok. In cell C31 it would say average last ten in column B and ignore the blank cell.


Column A B C
Information Date Amount Average
1/1/09 5.00
1/2/09 5.00
1/3/09 9.00
1/4/09 3.00
1/5/09 4.00
1/6/09
1/7/09 9.00
1/8/09 3.00
1/9/09 2.00
1/10/09 8.00
1/11/09 9.00 AVG(9,8,2,3,9,4,3,9,5,5) not Avg(9,8,2,3,9,0,4,3,9,5)


This woudl be rolling as the month goes on.

 

[itzvicc]

I did forget to mention I am not very good at VBA.

 

[Aladin Akyurek]

I did forget to mention I am not very good at VBA.

Let A1:B17 house the data, headers included...

<TABLE style="WIDTH: 185pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=247 x:str><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=119>Information Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64>Amount</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64>Average</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39814">1/1/2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(COUNT($B$2:B2)>=10,AVERAGE(INDEX($B$2:B2,LARGE(IF($B$2:B2<>"",ROW($B$2:B2)-ROW($B$2)+1),10)):B2),"")' x:arrayrange="C2"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39815">1/2/2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(COUNT($B$2:B3)>=10,AVERAGE(INDEX($B$2:B3,LARGE(IF($B$2:B3<>"",ROW($B$2:B3)-ROW($B$2)+1),10)):B3),"")' x:arrayrange="C3"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39816">1/3/2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(COUNT($B$2:B4)>=10,AVERAGE(INDEX($B$2:B4,LARGE(IF($B$2:B4<>"",ROW($B$2:B4)-ROW($B$2)+1),10)):B4),"")' x:arrayrange="C4"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39817">1/4/2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(COUNT($B$2:B5)>=10,AVERAGE(INDEX($B$2:B5,LARGE(IF($B$2:B5<>"",ROW($B$2:B5)-ROW($B$2)+1),10)):B5),"")' x:arrayrange="C5"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39818">1/5/2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(COUNT($B$2:B6)>=10,AVERAGE(INDEX($B$2:B6,LARGE(IF($B$2:B6<>"",ROW($B$2:B6)-ROW($B$2)+1),10)):B6),"")' x:arrayrange="C6"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39819">1/6/2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(COUNT($B$2:B7)>=10,AVERAGE(INDEX($B$2:B7,LARGE(IF($B$2:B7<>"",ROW($B$2:B7)-ROW($B$2)+1),10)):B7),"")' x:arrayrange="C7"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39820">1/7/2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(COUNT($B$2:B8)>=10,AVERAGE(INDEX($B$2:B8,LARGE(IF($B$2:B8<>"",ROW($B$2:B8)-ROW($B$2)+1),10)):B8),"")' x:arrayrange="C8"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39821">1/8/2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(COUNT($B$2:B9)>=10,AVERAGE(INDEX($B$2:B9,LARGE(IF($B$2:B9<>"",ROW($B$2:B9)-ROW($B$2)+1),10)):B9),"")' x:arrayrange="C9"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39822">1/9/2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(COUNT($B$2:B10)>=10,AVERAGE(INDEX($B$2:B10,LARGE(IF($B$2:B10<>"",ROW($B$2:B10)-ROW($B$2)+1),10)):B10),"")' x:arrayrange="C10"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39823">1/10/2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(COUNT($B$2:B11)>=10,AVERAGE(INDEX($B$2:B11,LARGE(IF($B$2:B11<>"",ROW($B$2:B11)-ROW($B$2)+1),10)):B11),"")' x:arrayrange="C11"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39824">1/11/2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2154733 class=xl22 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(COUNT($B$2:B12)>=10,AVERAGE(INDEX($B$2:B12,LARGE(IF($B$2:B12<>"",ROW($B$2:B12)-ROW($B$2)+1),10)):B12),"")' x:arrayrange="C12">5.7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39825">1/12/2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(COUNT($B$2:B13)>=10,AVERAGE(INDEX($B$2:B13,LARGE(IF($B$2:B13<>"",ROW($B$2:B13)-ROW($B$2)+1),10)):B13),"")' x:arrayrange="C13">5.7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39826">1/13/2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(COUNT($B$2:B14)>=10,AVERAGE(INDEX($B$2:B14,LARGE(IF($B$2:B14<>"",ROW($B$2:B14)-ROW($B$2)+1),10)):B14),"")' x:arrayrange="C14">5.9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39827">1/14/2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(COUNT($B$2:B15)>=10,AVERAGE(INDEX($B$2:B15,LARGE(IF($B$2:B15<>"",ROW($B$2:B15)-ROW($B$2)+1),10)):B15),"")' x:arrayrange="C15">5.9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39828">1/15/2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(COUNT($B$2:B16)>=10,AVERAGE(INDEX($B$2:B16,LARGE(IF($B$2:B16<>"",ROW($B$2:B16)-ROW($B$2)+1),10)):B16),"")' x:arrayrange="C16">5.8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39829">1/16/2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(COUNT($B$2:B17)>=10,AVERAGE(INDEX($B$2:B17,LARGE(IF($B$2:B17<>"",ROW($B$2:B17)-ROW($B$2)+1),10)):B17),"")' x:arrayrange="C17">5.8</TD></TR></TBODY></TABLE>

C2:

Control+shift+enter, not just enter...

=IF(COUNT($B$2:B2)>=10,AVERAGE(INDEX($B$2:B2,
   LARGE(IF($B$2:B2<>"",ROW($B$2:B2)-ROW($B$2)+1),10)):B2),"")

and copy down.

 

[itzvicc]

If you have a second I think I did not give enough data, or I messed up the formula.

I am sad to say either I did something wrong or it did not work.
Row 11 is the first one with data. The last row with Data is 86.



(B) (C) (D) (E)

Date Day of Ending 10 Day Cash
Week Cash Average
9/18/2008 Thursday $50,000
9/19/2008 Friday $50,000
9/22/2008 Monday $350,000
9/23/2008 Tuesday $50,000
9/24/2008 Wednesday $50,000
9/25/2008 Thursday $200,000
9/26/2008 Friday $50,000
9/29/2008 Monday $50,000
9/30/2008 Tuesday $50,000
10/1/2008 Wednes $50,000
10/2/2008 Thursday $150,000
10/3/2008 Friday $150,000
10/6/2008 Monday $150,000
10/7/2008 Tuesday $150,000
10/8/2008 Wednes $150,000
10/9/2008 Thursday $150,000
10/10/2008 Friday $105,000
10/13/2008 Monday $100,000
10/14/2008 Tuesday $95,000
10/15/2008 Wednes $90,000
10/16/2008 Thursday $75,000
10/17/2008 Friday $60,000


Thanks for your help. I appreciate your help in advance.

 

[Aladin Akyurek]

If the first data record is in B11:D11, then:

E11:

Control+shift+enter, not just enter...

=IF(COUNT($D$11:D11)>=10,AVERAGE(INDEX($D$11:D11,
    LARGE(IF($D$11:D11<>"",ROW($D$11:D11)-ROW($D$11)+1),10)):D11),"")

and copy down.

 

[itzvicc]

Ok I hate to keep asking questions and not seem like I am getting this.... But I can not seem to get it to work. Maybe I need to restate what I am attempting to do.

Column D is where I input may daily ending cash value. Based on today and the previous 9 days I need to determine what my prior 10 day average cash on hand is.

So in Column D9:D86 (for testing D9:D20 =$10,000, $20,000….to $100,000 in multiples of 10,000) is 10 days worth of cash on hand. If you do a formula in E20 of: =sum(D9:D20)/10=$55,000. If I put in your formula and obtain an blank.vaule return which would be great in place of an error sign, So I thought maybe I should change the ending value $D$11:D86 where the D row ends. After changing this it did return a value of 60,000 which is not correct either. So now I am clueless. I have not nested this many formulas together previously.

Now having said this I also have not previously used the large formula. Additionally I have never had the opportunity to use a formula that is written with $D$11:D11. From what I have learned/understood the $D$11 is a Permanente cell reference, but not sure why the finial numbering your range of cells is D11 (same cell, just not Permanente).

Your Formula I used if correct:

=IF(COUNT($D$11:D11)>=10,AVERAGE(INDEX($D$11:D11,
LARGE(IF($D$11:D11<>"",ROW($D$11:D11)-ROW($D$11)+1),10)):D11),"") **finished off with a Ctrl+shift+Enter to obtain the {} brackets.

I hope this helps and if you wish I can send you a copy of the worksheet so you can actually see it if you wish. My email address is itzvicc@yahoo.com if you would like to send me a msg to respond with the attachment.

Thanks againf or all of your help.

 

[Aladin Akyurek]

...
Column D is where I input may daily ending cash value. Based on today and the previous 9 days I need to determine what my prior 10 day average cash on hand is.

So in Column D9:D86 (for testing D9:D20 =$10,000, $20,000….to $100,000 in multiples of 10,000) is 10 days worth of cash on hand. If you do a formula in E20 of: =sum(D9:D20)/10=$55,000.
...

If the data starts in D9...

E9:

Control+shift+enter, not just enter...

=IF(COUNT($D$9:D9)>=10,AVERAGE(INDEX($D$9:D9,
    LARGE(IF($D$9:D9<>"",ROW($D$9:D9)-ROW($D$9)+1),10)):D9),"")

and copy down.

 

[itzvicc]

10 Day running Average Formula ignore blank/zero, 10 Day Average

YOU ROCK!!!!!!!!!!!!!!!!!!!!

So basically it is not me giving a critical detail of the information starting on Cell D#, but the E# is the first it would be able to give the 10 Day Average.

WOW

YOU ROCK YOU ROCK YOU ROCK!!!!!!!!!!!!!!!!!!!!!!!

I appologize for the going back in forth.
ATT EVERYONE it works like a charm

=IF(COUNT($D$9:D18)>=10,AVERAGE(INDEX($D$9:D18,LARGE(IF($D$9:D18<>"",ROW($D$9:D18)-ROW($D$9)+1),10)):D18),"")
$D$9 is where the data starts, D18 is end of data to review.


THANK YOU AGAIN