# Highest & Lowest

#### hsandeep

##### Well-known Member
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
...
Similarly for other C3:C200 & D3:D200 answer would be in E3:E200 & F3:F200.
How to accomplish?

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Andrew Poulsom

##### MrExcel MVP
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

##### Well-known Member
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

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

#### hsandeep

##### Well-known Member

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

##### MrExcel MVP
To do that with formulas would require circular references.

#### hsandeep

##### Well-known Member

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

##### Well-known Member
Andrew

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

#### Andrew Poulsom

##### MrExcel MVP
What is causing Z1 to change?

#### hsandeep

##### Well-known Member
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

Replies
0
Views
67
Replies
15
Views
489
Replies
13
Views
780
Replies
5
Views
79
Replies
3
Views
84

1,109,349
Messages
5,528,188
Members
409,807
Latest member
nicky736

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...