Forum Discussion
multiplication with increments of realtime clock
Hi,
Could you please provide small sample with source data and desired result? Part of questions:
What is exact format for the text in column A ? It's like "volume @ time" or "volume@time", 9.05 means 9Hrs 5min or 9 and 5/100 Hrs, etc
- where the magic 50 is kept
- where is the time with which sync values in column B
- what exactly you'd like to calculate in B (multiplying average on number of minutes fives cumulative sum; which increments to take)
Firstly thanks for the response
Trading time : 09:00 to 15:30
COLUMN A : this is the data i get from the Data brokers which will give me cumulative data and will be changing based on interval for example , this value is to compare with value from Column B.
Column B is what i need your help
example if i see data at 09:01 as 100 and see 09:02 as 200 which means by 9:02 which is 2 minutes from trading the volume is 200, so if i see at 10: 00 as 2000 which means after 1 hour of trading the volume is 1000 quantities.
i don't get minute by minute data and hence i am trying to that in excel which is column B and compare with Volume A ( meaning i don't get what happened between 9:06 and 9:07 when i see the data at 09:10 )
Column B : this magic 50 is number i derived for different sheet , this is basically volume average per minute , take any number of your choice.
lets say for example
at 09:01 AM it should multiple 50 by one time because 1 minute(after trading started) for example which is 50*1 = 50 , this value i will compare with Volume A at 09:01 am and create an alert if it is 10 times more , meaning alert me when Column A (volume traded getting from data broker) is greater than 500 (10 times)
at 09:02 AM it should multiple 50 by two time because 2 minute(after trading started) for example which is 50*2 = 100 , this value i will compare with Volume A at 09:02 am and create an alert if it is 10 times more , meaning alert me when Column A (volume traded getting from data broker) is greater than 1000(10 times)
Note 10:00 AM should be 60 minute , similarly 11:00 120 minute and so on till 15:30 which is 390 Minute from trading started which is 09:00 AM
at 15:30 AM it should multiple 50 by 390 time because 390 minute(after trading started) for example which is 50*390 = 19500 , this value i will compare with Volume A at 15:30 PM and create an alert if it is 10 times more , meaning alert me when Column A (volume traded getting from data broker) is greater than 195000(10 times)
I have attached screenshot which should explain most of the things
If I understood that correctly
you may use
=IF(ISBLANK(A2),"",(A2-$F$2)*24*60*$F$1)
where you have time from left most column.
Is the Time getting incremented every minute ?
in real time you only have only ROW , where Data column be changing every minute and so do A2
our B2 must be multiplying in increments of 50 for every minute change
I really appreciate your efforts on this , but i dont see the time incrementing real time
