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?

 

[hsandeep]

Did lot of exercise & finally got something....but a little wrong answer appears. Perhaps the formula needs to be corrected.
Sheet1

	C	D	E	F	G
		In correct

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #ffff00"]CORRECT[/TD]
[TD="bgcolor: #ffff00"]CORRECT[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]200.23[/TD]
[TD="align: right"]500.23[/TD]
[TD="align: right"]200.52[/TD]
[TD="bgcolor: #ffff00, align: right"]200.23[/TD]
[TD="bgcolor: #ffff00, align: right"]500.23[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]200.23[/TD]
[TD="align: right"]500.12[/TD]
[TD="align: right"]200.52[/TD]
[TD="bgcolor: #ffff00, align: right"]200.23[/TD]
[TD="bgcolor: #ffff00, align: right"]500.12[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]200.52[/TD]
[TD="align: right"]500.11[/TD]
[TD="align: right"]200.52[/TD]
[TD="bgcolor: #ffff00, align: right"]200.52[/TD]
[TD="bgcolor: #ffff00, align: right"]500.11[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]200.44[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]200.52[/TD]
[TD="bgcolor: #ffff00, align: right"]200.52[/TD]
[TD="bgcolor: #ffff00, align: right"]500.11[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E2 =ContinualMax($C2:$C$200,$Z$1=1) E3 =ContinualMax($C3:$C$200,$Z$1=1) E4 =ContinualMax($C4:$C$200,$Z$1=1) E5 =ContinualMax($C5:$C$200,$Z$1=1) <tbody> </tbody>

<tbody>
</tbody>

I need the Maximum value of the column C in column E & Minimum value of the column D in column F.

 

[mikerickson]

to get those desired results, the range argument should be C2:D2

 

[hsandeep]

Agreed. range argument has been changed. Your formula is giving me MAXIMUM FROM the ENTIRE RANGE in all cells whereas I need 'running' maximum & minimum value...I hope I have been able to post my point clearly. Please see my table in thread #31

 

[mikerickson]

have you tried it with varying values for z1?

 

[hsandeep]

No because Z1 will generate either of 1 or 0 ONLY.
What I am trying to convey is that for Z1=1, I need 'running' maximum of the C2:C200 in E2:E200 & 'running' minimum of the D2:D200 in F2:F200
Ex:
E2=C2 (Always)
E3=either of C2 or C3 whichever is higher
E4=either of C2 or C3 or C4 whichever is higher
E5=either of C2 or C3 or C4 or C5 whichever is higher

& similarly minimums in F2:F200
F2=D2 (Always)
F3=D2 or D3 whichever in minimum
F4=D2 or D3 or D4 whichever in minimum
F5=D2 or D3 or D4 or D5 whichever in minimum & so on....