+ Reply to Thread
Results 1 to 6 of 6

Shifting an AVERAGE formula down automatically

  1. #1
    Registered User
    Join Date
    06-20-2011
    Location
    Tennessee, USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Shifting an AVERAGE formula down automatically

    Long-time reader, first-time poster. Thanks in advance for any help.

    Short version: I have a column of data that goes down hundreds of rows. This data is formula driven such that the first X number of rows could be zero depending on other inputs.

    What I need to do is have another column of data that takes the average of every 12 entries in the first column but must always exclude any cells with zero.

    In other words, if A1:A24 is all non-zero numbers, then I need B1:B12 to equal AVERAGE(A1:A12) and B13:B24 to equal AVERAGE A13:A24.

    But, if A1:A6 equals zero, then B1:B6 must also equal zero and B7:B18 equaling AVERAGE A7:A18.

    I need a formula I can put in one cell and copy down such that column B will always start averaging every 12 numbers once it encounters the first non-zero number in Column A.

    Thanks in advance!
    Last edited by Iron Mike; 06-21-2011 at 12:10 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Shifting an AVERAGE formula down automatically

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    06-20-2011
    Location
    Tennessee, USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Shifting an AVERAGE formula down automatically

    My apologies - I didn't see that I could manage attachments.

    I've attached a worksheet giving a good idea of what I need. More background: I'm a banker (don't hate me) and work on structuring interest rate swaps. As you can see from the attached worksheet, what I need is a formula to go in Column C/I that will work for all cells in the spreadsheet.

    We do an "average amortization" to determine reductions in notional principal balance over a 12 month period that the swap is in effect. This "average amortization" is derived from the actual amortization shown in Columns B/H. The key is a period of interest only that can change. When it changes, the actual amortization columns will change and the average formula must change with it.

    I consider myself pretty good at this stuff, but this has stumped me big time.
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Shifting an AVERAGE formula down automatically

    Try in E15:

    Please Login or Register  to view this content.
    copied down.

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Shifting an AVERAGE formula down automatically

    bit of trouble downloading at moment
    but with values in col a then in b1 put
    =CEILING((ROW()-COUNTIF($A$1:A1,0))/12,1)
    in c1 put
    =SUMIF(B:B,B1,A:A)/COUNTIF(B:B,B1)
    "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

  6. #6
    Registered User
    Join Date
    06-20-2011
    Location
    Tennessee, USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Shifting an AVERAGE formula down automatically

    Quote Originally Posted by NBVC View Post
    Try in E15:

    Please Login or Register  to view this content.
    copied down.
    NBVC, that is baller. I won't admit to understanding the syntax those functions are making, but they work. You are the man and I thank you kindly.

    Of course, getting one section causes others to now reveal their own host of problems, but I'm working through these.

    Thanks again!

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1