If and or conditions (combination) help please

A

aiki100

Board Regular
Joined
Aug 16, 2016
Messages
102
Greetings all - and Merry Christmas, Happy Holidays and a wonderful whatever else you may choose to celebrate at this tail end of the year.


I have been trying accomplish this below, and google, this site and many others have led me close, but I just can’t find the right bit to get it done. Any help much appreciated!


I have two (math derived) criteria, both of which must be true, OR another two (math derived) which could instead be true. it is also possible that neither (set) of conditions might be true.


​Excel 2016 (Mac) 32 bit
ABCDEFG
Values
Row LabelsMax of High Min of Low
1/3/10
1/4/10
1/5/10
1/6/10
1/7/10
1/8/10
1/10/10
1/11/10
1/12/10
1/13/10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]High[/TD]
[TD="align: center"]Low[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]S[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1.4337[/TD]
[TD="align: right"]1.4257[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1.4456[/TD]
[TD="align: right"]1.4269[/TD]
[TD="align: center"]HH[/TD]
[TD="align: center"]HL[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1.4483[/TD]
[TD="align: right"]1.4334[/TD]
[TD="align: center"]HH[/TD]
[TD="align: center"]HL[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1.4447[/TD]
[TD="align: right"]1.4283[/TD]
[TD="align: center"]LH[/TD]
[TD="align: center"]LL[/TD]
[TD="align: center"][/TD]
[TD="align: center"]S[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1.4404[/TD]
[TD="align: right"]1.4299[/TD]
[TD="align: center"]LH[/TD]
[TD="align: center"]HL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1.4439[/TD]
[TD="align: right"]1.4263[/TD]
[TD="align: center"]HH[/TD]
[TD="align: center"]LL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1.4533[/TD]
[TD="align: right"]1.4408[/TD]
[TD="align: center"]HH[/TD]
[TD="align: center"]HL[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1.4556[/TD]
[TD="align: right"]1.4453[/TD]
[TD="align: center"]HH[/TD]
[TD="align: center"]HL[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1.4546[/TD]
[TD="align: right"]1.4456[/TD]
[TD="align: center"]LH[/TD]
[TD="align: center"]HL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]1.4579[/TD]
[TD="align: right"]1.4463[/TD]
[TD="align: center"]HH[/TD]
[TD="align: center"]HL[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"][/TD]

</tbody>
D HL​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #F0E0E0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]D4[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]B4>B3,"HH", "LH"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]E4[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]C4>C3,"HL", "LL"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]F4[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D4="HH",E4="HL"),"L", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]G4[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D4="LH",E4="LL"),"S", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]D5[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]B5>B4,"HH", "LH"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]E5[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]C5>C4,"HL", "LL"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]F5[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D5="HH",E5="HL"),"L", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]G5[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D5="LH",E5="LL"),"S", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]D6[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]B6>B5,"HH", "LH"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]E6[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]C6>C5,"HL", "LL"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]F6[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D6="HH",E6="HL"),"L", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]G6[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D6="LH",E6="LL"),"S", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]D7[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]B7>B6,"HH", "LH"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]E7[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]C7>C6,"HL", "LL"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]F7[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D7="HH",E7="HL"),"L", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]G7[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D7="LH",E7="LL"),"S", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]D8[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]B8>B7,"HH", "LH"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]E8[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]C8>C7,"HL", "LL"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]F8[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D8="HH",E8="HL"),"L", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]G8[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D8="LH",E8="LL"),"S", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]D9[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]B9>B8,"HH", "LH"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]E9[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]C9>C8,"HL", "LL"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]F9[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D9="HH",E9="HL"),"L", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]G9[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D9="LH",E9="LL"),"S", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]D10[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]B10>B9,"HH", "LH"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]E10[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]C10>C9,"HL", "LL"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]F10[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D10="HH",E10="HL"),"L", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]G10[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D10="LH",E10="LL"),"S", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]D11[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]B11>B10,"HH", "LH"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]E11[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]C11>C10,"HL", "LL"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]F11[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D11="HH",E11="HL"),"L", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]G11[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D11="LH",E11="LL"),"S", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]D12[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]B12>B11,"HH", "LH"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]E12[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]C12>C11,"HL", "LL"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]F12[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D12="HH",E12="HL"),"L", ""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #F0E0E0"]G12[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]AND(D12="LH",E12="LL"),"S", ""[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




I have a day. (A3)
There is a high and low on that day (B3 and C3)


On the next day, (row4) I want to know if that high is higher than the day before (HH) AND if the low is higher than the day before (HL). If both these conditions are true, then this should produce “HHHL”.


If it is not true, I want to know if the high is lower than the day before (LH), and if the low is lower than the day before (LL). If these two sets of criteria are true, then this should produce “LHLL”




As it is, I could only figure out how to do this in pieces, as below”


so D4 is =IF(B4>B3,"HH", "LH")
and E4 is =IF(C4>C3,"HL", "LL")


compared today’s high and low to yesterdays




F4 is =IF(AND(D4="HH",E4="HL"),"L", "")
G4 is =IF(AND(D4="LH",E4="LL"),"S", "")


Compared these result to see if the combo of HH and LH existed. If it it dis, it returned “L”


…and the reverse, for G4




What I’d like to do, is combine all of this, and have the result, “S”, “L”, or blank (if nothing is true) appear in one cell.




I’ve been able to build pieces but have not been able to build it all in one formula. So there is some basic functionality I do not understand, so if there IS a solution to this, I would appreciate it, if someone could point out to me what essential knowledge I was missing, or the best function or info that I should go study.


Thank you all so much for your help - it is much appreciated!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Something like this?

=IFS(AND($B4>$B3,$C4>$C3),"HHHL",AND($B4<$B3,$C4<$C3),"LHLL",$B4>$B3,"HH",$B4<$B3,"LH",$C4>$C3,"HL",$C4<$C3,"LL")

You may continue with other conditions...
 
Upvote 0
Well thank you so very much! After I adjusted for my columns (F&G rather than AB), this worked great. Can't thank you enough! I can see how you approached it, and this is very helpful for my learning how to construct these on my own.

I hope you have a very fine week - thank you again!
 
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Question Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
329
gtd526
gtd526
gtd526
  • Question Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
357
gtd526
gtd526
D
  • Solved
Chart Help Please
Replies
1
Views
106
dmj120
D
P
  • Question Question
Count names based on enrollment
Replies
2
Views
229
paulkc
P
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
363
gtd526
gtd526

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

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