• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional formatting - Help - Bit complicated

R

ramnaidu

New Member
Hi All

Hope you are doing well.

I have a value in Sheet 1 in cell A1, if the cell value (A1, Sheet 1) is in the Top 25% of the values of column A in sheet 2, I want to get green, between top 25% to 75% to Amber and bottom 25% to Red.

How can I do it, please help.
 
AIM said:
Hi Ram,

Please check the attached file if this is what you are looking for.

Regards
Click to expand...
Hi AIM

Thanks for your message, but it was bit different what I need.

In sheet 1 there is only one cell C1(and the data in it comes from no of calculations) and there are 100 cells in sheet 2.
If C1 in sheet 1 is in top 25 of 100 cells in sheet 2 then it should be green.

The value of C1 in sheet 1 will change frequently and so conditional formatting should change accordingly.

Hope I have explained you the situation correctly.
 
Good day ramnaidu

You do not see CF as such, it is applied to the cells as per the CF rules.


upload_2014-6-4_19-51-39.png


The above are the rules that Somendra has put in place.

=A1<QUARTILE(Sheet2!$A$1:$A$19,1)
=AND(A1>=QUARTILE(Sheet2!$A$1:$A$19,1),A1<=QUARTILE(Sheet2!$A$1:$A$19,3))
=A1>=QUARTILE(Sheet2!$A$1:$A$19,3)
.
 
bobhc said:
Good day ramnaidu

You do not see CF as such, it is applied to the cells as per the CF rules.


View attachment 7290


The above are the rules that Somendra has put in place.

=A1<QUARTILE(Sheet2!$A$1:$A$19,1)
=AND(A1>=QUARTILE(Sheet2!$A$1:$A$19,1),A1<=QUARTILE(Sheet2!$A$1:$A$19,3))
=A1>=QUARTILE(Sheet2!$A$1:$A$19,3)
.
Click to expand...
Hi BOB

Thanks for the reply
Please find the attached spreadsheet and help with my example
When I selected Nike on sheet 1 his conversion rate is 0.25, all the details in sheet 1 i do get from sheet 2.
So conversion rate for Mike is 0.25 which is last so it should be red.
If I have selected Antha on sheet 1 then conversion rate is 100% then conditional formatting should be green.
Hope I explained correctly. The data on sheet is from sheet. please help
 

Attachments

bobhc said:
Good day ramnaidu

You do not see CF as such, it is applied to the cells as per the CF rules.


View attachment 7290


The above are the rules that Somendra has put in place.

=A1<QUARTILE(Sheet2!$A$1:$A$19,1)
=AND(A1>=QUARTILE(Sheet2!$A$1:$A$19,1),A1<=QUARTILE(Sheet2!$A$1:$A$19,3))
=A1>=QUARTILE(Sheet2!$A$1:$A$19,3)
.
Click to expand...
Forgot to say I am using excel 2007.
 
You must log in or register to reply here.
Back
Top