Problem with Criteria using AVERAGEIFS

[rotomarty]

I have software that captures weights of product running across an in-motion scale. The data includes a time/date stamp for each line. I am trying to average the weights per hour (25 per minute). I have exported the data to Excel and created a table with hourly numbers that represent dates at a specific time. i.e. 8/12/11 19:00 as 40767.7916676504 in A20 and 8/12/11 19:59 as 40767.8332675505 in B20. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The formula=AVERAGEIFS(Sheet1!$F$2:$F$11530,Sheet1!$E$2:$E$11530,">=A20",Sheet1!$E$2:$E$11530,"<=B20") results in #DIV/0!
<o></o>
The formula <o></o>
=AVERAGEIFS(Sheet1!$F$2:$F$11530,Sheet1!$E$2:$E$11530,">=40767.7916676504",Sheet1!$E$2:$E$11530,"<=40767.8332675505") results in 68.08787<o></o>

Is there a way to use the cell value and avoid typing in every date number? I am not good at VBA so would prefer using the formula bar if possible. Thanks!<o></o>

 

[T. Valko]

I have software that captures weights of product running across an in-motion scale. The data includes a time/date stamp for each line. I am trying to average the weights per hour (25 per minute). I have exported the data to Excel and created a table with hourly numbers that represent dates at a specific time. i.e. 8/12/11 19:00 as 40767.7916676504 in A20 and 8/12/11 19:59 as 40767.8332675505 in B20. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The formula=AVERAGEIFS(Sheet1!$F$2:$F$11530,Sheet1!$E$2:$E$11530,">=A20",Sheet1!$E$2:$E$11530,"<=B20") results in #DIV/0!
<o></o>
The formula <o></o>
=AVERAGEIFS(Sheet1!$F$2:$F$11530,Sheet1!$E$2:$E$11530,">=40767.7916676504",Sheet1!$E$2:$E$11530,"<=40767.8332675505") results in 68.08787<o></o>

Is there a way to use the cell value and avoid typing in every date number? I am not good at VBA so would prefer using the formula bar if possible. Thanks!<o></o>

Try it like this...

=AVERAGEIFS(Sheet1!$F$2:$F$11530,Sheet1!$E$2:$E$11530,">="&A20,Sheet1!$E$2:$E$11530,"<="&B20)

 

[rotomarty]

Sweet! Thank you so much! That works perfect.

 

[T. Valko]

Sweet! Thank you so much! That works perfect.

You're welcome. Thanks for the feedback!