Time series function

**schlepers** · 03-26-2008, 05:27 PM

I'm trying to calculate and average using a time frame in the day. I want to use a certain number "X" that correlates to a hour/minute in the current time. How do I write a statement to get me the correct "X" value?

example:

8:00 .005
8:01 .006
8:02 .0075
8:03 .0077
8:04 .008

time now is 8:03

How do I get the .0077 in excel?

thx

**Claymation** · 03-26-2008, 08:58 PM

For your post this formula would work to return .0077:

=VLOOKUP(A4,A1:B5,2,FALSE)

However, there are a few questions...where is the time coming from? Do you have that pasted in a cell in a "=NOW()" function, is it returned from another cell, or do you just want to enter it in a cell and use that as a reference for the formula?

In the formula posted above I used A4 as the reference as that cell corresponds with "8:03" and therefore returns the correct value. But I am imagining that there is more to your sheet than that.

Can you provide a little more information?

**schlepers** · 03-27-2008, 02:49 PM

I don't know how to get a clock in there.

I want it so that what ever time it is, by hour/minute, it finds the matching hour/minute and uses the variable in the next cell over from it so that I can make an average from another inputed field from past days to make a calculate average.

**Claymation** · 03-27-2008, 03:20 PM

To get the exact date and time in a specific cell you can use the NOW formula:

=NOW()

Only one problem with that formula is that it stays static until you recalculate. To get around that you can invoke this code in your workbook to show an actual date and time (running)

Please Login or Register  to view this content.

**schlepers** · 03-27-2008, 04:07 PM

Do I put that in the macro area? Also I don't need the date, just time.

**Claymation** · 03-28-2008, 06:54 PM

Yes, you would add that in as code on your specific sheet. You can take out the date portion and just use this poriton of the code that will place the current time in A1.

Please Login or Register  to view this content.

Hope that helps.

**schlepers** · 03-30-2008, 09:10 PM

I'll try to make it work tomorrow.

**schlepers** · 03-31-2008, 01:07 PM

Do I put it in the Visual Basic editor? And it should work from cut n pasting it?

**Claymation** · 04-01-2008, 12:37 AM

Yes, if you have the workbook open, press ALT + F11 and it will open up. Right click on "This Workbook" for your code and go Insert > Module. Then paste the code into the workbook and save. To start the clock running just press ALT + F8 and run the "RECALC" macro. The current time should appear in A1.

Hope that helps.