Highest & Lowest

[hsandeep]

Z1=1 or 2 as soon as system time equals 'start time' & 'end time' respectively else Z1=0 (default)
During this "Time Period" when Z1 first becomes equal to 1 & finally when it becomes equal to 2;
C2:C200 & D2 D200 keeps on updating its values continuously.
Output required: in E2:E200 & F2:F200 which should be the highest value of respective cell address of C column & the lowest value of respective cell address of D column AS LONG AS Z1=1.
Ex:
For Z1=1
C2=200.22 D2=507.43 E2=200.22 F2=507.43
C2=201.34 D2=501.43 E2=201.34 F2=501.43
C2=204.75 D2=500.43 E2=204.75 F2=500.43
C2=204.75 D2=508.55 E2=204.75 F2=500.43
C2=203.89 D2=500.43 E2=204.75 F2=500.43
...
Answer is E2 & F2.
Similarly for other C3:C200 & D3:D200 answer would be in E3:E200 & F3:F200.
How to accomplish?

 

[Andrew Poulsom]

I don't understand the significance of Z1, but can't you use these formulas in E2 and F2 copied down?

=MAX(C$2:C2)

=MIN(D$2:D2)

 

[hsandeep]

I don't understand the significance of Z1

My Excel workbook contains real time feed. The ws in which Z1 is there becomes equal to 1 at a PARTICULAR TIME T1 (named as start time) & finally becomes equal to 2 at a PARTICULAR TIME T2 (named as end time). BEFORE T1 & AFTER T2; Z1=0.
So there is a defined time frame from T1 to T2 i.e. when Z1=1 or 2 UNLESS & UNTIL Z1 equals to 0.

So for this period I need the solution.

 

[Andrew Poulsom]

What should be in E2:F200 when Z1 is zero?

 

[hsandeep]

When Z1=0 column C2:C200; D2:D200 CHANGES. But E2:E200 & F2:F200 should not consider the changes in column C & column D respectively & should show the last highest & lowest value.

 

[Andrew Poulsom]

To do that with formulas would require circular references.

 

[hsandeep]

If circular references doesn't create problem than OKAY else can you help with a macro as this ws does not contain any macros. There are few more ws in this Excel workbook (for your info.).
I am using Excel 2007.
When Z1=0 column C2:C200; D2:D200 CHANGES. E2:E200 & F2:F200 SHOULD NOT consider the changes in column C & column D.
When Z1=1 column C2:C200; D2:D200 CHANGES. E2:E200 & F2:F200 SHOULD CONSIDER the changes in column C & column D.
When Z1=2 column C2:C200; D2:D200 CHANGES. E2:E200 & F2:F200 SHOULD CONSIDER the changes in column C & column D.

 

[hsandeep]

Andrew

Waiting for your help through a macro....brother.

 

[Andrew Poulsom]

What is causing Z1 to change?

 

[hsandeep]

X1=9/15/14 1:00:00 PM
Y1=9/15/14 1:30:00 PM
Z1=1 if system time>=X1 AND <=Y1

So 'DURING' the time period chosen, Z1=1