how to take value of variable data with time.

[nnish]

There is two rows.One row is time other one changing value with time.I want to take value of specific time and unchange.

[JohnTopley]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[nnish]

please check my attachment.

[JohnTopley]

How is time/price changed as you need to capture time within 1 minute of the hour e.g. 0900:0901

[nnish]

No.Price change withing 1 second ,2 second or 1 minute ............like that. That is no need to consider, I need when my time comes to time row first value should copy and after that no change.

[JohnTopley]

in E3

=IF($A$3=D3,$B$3,0)

Copy down

Price will remain unchanged for 24 hours.

You may want to put some "tolerance" on the check ie, within +/- 10 seconds.

[nnish]

It is not working after one minute. it was changed.Time row time changing 9:01:1, 9:01:25, 9:01:37,.................like that.That time was changed price also changed.My attachment mention time is wrong.

[JohnTopley]

Hence the need for a "tolerance" to be applied to the check of A3 vs D3. It will be (almost) pure chance you get an exact match to the second.

[nnish]

I can apply 30second tolerance.please send me formula.

[JohnTopley]

Try

=IF(AND($A$3-TIME(0,0,30)<=D3,$A$3+TIME(0,0,30)>=D3),$B$3,0)

so time of 090:00, then (inclusive) time between 08:59:30 and 09;00:30 will return a result

[nnish]

Sir,

That function take value.But

When time and price change but price value should be unchanged.
for example,
At 10:00:00 price is 0.4578.
time will come 10:30:45 and price will come 0.4124.but price value column not change, it should be 0.4578.

[JohnTopley]

So under what conditions does the price change???

My test for 30 SECONDS not 30 minutes!

[nnish]

sir,
Price column value change any time, like a stock market. I need time match with my time (D3) Price will appear in E3. That price will not change 59 minute.

[JohnTopley]

It will not change with my formula if the time is +/- 30 seconds of your time in D3 PROVIDING you have hourly intervals.

[nnish]

A3 and D3 time is matching and E3 price appear. After 30 second that E3 value going to 0. But i want E3 value not change withing 1 hour.

[JohnTopley]

If the value in E is to remain constant (once set) then you will need VBA to do this as you cannot have both a formula and (an unchanging) cell value based on that formula.

You probably need Worksheet Event macro.

[JohnTopley]

Rght click on Sheet1 and click "View Code" (already done in Attachment)

Copy and paste code below:

Please Login or Register  to view this content.

Change time/price in A3/B3 to test

Hopefully this will work with a real time feed.