Need help with average/lookup formula

[TTTNT]

Hi everyone,

Hope someone can help me with a formula. I am looking for a formula t
put into sheet 1 that will average column I in sheet2 (averaging onl
numbers that are 10 or below) that appear between two dates (in sheet1
month start/end dates, weekly mon/sun dates), I have tried severa
different types of formulas I have searched for online without an
luck.

I have attached a small sample file of my data.

Hope someone can help, let me know if I dont make any sense - reall
sorry in advance this is my first time on these forum

+-------------------------------------------------------------------
|Filename: Sample Data.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=901
+-------------------------------------------------------------------

 

[shanermuls]

Hi everyone,

Hope someone can help me with a formula. I am looking for a formula t
put into sheet 1 that will average column I in sheet2 (averaging onl
numbers that are 10 or below) that appear between two dates (in sheet1
month start/end dates, weekly mon/sun dates), I have tried severa
different types of formulas I have searched for online without an
luck.

I have attached a small sample file of my data.

Hope someone can help, let me know if I dont make any sense - reall
sorry in advance this is my first time on these forums

Paste following to cellB7

Then drag across

=+AVERAGEIFS(Sheet2!$I:$I, Sheet2!$H:$H
">="&LEFT(Sheet1!F$6,5)&"/2013", Sheet2!$I:$I,"<10",Sheet2!$H:$H
"<="&RIGHT(Sheet1!F$6,5)&"/2013"

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Claus Busch]

Hi,

Am Tue, 20 Aug 2013 05:03:35 +0100 schrieb TTTNT:

I am looking for a formula to
put into sheet 1 that will average column I in sheet2 (averaging only
numbers that are 10 or below) that appear between two dates (in sheet1 -
month start/end dates, weekly mon/sun dates), I have tried several
different types of formulas I have searched for online without any
luck.

in B2 try:
=AVERAGE(IF(TEXT(Sheet2!$A$2:$A$1200,"MMMM")=B1,Sheet2!$I$2:$I$1200))
and enter the array formula with CTRL+Shift+Enter and drag to F2
in B7 try:
=AVERAGEIFS(Sheet2!$I$2:$I$1200,Sheet2!$A$2:$A$1200,">="&H6,Sheet2!$A$2:$A$1200,"<="&H7)
or
=AVERAGE(IF((Sheet2!$A$2:$A$1200>=H6)-(Sheet2!$A$2:$A$1200>H7),Sheet2!$I$2:$I$1200))
The formula above is an array formula. Enter with CTRL+Shift+Enter


Regards
Claus B.