Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    I need to average monthly costs between varying dates [no fractions of month] for analysis. I would like to use helper cells to insert needed range. I do not know if a Pivot table would work here, but I am not familiar in their construction.
    Column A is dates, column C is cost, "Start date" is H1, "End date" is H2 and calculation [average] is H3.

    Dates are by month, ex) 1-18-2009, 2-18-09, 3-18-2009 etc.
    As example, what is the average cost between 1-18-09 and 5-18-09.

  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
    If the range of interest is from row 2 to row 100 (change to meet your range), you can use the array formula (confirm with strl-shift-enter):
    =AVERAGE(IF(($A$2:$A$100>=$H$1)*($A$2:$A$100<=$H$2 )*ISNUMBER($C$2:$C$100),$C$2:$C$100))

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Thanks Steve. Works good. I never would have figured out that formula.

Posting Permissions

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