Share via

Excel - Average The last 10 values on a column, on a specific range of rows, from top to bottom while adding new values, ignoring the zeros

Anonymous
2017-05-29T23:29:22+00:00

I want to average the last 10 values in a column. I have 53 rows that will have data entered but not all at once. The data is entered starting in the lats row. I want the average result in the title cell at the top of the column.

This is what I have. I want the average of the last 10 values in column "C" the range is C57 (start point) to C5 (end point) the result I want it in C4.

thanks

***Post moved / split by the moderator to the appropriate forum category / another thread

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2017-06-17T07:42:00+00:00

    Hi JCYW,

    The #DIV/0! you have met is because the is no data in column E, so when you do the calculate, the divisor is zero. You can insert =IFERROR(C9/E9,0) to display 0 in column F if column E is empty.

    For more information, you can refer to this article: How to correct a #DIV/0! error.

    Regards,

    Tisky

    Was this answer helpful?

    0 comments

Answer accepted by question author

  1. Anonymous
    2017-06-01T06:19:54+00:00

    Refer below image:

    Enter below formula as an array formula (CTRL+SHIFT+ENTER) in cell C4 - this formula will ignore zeros (ie. zeros in cell C5 to C8) and calculate average of the most recent 10 values excluding zeros (note: any inbetween zeros will also be ignored):

    =SUM(INDEX($C$5:$C$57,SMALL(INDEX(IF($C$5:$C$57<>"",IF($C$5:$C$57<>0,ROW($C$5:$C$57)-ROW($C$5)+1)),0),10)):$C$5)/10

    Alternate - enter below formula as an array formula (CTRL+SHIFT+ENTER) in cell D4 - this formula will include all zeros (ie. zeros in cell C5 to C8) while calculating average - this would give similar results to Ashish's formula:

    =AVERAGE(INDEX($C$5:$C$57,SMALL(INDEX(IF($C$5:$C$57<>"",ROW($C$5:$C$57)-ROW($C$5)+1),0),10)):$C$5)

    Regards,

    Amit Tandon

    www.globaliconnect.com

    Was this answer helpful?

    0 comments

15 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-05-31T17:05:12+00:00

    I have Heading in cell C3

    The average is computed in cell C4 with

    =AVERAGE(INDIRECT(ADDRESS(COUNT(C5:C1000)+4-9,COLUMN(C1))&":"&ADDRESS(COUNT(C5:C1000)+4,COLUMN(C1))))

    If your heading is in C19 chance 4 to 19, and change C5 to appropriate value

    Note my tested range in C5:C1000 but this can be changed as needed

    best wishes

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2017-05-31T02:50:30+00:00

    Hi JCYW,

    Have you tried Ashish's suggestion?

    As tested from my side, the formula can work as the following picture shows:

    Regards,

    Joanne

    Was this answer helpful?

    0 comments
  3. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2017-05-29T23:41:03+00:00

    Hi,

    Try this formula in cell C4

    =AVERAGE(C57:C48)

    The assumption is that there will be no blank cells in range C57:C48 else for averaging 10 values, the range will have to be increased dynamically.

    Was this answer helpful?

    0 comments