Highest & Lowest

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,213
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
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?
 
Did lot of exercise & finally got something....but a little wrong answer appears. Perhaps the formula needs to be corrected.
Sheet1

CDEFG
1 In correctCORRECTCORRECT
2200.23500.23200.52200.23500.23
3200.23500.12200.52200.23500.12
4200.52500.11200.52200.52500.11
5200.44555200.52200.52500.11

<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>
</tbody>

Spreadsheet Formulas
CellFormula
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.
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
 
Upvote 0
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....
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top