Thread: Average row of numbers using different row #'s

Field 1 input: Beginning Date (e.g 7/17/09)
Field 2 input: End Date (e.g. 7/23/09)
Need: Average costs on and between these given dates

From table with this information:

Date Market two
7/16/2009 80
7/17/2009 80
7/20/2009 82
7/21/2009 84
7/22/2009 82
7/23/2009 82
7/24/2009 82
7/27/2009 82

I need Excel to average Market two 7/17 to 7/23 (80, 82, 84, 82, 82) based on the dates I give it.

If I change the date to 7/20 to 7/24, I then need excel to average (82, 84, 82, 82, 82)

How can I get Excel to average based on different dates? Or basically, how do I get it to pull rows between 2 inputs?

Thank you much,

Roger!

Try this:

=SUMPRODUCT(--($A$6:$A$27>=$B$2);--($A$6:$A$27<=$B$3);$B$6:$B$27)/SUMPRODUCT(--($A$6:$A$27>=$B$2);--($A$6:$A$27<=$B$3))

Replace ; with , if needed

Try:

=AVERAGE(IF($A$6:$A$27>=B2,IF($A$6:$A$27<=B3,$B$6:$B$27)))

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER.. you will see { } brackets appear.

If you are indeed using 2007, then use Averageifs...

=AVERAGEIFS($B$6:$B$27,$A$6:$A$27,">="&B2,$A$6:$A$27,"<="&B3)

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save