Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.links
ChrisM
 
Posts: n/a
Default Averaging Value between Two Serial Dates

Hi all,

I have two spreadsheets. The first spreadsheet contains two columns, one for
start date/time (in serial format) and the second for stop date/time (in
serial format).

The second spreadsheet contains two columns. One containing a serial
date/time and the other containing a meteorological value (eg. temperature).

I want Excel to read the start and stop date/time from the first spreadhseet
and then search the second spreadsheet for all temperature values which were
recorded during this time frame.

Lastly, I want Excel to average these values.

Is this possible using a single Excel function?? Or will I have to use many
nested functions?? Would it be easier to use VBA and if so where would I
start?? Any help would be greatly appreciated. Thanks

Chris


  #2   Report Post  
Posted to microsoft.public.excel.links
Bill Manville
 
Posts: n/a
Default Averaging Value between Two Serial Dates

Ah, so after I spent 30 minutes developing a formula for your previous
data structure you have decided to change the structure. This is much
easier to work with...

Assuming the column of meteo date/time is named MeteoDates and the
readings are named MeteoVal we can use SUMIF to get the sum of the
readings at times after the StartDateTime and subtract those which are
after the StopDateTime:
=(SUMIF(MeteoDates,StartDateTime,MeteoVal)-SUMIF(MeteoDates,StopDateT
ime,MeteoVal))/(COUNTIF(MeteoDates,StartDateTime)-COUNTIF(MeteoDates,
StopDateTime))

By the way, this question would be better suited to the
WorksheetFunctions newsgroup

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Averaging a Value between Two Serial Dates ChrisM New Users to Excel 1 November 16th 05 04:09 AM
Averaging Values Embraced In an Interval Between Two Dates ChrisM Links and Linking in Excel 1 November 16th 05 01:25 AM
Averaging a Value Between Two Serial Dates ChrisM Excel Worksheet Functions 1 November 15th 05 09:45 PM
Calculating number of days between two dates that fall between two other dates [email protected] Excel Discussion (Misc queries) 5 October 26th 05 06:18 PM
Where is DateDiff function in Excel 2002 ? Nigel Welch Excel Worksheet Functions 4 March 4th 05 04:18 PM


All times are GMT +1. The time now is 05:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"