averaging a 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


0
11/15/2005 8:03:52 PM
excel 39879 articles. 2 followers. Follow

1 Replies
598 Views

Similar Articles

[PageSpeed] 28

See response in .programming

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"ChrisM" <Christopher.Meaney@ec.gc.ca> wrote in message
news:ufFi0%23h6FHA.4012@TK2MSFTNGP14.phx.gbl...
> 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
>
>


0
bob.phillips1 (6510)
11/15/2005 9:58:42 PM
Reply:

Similar Artilces:

HDD Serial Number.
I want to find out the HDD Serial Number in my C++ Code. There seems to be a way using the WMI. But I donot (cannot) use it. I want to know if there is some C++, C or assembly code that can help me do this. Any help/suggestions would be very helpful and highly appreciated. Anwar. You can do this with GetVolumeInformation() Fariborz "Anwar Khan" wrote: > I want to find out the HDD Serial Number in my C++ Code. > There seems to be a way using the WMI. But I donot (cannot) use it. > > I want to know if there is some C++, C or assembly code that can help me do &g...

Earned Value Report in Project 2007
I'm using MS Project 2007, have input all my tasks, costs, set baseline so all my numbers are populating great. I'm using the visual report to generate the earned value over time report (the pivot table and chart). Unfortunately while I am really good at most things Excel, pivot tables is not one of them. Project is populating the table with Quarters 1, 2 and 3. I only need Quarter 1 for my report. Is there a way to hide or delete the other two quarters from the table? Thanks for the help! Teri Hi Teri, I am guessing that you have your "Time Weekly Calendar&q...

Can I exclude the criteria Value from the query results?
I am using the [Dupe_Joiner_Title_ID] to match a list of records from a duplicates table against an ID form field, but want to exclude the actual value that the query uses as the criteria from being seen in the continous form that is linked to the query, but shows all other matching records for the form's current record. This is the criteria code: [Forms]![frm_Runs]![frm_Street_Joiner_Main].[Form]![frm_Street_Joiner_Sub].[Form]![Joiner_Title_ID] that is in the 'Dupe_Joiner_Title_ID: Joiner_Title_ID' column. This is my SQL: SELECT Tbl_Street_Joiner_Dupes.Address, Tbl_Stree...

Using SUMIFS with date range
I am trying to sum a column of cells if several criteria are met...1. equal to specific customer name, 2. is greater than or equal to a beginning date entered into a specific cell and 3. is less than or equal to an end date entered into another specific cell. I can get the formula to work if I use the beginning and end dates in the SUMIFS formula, but I don't want to change the formula each time I run the spread. So I want to be able to data enter the begin and end dates in certain cells (line in B1 and B2) so I only change those dates to recalculate the totals. Somethin...

Changing line appearance for future values
Using Excel 2007. Is it possible to change the appearance of a line in a chart for values in the future (like projections)? I would like to go from solid to dashed and keep the same color and weight. Either you format line pieces individually (manual work). Either you use 2 series to plot in the chart. Use IF functions to see on which series a given value should be shown. Use NA() for values that should not be plotted. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "RhysPieces" wrote: > Using Excel 2007. Is it possible to change the appearance of a line...

Conditional formating based on another cell value
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I've managed to use the solution described below, but I haven't been able to figure out how to use this for formating a cell based on a date entered into another cell. <br><br>Example: When 1/15/10 is entered into AG4, P4 is highlighted green. <br><br>Any help would be appreciated. <br> ----- <br> &quot;On 6/30/08 7:54 PM, in article 59b52d02.-1@webcrossing.caR9absDaxw, <br> &quot;Simon@officeformac.com&quot; wrote:http://www.officeformac.com/macimages/btn_publish.gif &l...

date format 01-02-10
How can I set a date format for any cell and user has to use that format otherwise user is not allowed to enter any date. Hi you can only do it with code. The following is some event code which would ensure that only dates can be entered in column A, strating from row 2. Adjust to suit your nededs. also, date format has been set as dd mmm yyyy e.g. 02 Jan 2010, again change to suit the format you want. Private Sub Worksheet_Change(ByVal Target As Range) Dim edate As Date If Target.Row < 2 Then Exit Sub ' change row number from 2 if required If Target.Co...

two different email accounts in outlook
How do you store two different email accounts in two different places in outlook? I have two accounts, one on an exchange server and another just a local imap account. I want to store the mail in two different places. How do I do this?? In the control panel click on the mail icon. Click the bottom button that says "show profiles". Add another profile and setup your IMAP email account. Make sure you click the "Prompt me for which account to use" bullet. Now you will have separate accounts for each email address. ...

Date "yyyymm"
I have a worksheet were the dates are displayed as 200901 (yyyymm --- no slash) in column A. I need to add a formula to cells in colum C that will only substract any given number of months from this date, such as 200901 minus 13 months will equal 200811. Now, on the same boat, I would like to show the new date displayed as 11/01/2008. Thank you On Tue, 15 Dec 2009 06:31:02 -0800, Memphis <memphisland@bogusaddress.com> wrote: >I have a worksheet were the dates are displayed as 200901 (yyyymm --- no >slash) in column A. >I need to add a formula to cells in c...

Pay Period From/To dates on Payroll check voids
When a payroll transaction is entered or a build is processed, pay period from and to dates are entered. These dates are stored as 'trxbegdt' and' trxenddt' on UPR30300. However, when a check is voided, the 'trxbegdt' and trxenddt' on the UPR30300 table holds the actual check void date and not the original pay period from and to dates. SmartList Builder, Crystal Reports and Excel data query tools can be used to pull data based on the pay period date ranges for performance based reporting, but voids cause inconsistencies in the analysis since the dates are ...

DIsplaying negative time values
Hi, I have two columns of time values. I need to determine the difference between the two values and return either a positive or negative time value. The table below is in hours and minutes Time A Time B 09:00:00 09:00:00 09:00:00 09:03:00 09:00:00 08:58:00 In the above example First row should be "00:00:00", Second row should be "00:03:00", and Third row should be "-00:02:00" When subtracting Time B from Time A for the first two rows i get the correct value, but the third row returns as "##########...

Styles and Set Numbering Value problem
Running MS Office Pro 2007 with Windows XP Pro. I have styles defined for numbered lists. These styles have simple characteristics, like indent, space before, etc. - nothing special. I want the numbered lists to appear throughout the document, with each starting at the number 1. When I try to start a new list using the Set Numbering Value dialog, I get a message saying "Changing the number format for this list updates the style "NumList" and reapplies the style to each paragraph. Do you want to continue?" If I select no, the starting number is not chan...

Date query 01-21-10
I have a table which contains a field called "DUEDATE", data type is DATE/TIME, formatted as LONGDATE. I use it to enter in dates when projects are due. How can I write a query which returns "DUEDATE" items for the current date. Example: Today is January 21, 2010 and I want to retrieve everything from the table with "DUEDATE" of january 21,2010. If you only store the date part and only want to return records where the DueDate is the current date, set the criteria under DueDate to: =Date() Duane Hookom MS Access MVP "Iago" <Iago@d...

Function to format a value into percent with no decimal
I have about 50 rows of data, each with 2 columns. The first column (A) holds a number; the format is General. The second column (B) divides the number in A over 51, using this formula =A1/51 (and =A2/51, =A3/51, etc.). The format for column B is Percentage with 0 decimal places, so the results look like 25%, 4%, etc. I'd like to create a third column (C) which concatenates the results of columns A & B to yield something like: 13 (25%) But instead I get: 13 (0.254901960784314) The function I'm using is: =B1 & " (" & C1 & ")" I'm thinking I...

How to suppress zero value check checks and remittance
Any one knows how to avoid printing the zero value remitances during the checks run in PM. GP7.5 Thanks in Advance. ...

Trying to select a specific range based on the time value of user form input
Here is the cmdOK_Click event of my user form. What I need it to do is based on the time input in the pckApptTime on my form to have it offset to a different range of cells (ie: if time is 7:15, then the active cell offset is range A1, looping for a blank cell. If the time is 8:30 am then the range begins at a10, 9:45 range a20, 1:15 range a30, 2:30 range a40, and 3:45 range a50) Where in the Sub do I put this information? and what do I put to have the OK_Click do this automatically? Private Sub cmdOK_Click() Range("A1").Select ActiveWorkbook.Save (this is to update the ...

Task with overdue dates print in smaller fonts?
In Outlook 2003, if I have a task with an overdue due date, it appears on screen just like all the other Tasks ... but when I print out my Tasks, it shows up in a smaller font. If I delete the due date, it prints in the same size font as everything else. Where do I set my preference for all tasks to be printed the same regardless of whether they have due dates or not? ...

Transferring option group value
I'm trying to create a form for adding new record only. How to do that ?. In that from,I want to put an option button ( group ) to shown or hide some text box ( control ) in my forms.But I also want to transferr the option group value to a field once the form close or the Save_cmd is clicked. Then in another qury I need to join the value transferred from the option group with another table so that the data appear as a text in another table. Please advice how to do that since I have no idea with coding? shiro wrote: >I'm trying to create a form for adding new record only. >...

Maximum value
Hello, I have a table in the following format: ID Diagnosis Fraction 1 X48 0.01 1 Y52 0.5 2 U78 0.85 etc What I need is only those records that have the highest fraction for each ID. ID Diagnosis Fraction 1 Y52 0.5 2 U78 0.85 etc I have used the Max function in my query. However, that does not work because the information is also grouped by diagnosis as well as ID and this causes all of the lines to appear. Does anyone know how to get around this problem? I can't just bring through Fraction ...

Copy formulas, format, validation to sheet with values
Environment: Excel 2000, Win 2000 I have a workbook with 12 sheets, one for every month of a year, all the same size, same format, just different values. I am still making changes to the workbook application, e.g. formulas, formats, validation, conditional formatting. So I change the sheet for one month, and then have to get the changes duplicated for all the other 11 sheets in the workbook. What is the best way to do that, especially for the formulas? Paste Special with deleting the constants afterwards is not a solution here, as some of the target sheets already have values entered manually...

FREQUENCY and dates
Hi, I'm doing some performance analysis and have a large number of timestamp (formatted as dd/mm/yyyy hh:mm:ss) / transaction duration pairs in a spreadsheet. I'm trying to create a histogram showing number of transactions each hour, but when I use the FREQUENCY function I get very small numbers associated with each timestamp "bin". Am I missing something fundamental about the way Excel handles dates? After that I need a similar plot of average duration each hour or 30 minutes. I don't see any way to force FREQUENCY to do that. Related to this, I tried selecting the...

Why is date repeated in brackets in Outlook 2007 Calendar?
in day, week, month, views "Buddie" <Buddie@discussions.microsoft.com> wrote in message news:DF1E0322-20E0-4137-9708-5638D1F472D8@microsoft.com... > in day, week, month, views Do you have alternate calendars enabled? Tools>Options>Calendar Options is where you find that setting. Otherwise, check the Date format(s) in the Windows Time/Date settings. -- Brian Tillman [MVP-Outlook] Do you have a second calendar index enabled? look in tools, options, calendar options. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.ne...

Weighted Average #2
I am trying to weight out the average number of peices packed togethe per purchase order to end up with an average of pieces packed togethe that is accurate. PO Units / Units packed Together 12,296 / 13 1,000 / 2 50,000 / 1 As you can see above I have a wide range of numbers. My data is colomn "f" for pcs and "o" for ordered units. With thousands of rows of data. Thanks -- MatthewFlinche ----------------------------------------------------------------------- MatthewFlinchem's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3411 View this t...

sum a cell value to a range
Hi I need to sum value in A4 to a range B4:B12 every time I enter a new value in A4. How can I do it? Thanks in advance Osmario "Osmario.Avila@gmail.com" <osmario.avila@gmail.com> wrote: > I need to sum value in A4 to a range B4:B12 every time I > enter a new value in A4. How can I do it? =SUM(A4,B4:B12) I wrote: > "Osmario.Avila@gmail.com" <osmario.avila@gmail.com> wrote: >> I need to sum value in A4 to a range B4:B12 every time I >> enter a new value in A4. How can I do it? > > =SUM(A4,B4:B12) Or did you mean that you want to a...

Open file with variable date name based on current date
Hello, I have a folder containing different .xls report files with the name format "YYYY MM text.xls", where YYYY stands for year, MM for month. I need a macro which, when run from an excel file saved in a different loacation, based on the current date, e.g. January 7, 2009, will open the file "2009 01 text.xls". Thanks for your help, Ciperian Workbooks.Open "C:\Foldername\" & Format(Now(),"yyyy mm") & " text.xls" Except it will open 2010 01 text.xls ;-) HTH, Bernie MS Excel MVP "Ciprian" <C...