Excel 2010 - Conditional Formatting

[Harry Covair]

Hi the forum,

In an Excel table showing the turnover by sellers (in column) and products (in line), I would like to display in red the seller with the largest total turnover without creating an additional column. Here is an example

[pre]

Product 1 Product 2	       Product 3	  Product 4
Salesman1	228	60	           408	            81
Salesman2	0	525	             0	             0
Salesman3	2282	323	           105	             0
Salesman4	0	580	          1425	          1170
Salesman5	0	1546	          1073	             0

[/pre]
Do some people can help me to solve this problem with a conditional formatting rule? (Salesman 4 must appear in red)

Thanks in advance

Harry

 

[bobhc]

Good day Harry


I think the easest way would be a helper, then use the top 10 in CF and then lower the top ten down to one. If you sum sales man one and then fill down and select all the sum rows first you will gwt your hightlighted salesman four but all salesmen totals as well.

[pre]

Product 1   Product 2  Product3  Product4	Sum
Salesman1	228	      60	408	81	777
Salesman2	0	     525	0	0	525
Salesman3	2282	     323	105	0	2710
Salesman4	0	     580	1425	1170	3175
Salesman5	0	    1546	1073	0	2619

[/pre]

 

[Harry Covair]

Hi Bobhc,

Thanks for your answer.

As the table is part of a sheet, I cannot add a (dummy) total colomn.

That the reason why I try to use a kind of matrix formula in a CF.

By the way, with a total colomn, the CF formula should be:

=sum(B2:E2) = max($F$2:$F$6), assuming "Salesman1" is in A2 and "product1" in B1


Regards


Harry

 

[NARAYANK991]

Hi Harry ,


Try this :


1. Define a named range Number_of_Rows , referring to : =ROW(INDIRECT("1:"&ROWS(Sheet1!$A$2:$A$6)))


where I have assumed your data is in Sheet1.


2. Select the range of salesmen's names , and put in the following CF formula :


=ROW(A2)-ROW($A$2)+1=MATCH(MAX(SUBTOTAL(9,OFFSET($B$2,Number_of_Rows-1,,,4))),SUBTOTAL(9,OFFSET($B$2,Number_of_Rows-1,,,4)),0)


where I have assumed your data starts from row 2.


The 4 is because I have assumed 4 columns of values ( products ).


The above formula will work if there is only one salesman with the highest sales ; in case there can be more than one , and you want to highlight all of them , then use this CF formula :


=MAX(SUBTOTAL(9,OFFSET($B$2,Number_of_Rows-1,,,4)))=SUBTOTAL(9,OFFSET($B$2,ROW(A2)-ROW($A$2),,,4))


Narayan

 

[Harry Covair]

Hi Narayan


Sorry for the late reply but I did not have had much free time during the weekend due to children and grandchildren.

First of all, congratulation and many thanks for your answer. It was precisely what I was looking for and except a little customization your solution is working perfectly.

I took some time to understand your formula but I have a problem with the "ROW(A2)-ROW($A$2)+1" in the conditional formatting formula.

Would you be so kindly to explain its meaning.

Thanks in advance for your answer.


Harry

 

[NARAYANK991]

Hi Harry ,


What we are doing using the SUBTOTAL(9,....) construct is forming the total for each row of the range ; if you see the working of the formula using the Evaluate Formula button , you can see the array of totals {777;525;2710;3175;2619}.


If your data starts from row 2 , then the construct ROW(A2)-ROW($A$2)+1 , when copied down will result in values of 1 , 2 , 3 , 4 and 5 , since the ROW(A2) will change to ROW(A3) , ROW(A4) , ROW(A5) and so on.


Since the MAX function will return the maximum of the row totals , it will return 3175.


We see which row has this value , and format it.


Since the MATCH function will return the first row which matches the value we are looking for , using it will color only one salesman ; in the second CF formula , we are coloring all rows which have this maximum value.


Please let me know if my explanation has not clarified the matter.


Narayan

 

[Harry Covair]

Hello Narayan,

Thank you for your detailed explanation, it is very clear. Meanwhile I put the formula Match (...) in an Index function to understand how it works.

Again, congratulations and thank you for your help.


Harry