Need help with average/lookup formula
 

Hi everyone,

Hope someone can help me with a formula. 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.

I have attached a small sample file of my data.

Hope someone can help, let me know if I dont make any sense - really 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")

Need help with average/lookup formula
 

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,Sh eet2!$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$120 0,"="&H6,Sheet2!$A$2:$A$1200,"<="&H7)
or
=AVERAGE(IF((Sheet2!$A$2:$A$1200=H6)-(Sheet2!$A$2:$A$1200H7),Sheet2!$I$2:$I$1200))
The formula above is an array formula. Enter with CTRL+Shift+Enter


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2