Find High & Low in different ranges meeting certain criteria

[hsandeep]

This thread I have cross posted Cell value dependent on multiple conditions

2 input ranges are there in my Worksheet (tab named ‘HL’)

Range 1: D10:M10 which generates either of 1 or 0 or “” (null) with a condition that 1 would be generated in a ‘contiguous’ manner i.e. if 1 is generated in K10 then D10:J10=1
Range 2: 9 sets with each set of 2 rows. The 9 sets are D4:M6; D12:M14; D16:M18; D20:M22; D24:M26; D28:M30; D32:M34; D36:M38 & D40:M42. These 9 sets (with 2 rows each) generates a numerical value >=0 or “” (null)
Answers needed in 9 ranges (Range 3) each having 2 rows. 9 ranges (Range 3) are T4 & T6; T12 & T14; T16 & T18; T20 & T22; T24 & T26; T28 & T30; T32 & T34; T36 & T38; T40 & T42
Criteria’s:

• Code should find the cell in D10:M10 with last “1” (here in example K10)
• Then, corresponding ‘cells’ in Range 2:D4:M4 & D6:M6 (here K4 & K6)..allow me to call K4 & K6 as ‘base cells’ for further correspondence purpose only & similarly for other 8 sets of Range 2 i.e. D12:M12 & D14:M14 (here K12 & K14) & so on up to ……. D40:M40 & D42:M42 (here K40 & K42)
• In 4th row (corresponding to base cell K4), check the ‘1st cell’ on its left hand side of K4 (J4 in this case) findhigh=IF(AND(J4>K4,J6<K6),J4,check ‘next cell’ on its left hand side)
• findlow=IF(AND(J4>K4,J6<K6),J6,check ‘next cell’ on its left hand side)

[*]check ‘next cell’ on its left hand side=IF(AND(I4>K4,I6<K6),I4) for findhigh & =IF(AND(I4>K4,I6<K6),I6) for findlow

• IF no cells in D4:J4 (in this case up to J4 since the corresponding base cell is K4) meets the criteria, then ‘base cells’ becomes J4 & J6 for code’s further checking purpose.
• Follow criteria #3 i.e. check the ‘1st cell’ on left hand side of J4 (I4 in this case)
• findhigh=IF(AND(I4>J4,I6<J6),I4,check ‘next cell’ on its left hand side)
• findlow=IF(AND(I4>J4,I6<J6),I6,check ‘next cell’ on its left hand side)
• check ‘next cell’ on its left hand side=IF(AND(H4>J4,H6<J6),H4) for findhigh & =IF(AND(H4>J4,H6<J6),H6) for findlow
• Follow criteria #4 i.e. IF no cells in D4:I4 (in this case up to I4 since the corresponding base cell is now J4) meets the criteria, then ‘base cells’ becomes I4 & I6 for code’s further checking purpose….& so on
• findhigh =’Null’ & findlow=’Null’ IF no criteria is met (example shown in row #36 & 38)
• findhigh =’Null’ & findlow=’Null’ IF the ‘current’ base cell/(s)=0 or “” (null)
  
• I tried but was unable to write the correct ‘complete’ code (wrong answers in row #12 & 14; row #28 &30)
  

Appreciate your patience.

HighLow v3.xlsm
	D	E	F	G	H	I	J	K	L	M	N						T			W	X	Y
1																	Answer given by current code					Correct Answer
3
4	488.90	488.00	485.00	650.00	650.00	530.00	580.00	600.00	0.00	0.00							650					650.00
6	476.80	475.00	477.00	498.00	499.00	520.00	501.00	500.00	0.00	0.00							499					499.00
9
10	1	1	1	1	1	1	1	1	0	0
11
12	84.45	84.00	85.00	80.00	75.00	99.00	98.00	100.00	0.00	0.00							Null					99.00
14	81.50	80.00	70.00	70.00	70.00	89.00	90.00	95.00	0.00	0.00							Null					89.00
15
16	759.50	720.00	759.00	730.00	780.00	777.00	800.00	769.00	0.00	0.00							800					800.00
18	748.80	780.00	749.00	750.00	750.00	751.00	600.00	650.00	0.00	0.00							600					600.00
19
20	354.50	320.00	330.00	340.00	350.00	320.00	325.00	329.00	0.00	0.00							350					350.00
22	346.50	350.00	320.00	321.00	300.00	300.00	301.00	302.00	0.00	0.00							300					300.00
23
24	364.30	365.00	348.00	338.00	400.00	329.00	355.00	356.00	0.00	0.00							365					365.00
26	354.80	320.00	320.00	300.00	353.00	305.00	350.00	352.00	0.00	0.00							320					320.00
27
28	235.95	235.00	240.00	250.00	260.00	255.00	220.00	219.00	0.00	0.00							Null					260.00
30	226.50	227.00	230.00	230.00	240.00	245.00	210.00	209.00	0.00	0.00							Null					240.00
31
32	132.60	136.00	160.00	165.00	155.00	156.00	158.00	145.00	0.00	0.00							155					155.00
34	123.80	126.00	131.00	145.00	150.00	155.00	151.00	151.00	0.00	0.00							150					150.00
35
36	223.25	220.00	215.00	232.00	212.00	241.00	210.00	250.00	0.00	0.00							Null					Null
38	219.15	210.00	200.00	200.00	200.00	201.00	200.00	201.00	0.00	0.00							Null					Null
39
40	35091	35000	34000	34600	34600	34550	34558	34555	0	0							35091.45					35091.45
42	34554	34600	33960	34557	34556	34449	34557	34555	0	0							34554					34554.00
HL

Cell Formulas
Range	Formula
T4	T4	=findhigh($D$4:$M$4,$D$6:$M$6,$D$10:$M$10)
T6	T6	=findlow($D$4:$M$4,$D$6:$M$6,$D$10:$M$10)
T12	T12	=findhigh($D$12:$M$12,$D$14:$M$14,$D$10:$M$10)
T14	T14	=findlow($D$12:$M$12,$D$14:$M$14,$D$10:$M$10)
T16	T16	=findhigh($D$16:$M$16,$D$18:$M$18,$D$10:$M$10)
T18	T18	=findlow($D$16:$M$16,$D$18:$M$18,$D$10:$M$10)
T20	T20	=findhigh($D$20:$M$20,$D$22:$M$22,$D$10:$M$10)
T22	T22	=findlow($D$20:$M$20,$D$22:$M$22,$D$10:$M$10)
T24	T24	=findhigh($D$24:$M$24,$D$26:$M$26,$D$10:$M$10)
T26	T26	=findlow($D$24:$M$24,$D$26:$M$26,$D$10:$M$10)
T28	T28	=findhigh($D$28:$M$28,$D$30:$M$30,$D$10:$M$10)
T30	T30	=findlow($D$28:$M$28,$D$30:$M$30,$D$10:$M$10)
T32	T32	=findhigh($D$32:$M$32,$D$34:$M$34,$D$10:$M$10)
T34	T34	=findlow($D$32:$M$32,$D$34:$M$34,$D$10:$M$10)
T36	T36	=findhigh($D$36:$M$36,$D$38:$M$38,$D$10:$M$10)
T38	T38	=findlow($D$36:$M$36,$D$38:$M$38,$D$10:$M$10)
T40	T40	=findhigh($D$40:$M$40,$D$42:$M$42,$D$10:$M$10)
T42	T42	=findlow($D$40:$M$40,$D$42:$M$42,$D$10:$M$10)