+ Reply to Thread
Results 1 to 12 of 12

Average of last 3 cells in a range, dynamic

  1. 03-23-2012, 05:09 AM #1
    Armitage2k
    Armitage2k is offline
    Forum Contributor
    Join Date
    03-24-2009
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    382

    Average of last 3 cells in a range, dynamic

    Hi,

    I have yearly summary sheet with 12 cells, each holding the score for one month. Now I need to calculate the 3 months rolling score for the LATEST 3 months of which I have scores. Now, since I am constantly updating this form, and add the scores for new months, the 3 months rolling formula needs to be manually adjusted as well. Is there any way to let excel ignore cells in a range with "0" as a value, and instead only calculate the average of the latest 3 cells with value >0 ?

    Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 3MR (Mar to May)
    10 20 30 10 20 0 0 0 0 0 0 0 20


    Hope this makes it clear.
    Thanks,

    A2k
    Last edited by Paul; 03-23-2012 at 05:24 PM.
    Reply With QuoteRegister To Reply
  2. 03-23-2012, 05:44 AM #2
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Average of last 3 cells in a range, dynamic

    Hi,

    a first possibility

    Please Login or Register  to view this content.
    to be confirmed as array formula with control+shift+enter.

    There are for sure more elegant solutions.

    Regards
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.
    Reply With QuoteRegister To Reply
  3. 03-23-2012, 06:01 AM #3
    Armitage2k
    Armitage2k is offline
    Forum Contributor
    Join Date
    03-24-2009
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    382

    Re: Average of last 3 cells in a range, dynamic

    Thanks mate, but you are killing me with this formula

    i found something simpler, might help out others:
    Please Login or Register  to view this content.
    the only problem i have is that it gives me #DIV errror if one of the cells has a Zero in it... one step in the right direction

    Thanks,
    A2k
    Reply With QuoteRegister To Reply
  4. 03-23-2012, 06:16 AM #4
    WHER
    WHER is offline
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Average of last 3 cells in a range, dynamic

    One more possibility, see attachment.
    Attached Files Attached Files
    Last edited by WHER; 03-23-2012 at 03:27 PM.
    Reply With QuoteRegister To Reply
  5. 03-23-2012, 07:51 AM #5
    Armitage2k
    Armitage2k is offline
    Forum Contributor
    Join Date
    03-24-2009
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    382

    Re: Average of last 3 cells in a range, dynamic

    thanks wher, this is getting me closer.

    how about a solution for a 12months rolling average? just changing the formula from -3 to -12 doesnt seem to cut it...
    respectively, how could i adjust my previous posted formula to not count the 0 cells?

    thank you,
    A2k
    Reply With QuoteRegister To Reply
  6. 03-23-2012, 08:01 AM #6
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Average of last 3 cells in a range, dynamic

    Hi

    May i suggest you a helper column and everything will be fine?!

    In the helper(and hidden) column(example,A), in A1=LARGE(G8:R8;1)

    A2=LARGE(G8:R8;2)

    A3=(LARGE(G8:R8;3)

    Then, result is =average(a1:a3)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  7. 03-23-2012, 03:31 PM #7
    WHER
    WHER is offline
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Average of last 3 cells in a range, dynamic

    For 12 months (or any other number) see modified attachment in post 4.
    Use the spinbutton to choose the number of months (in E1);
    The yellow highlight is only for visualising the dynamic range, it's not necessary for the formula.
    Reply With QuoteRegister To Reply
  8. 03-23-2012, 04:18 PM #8
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Average of last 3 cells in a range, dynamic

    Assuming all values are +0 until zeroes start then this formula will average the last 3 non-zero values

    =AVERAGE(OFFSET(G8,0,MAX(0,COUNTIF(G8:R8,">0")-3),,MIN(3,COUNTIF(G8:R8,">0"))))

    if there are less than 3 it just averages those
    Audere est facere
    Reply With QuoteRegister To Reply
  9. 03-25-2012, 11:30 AM #9
    Armitage2k
    Armitage2k is offline
    Forum Contributor
    Join Date
    03-24-2009
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    382

    Re: Average of last 3 cells in a range, dynamic

    Hi,

    thanks for all the solutions, works almost the way i want now. The only issue i have is that the formula doesnt seem to be compatible with percentages. I am getting either a REF or N/A error when calculating such figures.
    Copy of Xl0000000.xls
    attached is the workbook from post #4 with my modified cells. would love is someone could take a look.

    Cheers,
    A2k
    Reply With QuoteRegister To Reply
  10. 03-25-2012, 12:00 PM #10
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Average of last 3 cells in a range, dynamic

    My 2ps worth array entered
    =(SUM(INDEX(A1:AZ1,LARGE(IF(INDEX(A1:AZ1>0,0),COLUMN(A1:AZ1),0),1)),INDEX(A1:AZ1,LARGE(IF(INDEX(A1:AZ1>0,0),COLUMN(A1:AZ1),0),2)),INDEX(A1:AZ1,LARGE(IF(INDEX(A1:AZ1>0,0),COLUMN(A1:AZ1),0),3))))/3

    opps its average amended it now
    Last edited by martindwilson; 03-25-2012 at 03:06 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  11. 03-25-2012, 12:03 PM #11
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,481

    Re: Average of last 3 cells in a range, dynamic

    Try to replace 2 in:
    ,,LOOKUP(2,1/G8:AD8
    with bigger number:
    ,,LOOKUP(10^10,1/G8:AD8
    Quang PT
    Reply With QuoteRegister To Reply
  12. 03-25-2012, 02:48 PM #12
    WHER
    WHER is offline
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Average of last 3 cells in a range, dynamic

    1: if you're going to fill in numbers smaller than 1 (e.g. 33%), use a bigger number in the formula instead of 2 (as bebo suggests), maybe 9.999999999E+307

    2: in the example in post 9 you fill in two numbers (other than 0) but leave cell E1 set to 12. So you're trying to average the twelve last numbers of two. You can only have "the twelve last numbers" if you have 12 or more filled in (other than 0)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1