Solved

# Conditional Formatting Excel

Posted on 2011-04-30
Medium Priority
499 Views
Hi Experts,

I'm back asking more questions on conditional formatting.

As some of you that have assisted me in the will know, I currently trade stocks on the NYSE. I seek help identifying a money making pattern using Excel.

Just as a FYI the pattern is called a Double Bottom. Its used by traders to help identify a change in trend - I have attached an illustration of the pattern for those that may be interested. I have also attached the spreadsheet where I looking to use with the conditional format.

OK, a)  I'm seeking help compiling a conditional format what will find where two prices are the same, between two points. For example, you will see E2 has a value of 88.53 and DL2 also has a value of 88.53. This would satisfy the first condition.

b) On day fourteen, I would like the value in Cell BE2 to be greater than the cell E2(88.53) by 15%

That would satisfy the second condition.

c) I would like cell DP2 to be greater than DH2 and DV2 to be greater than DP2. That would satisfy the third condition.

If all conditions are met I would like a highlight in cell A2.

Need-less-to-say, I would like the condition replicated through out the spreadsheet.

I have received help with the third condition in the past, so hopefully that shouldn't prove to tricky for you Excel gurus.

I have tried to explain myself in as much detail as possible, but let me know if you need further clarification.

As always, thanks in advance for your help.

Cheers
EEDoubleBottom.xlsm
Double-Bottom.doc
0
Question by:cpatte7372
• 10
• 10

Author Comment

ID: 35499534
Wow, that's the longest ive waited for a response.

Please let me know if you need further information - you guys have assisted me with far more difficult excel questions than this one.

Cheers
0

LVL 42

Expert Comment

ID: 35499953
Please elaborate earlier on condition A.  Every row has at least one match with another column.  What does two prices the same mean?
0

LVL 42

Expert Comment

ID: 35499972
For simplicity, I put your 3 conditions and the 4th- a test for all prior three  = true in cells DC -> EC.

You need to elaborate on condition 1 as it doesn't make sense unless you advise what columns to compare with what columns.  As it stands in your data, almost all rows have at least one set of duplicates, or "price matches".

If we can clarify that, you'll have your desired solution.

See attached preliminary solution, pending your clarification.

Dave
EEDoubleBottom-r1.xlsm
0

Author Comment

ID: 35500071
Dave
Thanks for responding. Give me a second and I will clarify.
0

Author Comment

ID: 35500095
Dave

As regards the first condition, I'm looking situations where the price/cell value in E2 is the same as the price/cell value in DM2 (sorry I originally said DL2)

The second condition I would like the cell value in BD2 to be greater than the cell value in E2(88.53) by 15%.

The third condition I would like DP2 to be greater than DM2 and DV2 to be greater than DP2.

I hope this helps you assist me further.

Sorry for the confusion.

Cheers

0

Author Comment

ID: 35500157
Dave,

Did the further clarification help?
0

LVL 42

Expert Comment

ID: 35500169
Yes - "where two prices are the same" was innocuous to me, even with the example, I read that to mean any two in the row, thus the sophisticated formula to look for # unique numbers in the row.  That can be greatly simplified.

PS - there is no instance where E2 = DM2 in the dataset...

Dave
0

LVL 42

Expert Comment

ID: 35500172
Condition B - are you changing your criteria?  Look at your post and the original question...
0

LVL 42

Expert Comment

ID: 35500176
Condition C changed as well.

I put all your conditions in and get NO matches where all 3 conditions are true.  Is that correct from where you sit?

Dave
0

LVL 42

Expert Comment

ID: 35500187
To keep things "simple", I've put the criteria in columns DZ:EC.  If EC = TRUE (when the 3 conditions are met), the conditional formatting sets column A for that given row.

Its easy enough to put all these conditions directly into the conditional formatting, and we can do that at the end.  Its easier to debug with you if we keep these as formulas in the columns, for now - especially if your criteria is changing.

Here's what I currently have.

Dave
EEDoubleBottom-r1.xlsm
0

LVL 42

Expert Comment

ID: 35500287
I'm fixing to "hit the hay" and go to bed.  Any feedback (I'll wait around for a few more minutes)?

Dave
0

Author Comment

ID: 35500294
Dave,

Thanks again mate.

Going to check it out now....
0

Author Comment

ID: 35500297
Dave,

Thats flippin brilliant mate. You've nailed it.

Can't thank you enough.

Just need to make loads of money now :-)

Cheers
0

LVL 42

Expert Comment

ID: 35500333
Terriffic!  I'll send you my Paypal account when you're ready to share :)

Cheers,

Dave
0

Author Comment

ID: 35500380
Dave,

I hope you're still here mate.

My first condition is too rigid for me to find any true states.

Can you please tweak the first condition so that DM2 is within 2 0r 3% above/below E2.

I will simply never get a true statement if I leave it as E = DM

Cheers mate..
0

Author Comment

ID: 35500405
Ha ha, just saw you last comment; I'll give you a few share tips when I pick up a few winners....

I hope you read my last request??

Cheers
0

Author Comment

ID: 35500653
Hello Experts,

I wonder if someone could tell me if its possible to tweak the following formula so the a BD2 is greater or less than E2 by 15%.

At the moment, the formula states if BD2 is just greater than E2.

=IF(AND(ISNUMBER(BD2),ISNUMBER(E2)),BD2>E2*(1.15),FALSE)

Cheers
0

LVL 71

Expert Comment

ID: 35501031
Do you really want to have a single condition for "less or greater"? That would be "not equal" then, "<>".
0

LVL 42

Expert Comment

ID: 35501450
>>Can you please tweak the first condition so that DM2 is within 2 0r 3% above/below E2.

Done - =IF(AND(ISNUMBER(E6),ISNUMBER(DM6)),AND(E6>=DM6*(0.98),E6<=DM6*(1.03)),FALSE)

tests for E >= 98% of DM and E <= 103% of DM

>>BD2 is greater or less than E2 by 15%.

Are you sure you want not equal?  Perhaps there's a type o here.  What are you looking for?

See attached,

Dave
0

LVL 42

Accepted Solution

dlmille earned 2000 total points
ID: 35501453
Whoops...

See attached,

Dave
EEDoubleBottom-r1.xlsm
0

Author Comment

ID: 35701596
Dave,

Sorry its taken so long to respond.

I've just finished testing and it works like a dream.

You're a star.

I'll buy you a drink if you ever come to London.

Cheers mate....
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month13 days, 20 hours left to enroll

#### 807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.