• 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 using sumproduct - problem with 0/blank

hello

im having a couple of problems with my worksheet.

problem 1.
i have a conditional format that will highlight top 3 numbers in any given row. if there is a 0 in the row it goes wacky & highlights many other cells in the same row.

i have attached my sheet. please look at row 26 to see the above. now look at row 49. if another 0 is added, the same thing will happen.

my code:
Code:
=AND(SUMPRODUCT(--ISNUMBER($I9:$AD9))>=3,RANK(I9,$I9:$AD9)+SUMPRODUCT((I9=$I9:$AD9)*(I$8>$I$8:$AD$8))<6)

problem 2:

same workbook, same conditional, please refer to rows 22 & 46. there are 4 cells highlighted. every time the G column increases it will highlight another cell. the goal is to only have the top 3 in any row. it wont highlight until there are at least 3 numbers but then it doesnt stop.

any help of course would be appreciated.

ty in advance
FreakyGirl
 

Attachments

  • bosfile.xls
    98 KB · Views: 3
Hi Freaky,

Your formula has some error according to me or If I am unable to see something than please inform.

=AND(SUMPRODUCT(--ISNUMBER($I9:$AD9))>=3,RANK(I9,$I9:$AD9)+SUMPRODUCT((I9=$I9:$AD9)*(I$8>$I$8:$AD$8))<6)

The bold portion above has row fixed and that row number contains text so I did not understood the purpose of this logical test.

Can you explain??

Second, why <6 ?

Third, what if there are duplicates say a row contains, 9, 9, 8, 7 or 9, 9, 8, 7, 5

Than in these case what should be highlighted?

Regards,
 
ty for your quick reply.
to be honest, im not sure why. i recycle codes & modify them as needed in other places. i use this formula in another workbook & i dont seem to have this problem or havent noticed it yet. the only diff being is that this workbook uses text in the rows where the other does not.

re: the <6. it should be <4. that fixed problem 2. thanks to you asking about it it made me look at it more in depth & i noticed that when coping from one month to the other & making a slight change i put the wrong number. it works correctly now only higlight 3 unless of course there are duplicates.

re: the duplicates.
i was going to ask about that once i got the other problems fixed. it happened last month also but with the holidays i didnt have time to worry about it. the math is correct in other areas that uses those top numbers & it doesnt count the duplicates in even thou they highlight. i would like only the 1st occurance of a number to highlight if more than the same number is not needed. ie: in the case of 5 9 2 5 5 6 the 9 the 6 & the 1st 5 would highlight only.

i updated the workbook to show the change with the <4 & i also included last months sheet which shows the problem of the dups.

however, even with fixing the <4 the one row still highlights orange all wacky because of the 0. it also will highlight the 1st blank space in the row if a 0 is present as shown in row 26. the 0 is required because its a score as opposed to a blank meaning that person didnt play that night.

again, thank you for help in advance.
FreakyGirl
 

Attachments

  • bosfile.xls
    172.5 KB · Views: 1
hello.
i havent heard anything since i last replied & updated workbook.
why is that one row & only that one row doing that?
please help & ty for your time
FreakyGirl
 
hello.
i havent heard anything since i last replied & updated workbook.
why is that one row & only that one row doing that?
please help & ty for your time
FreakyGirl

Hey Just missed this one. See the file and comment.

Regards,
 

Attachments

  • bosfile (1).xls
    188 KB · Views: 3
ty for your answer. im sorry but i dont see any notes - maybe im not looking in the right place?

upon opening up the workbook i see that that 1 row no longer is highlighted all wacky - ty - however there are still 2 problems. one with the 0 one with the duplicates.

please look at L26. the 3 scores of 0, 1, 11 should all be highlighted. 0 is a score as opposed to a blank cell. row 49 also has a 0 in it & if you add another score it also will not highlight like row 26.

please look at N22. in this case the duplicate should be highlighted because its part of the 3 scores. ie: 8 7 7 7 7. the 8 & the 1st two 7s only would be highlighted.
-------------
last night was our weekly challenge so i have included a new worksheet with your updated conditional & another week of scores. easier to see when there are 4 scores instead of 3.

as above the same problems are happening but the rows are diff than above with the addition of new scores/players.

any row that has only 2 highlighted is wrong. row 22, 27 & 29 are all off because of the duplicates even thou they should be included with the top 3 scores.

thanks again in advance for your help.
FreakyGirl
 

Attachments

  • bosfile (1).xls
    273 KB · Views: 2
Hi ,

The issue is not just with the rows which have only 2 highlighted ; the issue is with every row ; even where there are 3 numbers highlighted , if you change one of the numbers to be a duplicate , the highlighting for that number disappears.

Narayan
 
Hi ,

We come back again to the same issue of logic correctness , which can never be done if we start off with an Excel formula.

I have introduced a lot of helper columns ( in the Jan15 tab ) , so that you can verify the correctness of the logic ; try this with as much variety of data as possible , and give your feedback.

Narayan
 

Attachments

  • bosfile (1).xlsx
    127.3 KB · Views: 4
Hi Freaky,

There is a lot of confusion with the points you laid out:

1. Can you tell if you have scores like 0,1,11,11,11 than what should get highlight.
2. In case of 1,11,7,12,12,12

Regards,
 
hello

ty for your help but i cant seem to open the file. i have tried several times & it just downloads as a webpage that brings me back here.

i noticed the extension of your file is .xlsx compared to my .xls & my version maybe isnt updated enough to read your file. i hate to ask but can you pls upload in .xls for me?

thanks,
FreakyGirl
 
Hi Freaky,

There is a lot of confusion with the points you laid out:

1. Can you tell if you have scores like 0,1,11,11,11 than what should get highlight.
2. In case of 1,11,7,12,12,12

Regards,


hello again.

case 1: the 11,11,11 would be highlighted as they are the 3 highest. however if there was a 4th 11 only the FIRST 3 should highlight 0,1,11,11,11,11

case 2: the 12,12,12 would highlight as they are the 3 highest. as case 1 if there was another 12, depending on placement would decide which ones would highlight. 12,12,1,11,7,12,12,12

for reference: highest score a player can get is 14, lowest score would be a 0. blank spaces mean a player didnt play that night.

i hope this helps & thanks for the help.
FreakyGirl
 
ty for the new file. i will work on this this upcoming week but from first glance it appears to be working exactly right. im not sure i understand the helper columns completely.

FreakyGirl
 
hello.

i just wanted to tell you that your formula seemed to have solved the problem of both the highlighting & duplicates.

i have spent some time filling in the sheet & at first i couldnt get your helper columns to work completely. but after seeing what you did i was able to figure out the problem & its working as i wanted.

thanks for spending your time helping people figure out their excel problems.

have a great day!
FreakyGirl
 
Back
Top