I have excel sheet with numeric data 15 rows and 4 columns(A,B,C,D). (Excel Image attached, in image E column and F column added for explanation only) Required formula for following conditions.(image)
Actually formula need to apply for covering only two row if suppose required output DATA for row#3 (A3) than ONLY above row#2 (B,C,D) data needs to consider in formula, means for A3 data need to consider row#2 B2,C2,D2) for A4 data need to consider B3,C3,D3) likewise same for other rows.
A3 NOT HIGHER THAN B2 AND NOT LOWER THAN C2 "NORMAL"
A5 HIGHER THAN B4 "HIGHER"
A6 LOWER THAN B5 "LOWER"
A8 FIRST 3 DIGIT MATCH WITH C7 AND D7 "L-SIRIES"
A11 FIRST 3 DIGIT MATCH WITH B10 AND D10 "H-SERIES"
A13 FIRST 3 DIGIT MATCH WITH B12 "H-RANGE"
A14 FIRST 3 DIGIT MATCH WITH B13 "L-RANGE"
currently I tried below formulas.
=IF(A3>B2,"HIGHER",IF(A3C3,"NORMAL",""))) (somehow after writing above formula here in BOX <> sign not displaying once I save my edits)
I tried another for "H-RANGE" =IF(LEFT(A3,3)=LEFT(B2,3),IF(A3=B2,TRUE, FALSE),TRUE) [getting Output but result is not accurate.
I tried another for "L-RANGE" =IF(LEFT(A3,3)=LEFT(C2,3),IF(A3=C2,TRUE, FALSE),TRUE) getting result but result is not accurate.
Conditional Formatting FORMULA also fine for highlight "A" column WITH DIFFERENNT condition match color.
Thank You in Advance.