• 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.

highlight top 5 of first 6 values in a row while skipping blanks but not 0's

F

FreakyGirl

Member
Hello All

I have a row of 14 cells with various numbers in them.
Im using 1 conditional format to show me the top 7 numbers in that range. (cell turns gray) - this works great

I would like to use another conditional format to show me the TOP 5 numbers of the FIRST 6 cells that have a value. (font should show as dark pink). - this isnt quite right.

out of the 14 cells in the row only 6 of them may have numbers in them. the conditional that does work uses this same idea but its not limited to only the first 6. i have tried using match, large, small, rank, etc but all i get is errors or it doesnt work at all.

I have helper columns set up ... Columns AP11 - AU11 show the first 6 numbers entered & Column AO11 shows them ranked serially but im not sure how to get from that to the formula needed.

suggestions? ideas? workbook attached.

thanks in advance,
FreakyGirl
 

Attachments

I think something like this could help ( you write that only six numbers can be in a row of that range)

=OR(RANK.EQ(O11,$O11:$AB11)=1,RANK.EQ(O11,$O11:$AB11)=2,RANK.EQ(O11,$O11:$AB11)=3,RANK.EQ(O11,$O11:$AB11)=4,RANK.EQ(O11,$O11:$AB11)=5)

Then the question is what to do if fifth and sixth score is a duplicate
 
Last edited:
Hi ,

I think you should just create a new workbook with 14 columns of data , which can have blanks , zeros and numbers in them. What ever formula is developed for this workbook can easily be replicated in your workbook.

I have not understood the following :
I would like to use another conditional format to show me the TOP 5 numbers of the FIRST 6 cells that have a value.
Click to expand...
In one row , the first 6 cells that have a value may span 8 columns because there are 2 blank cells in between ; in another row , the same 6 cells may span 11 columns because there are 5 blank cells in between ; is this correct ?

Narayan
 
tomas: ty for your reply. that would work if it didnt matter how many numbers were in the range. im looking to hightlight the TOP 5 of the FIRST six numbers entered in the range only.

Until the 6th number is entered all values will be highlighted dark pink. once the 6th cell is filled the conditional must look at the previous 5 numbers entered & decide if the 6th number would be considered part of the TOP 5.

if yes, than the 6th number font would turn pink like the others & the number it replaced the font would turn black as its the lowest number of the 6 entered. any other numbers entered after this point the font would be the default black.

if no then the font would remain as the default black.

if a duplicate it would highlight the first duplicate 7 5 5 6 3 3 only the first 3 would have a pink font & the second would be default black font.

-------------------------

Narayan: ty also for your reply.

yes, i could do that with a new workbook but i was trying to include the other formula which works perfectly. & its sorta what i need.

Code: 
=AND(SUMPRODUCT(--ISNUMBER($O11:$AB11))>=7,RANK(O11,$O11:$AB11)+SUMPRODUCT((O11=$O11:$AB11)*(O$10>$O$10:$AB$10))<8)

the above code will highlight gray the top 7 in the entire row. im looking to highlight only the top 5 of the first 6. i have tried to edit the code to do what i need, but i cant get it right.

i have tried both:
Code: 
=INDEX($O$11:$AB$11,LARGE(IF($O$11:$AB$11<>"",COLUMN($O$11:$AB$11)-COLUMN($O$11)+1),6))

or
Code: 
=IF($O$11:$AB$11<>"",COLUMN($O$11:$AB$11)-COLUMN($O$11)+1)

along with other variations but i cant quite get it.

In one row , the first 6 cells that have a value may span 8 columns because there are 2 blank cells in between ; in another row , the same 6 cells may span 11 columns because there are 5 blank cells in between ; is this correct ?
Click to expand...
yes! thats exactly how it would work.

thanks to both of you for your help.
FreakyGirl
 
Hi Freaky Girl

=IFERROR(RANK.EQ(O11,$O11:INDEX($O11:$AB11,1,SMALL(IF(ISNUMBER($O11:$AB11),COLUMN($O11:$AB11)),6)-COLUMN($O11)+1))<6,COUNT($O11:$AB11)<6)

This formula should work as CSE as you can see on the right in the attachement.
Duplicates are not sorted yet because I need to go. I will do it later if nobody jumps in meanwhile :)
 

Attachments

=IFERROR(RANK.EQ(O11,$O11:INDEX($O11:$AB11,1,SMALL(IF(ISNUMBER($O11:$AB11),COLUMN($O11:$AB11)),6)-COLUMN($O11)+1))<6,AND(NOT(ISBLANK(O11)),COUNT($O11:$AB11)<6))

actually I needed to add modification not to highlight blank cells when a row does not contain 6 or more values
 
Hi

So I dealt with duplication and now it should work as you need. Hovewer this array formula is not accepted in CF window ( it says something like reference operators cannot be used) So I left next to it helper columns and CF did like if cells in helper columns are true or False
 

Attachments

hello tomas

i tried the workbook but its not working for me. all the helper columns say TRUE or FALSE if there are 5 numbers. when i put in the 6th number all the helper columns change to #NAME?. im sure it has something to do with the .xltn in the formula.

can you please adjust because im using a earlier version of excel then you are.

thanks,
FreakyGirl
 
I am getting warning messages while saving the file that versions sooner then 2007 , there are not compatible functions plus overlapping conditional formatting causes problems.

Which version do you use ?
 
excel 2000 *hangs head in shame* :(

i went back & found the post that dealt with my excel & this same error:

http://forum.chandoo.org/threads/alphabetical-columns.22376/#post-135784

=_xlfn is displayed where you have formula which are not supported in excel version you are working with. May be it's because of me using IFERROR. See this link
https://support.office.com/en-sg/ar...-formula-882f1ef7-68fb-4fcd-8d54-
9fbb77fd5025

speaking of the overlapping conditional... my ultimate goal is to have them work together. maybe im going about this the wrong way.

conditional 1: highlight gray the top 7 numbers in the range - if 7 or more numbers exist in range. - works great
conditional 2: highlight pink the top 5 of the first 6 in the same range.

The problem is that if one of the first 5(pink) is also the part of the top 7 conditional 1 will take over.

if a cell meets both conditionals then the cell should be BOTH gray (to show top 7) but the font should be also be pink ( to show first 5)

please look at worksheet that says test. i removed the conditionals from a few rows & manually colored the cells to show you what im trying to do.

i had to delete alot of the info in there that had nothing to do with what we are working on in order to reupload it.

thanks for your attention,
FreakyGirl
 

Attachments

You must log in or register to reply here.
Back
Top