-1

enter image description here

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.

enter image description here

  • 2
    Please add the formulas that you have tried – Gangula Jul 23 '19 at 11:37
  • currently I am using =IF(A3>B2,"HIGHER",IF(A3<C2,"LOWER",IF(B3<>C3,"NORMAL",""))) and getting result for "HIGHER", "LOWER" and "NORMAL" but output NORMAL is not accurate and I need help for "H-SERIES" , "H-RANGE" , "L-SERIES" , "L-RANGE" – JUSTWIN Jul 23 '19 at 16:30
  • 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. – JUSTWIN Jul 23 '19 at 16:38
  • 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. – JUSTWIN Jul 23 '19 at 16:39
  • You explain a lot of the conditions with terms that aren't meaningful to anyone but you. In the 1st three, you can compare two cells, but you've added qualifiers normal, higher, and lower. What do we do with those terms? Similarly, the last four refer to series and ranges. Those terms appear in col E as "explanation", but it isn't obvious what's being explained or how to use the information. There's an indication that those are actually desired outputs, but then the conditions that produce those outputs aren't clear. (cont'd) – fixer1234 Jul 23 '19 at 23:57
0

enter image description here

How it works:

  • Enter this Formula in Cell AF2 & fill down.

    =IF(AND(LEFT(AA2,3)=LEFT(AC1,3),LEFT(AA2,3)=LEFT(AD1,3)),"Lseries",IF(AND(LEFT(AA2,3)=LEFT(AB1,3),LEFT(AA2,3)=LEFT(AD1,3)),"Hseries",IF(AND(LEFT(AA2,3)=LEFT(AB1,3)),"HRange",IF(AA2>AB1,"Higher",IF(AA2<AC1,"Lower",IF(AB2<>AC1,"Normal","LRange"))))))

N.B.

  • Formula does't returns LRange for Row 14, because first 3 digits of the value in AA14 doesn't matches with first 3 digits of AB13.

  • Check values in both cells in BLUE color.

You may adjust cell references in the formula as needed.

| improve this answer | |
  • RAJESH SIR, Thank YOU SO MUCH for your PRECIOUS efforts. – JUSTWIN Jul 24 '19 at 6:17
  • RAJESH SIR, Thank YOU SO MUCH for your PRECIOUS efforts. There is little mismatching forrow14 LRange - but LET me try from my end with another DATA, will update you if need your help to modify in your provided formula.. Thank You Genius. – JUSTWIN Jul 24 '19 at 6:23
  • @JUSTWIN,, read the N.B. with my post I've described Y Row 14 doesn't matches,, since it's check with uneven pattern so it may vary in cell/cells, depends on set or Rows !! – Rajesh S Jul 24 '19 at 6:27
  • @ Rajesh S. Thank You SIR, Yes I noted your comment about Row 14. I am happy to inform that with your PERFECT Formula, I just made little changes now RESULT is SUPERB.. Thank you for your HELP and SUPPORT. – JUSTWIN Jul 24 '19 at 8:11
  • =IF(AND(LEFT(N4,3)=LEFT(P3,3),LEFT(N4,3)=LEFT(Q3,3)),"Lseries",IF(AND(LEFT(N4,3)=LEFT(O3,3),LEFT(N4,3)=LEFT(Q3,3)),"Hseries",IF(N4>O3,"Higher",IF(N4<P3,"Lower",IF(AND(LEFT(N4,3)=LEFT(O3,3)),"HRange",IF(AND(LEFT(N4,3)=LEFT(P3,3)),"LRange",IF(N4=O4,"Open=High","Normal"))))))) – JUSTWIN Jul 24 '19 at 8:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.