Excel question

M

mgookin

My data is pasted after the question (so you don't have to scroll a mile to see what I'm asking).

I can't calculate these data in present form because there are multiple independent variables for each dependent variable; by definition that's not a function.

The instrument reporting the data only gives a hh:mm:ss format. I have done everything possible to make it display to millisecond and it's not going to happen. (The data is collected in a text file and exported to Excel format after the instrument is turned off).

To calculate data, I need a unique Time for each Value, and a unique Value for each Time.

Here's what I'm thinking:
Write a script looking for common time values of a unique time, and do an average of the Values for each unique second in time.

How do I do that? Or does someone have a better idea?

Thanks!
Date Time Value Unit
1-9-2010 23:02:15 63.2 Lux
1-9-2010 23:02:15 63.2 Lux
1-9-2010 23:02:16 63.4 Lux
1-9-2010 23:02:16 63.4 Lux
1-9-2010 23:02:16 63.3 Lux
1-9-2010 23:02:16 63.3 Lux
1-9-2010 23:02:16 63.3 Lux
1-9-2010 23:02:16 63.3 Lux
1-9-2010 23:02:16 63.3 Lux
1-9-2010 23:02:16 63.3 Lux
1-9-2010 23:02:17 63.4 Lux
1-9-2010 23:02:17 63.4 Lux
1-9-2010 23:02:17 63.4 Lux
1-9-2010 23:02:17 63.4 Lux
1-9-2010 23:02:17 63.4 Lux
1-9-2010 23:02:17 63.4 Lux
1-9-2010 23:02:17 63.3 Lux
1-9-2010 23:02:17 63.3 Lux
1-9-2010 23:02:17 63.3 Lux
1-9-2010 23:02:17 63.0 Lux
1-9-2010 23:02:18 63.0 Lux
1-9-2010 23:02:18 63.0 Lux
1-9-2010 23:02:18 60.1 Lux
1-9-2010 23:02:18 60.1 Lux
1-9-2010 23:02:18 60.1 Lux
1-9-2010 23:02:18 54.5 Lux
1-9-2010 23:02:18 54.5 Lux
1-9-2010 23:02:18 54.5 Lux
1-9-2010 23:02:18 51.1 Lux
1-9-2010 23:02:19 51.1 Lux
1-9-2010 23:02:19 51.1 Lux
1-9-2010 23:02:19 20.1 Lux
1-9-2010 23:02:19 20.1 Lux
1-9-2010 23:02:19 20.1 Lux
1-9-2010 23:02:19 3.7 Lux
1-9-2010 23:02:19 3.7 Lux
1-9-2010 23:02:19 3.7 Lux
1-9-2010 23:02:19 17.4 Lux
1-9-2010 23:02:20 17.4 Lux
1-9-2010 23:02:20 17.4 Lux
1-9-2010 23:02:20 61.5 Lux
1-9-2010 23:02:20 61.5 Lux
1-9-2010 23:02:20 61.5 Lux
1-9-2010 23:02:20 72.0 Lux
1-9-2010 23:02:20 72.0 Lux
1-9-2010 23:02:20 72.0 Lux
1-9-2010 23:02:20 69.0 Lux
1-9-2010 23:02:20 69.0 Lux
1-9-2010 23:02:21 69.0 Lux
1-9-2010 23:02:21 68.3 Lux
1-9-2010 23:02:21 68.3 Lux
1-9-2010 23:02:21 68.3 Lux
1-9-2010 23:02:21 62.2 Lux
1-9-2010 23:02:21 62.2 Lux
1-9-2010 23:02:21 62.2 Lux
1-9-2010 23:02:21 58.8 Lux
1-9-2010 23:02:22 58.8 Lux
1-9-2010 23:02:22 58.8 Lux
1-9-2010 23:02:22 60.1 Lux
1-9-2010 23:02:22 60.1 Lux
1-9-2010 23:02:22 60.1 Lux
1-9-2010 23:02:22 57.9 Lux
1-9-2010 23:02:22 57.9 Lux
1-9-2010 23:02:22 57.9 Lux
1-9-2010 23:02:22 63.3 Lux
1-9-2010 23:02:22 63.3 Lux
1-9-2010 23:02:23 63.3 Lux
1-9-2010 23:02:23 63.8 Lux
1-9-2010 23:02:23 63.8 Lux
1-9-2010 23:02:23 63.8 Lux
1-9-2010 23:02:23 64.0 Lux
1-9-2010 23:02:23 64.0 Lux
1-9-2010 23:02:23 64.0 Lux
 
Can you go into more detail about what you are trying to accomplish? Kind of hard to figure out. Sounds like you could do a simple

= if ( ) or maybe = or ( if ( ) )

But I didn't fully grasp what you are trying to accomplish. It has been a while since I have programmed in excel , but luckily it is very user friendly. Although it unfortunately is not that dynamic.
 
I found the answer.

It's use the import data function with text to columns to split date/ time/ Value/ etc., then run a Pivot Table to have it tell me average Value per given second of time.

Works like a champ.

It's not for trading. I came here because I know smart people hang out here.

Thanks for the offer there Algo
 
Quote from mgookin:

I found the answer.

It's use the import data function with text to columns to split date/ time/ Value/ etc., then run a Pivot Table to have it tell me average Value per given second of time.

Works like a champ.

It's not for trading. I came here because I know smart people hang out here.

Thanks for the offer there Algo
More...

Good call man.

GL
 
You must log in or register to reply here.
Back
Top