Thread: average monthly cost between dates

1. 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. 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. 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
•