Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Prince George, Br. Columbia, Canada
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Average (Excel 97 SR2)

    I have a table of price history for every day from Nov. 97 to current. Column A has the mm,dd,yy and there are 4 other columns with pricing information.

    What I would like to do is get a monthly average price from the table. Something like =If(A:A=<11/30/97 and =>11/01/97) Average(the cooresponding cells in C:C) Can Some one suggest a formula. I seem to be choking on the way I enter the dates.

    Thanks

    Larry

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Average (Excel 97 SR2)

    This ARRAY formula should work (confirm with ctrl-shift-enter):

    =AVERAGE(IF((A1:A5000<=DATE(1997,11,30))*(A1:A5000 >=DATE(1997,11,1)),C1:C5000))

    Change the length of the range accordingly. You can NOT use the whole column (A:A) though you CAN use A1:A65535 if you want all but the last one or A2:A65536 if you don't want the first one.

    Steve

Posting Permissions

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