Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif with conditional formatting
I have rows of numbers such as:
John Mike Jason Joe Tom 11 12.5 15 17 4 12 14 11 6 5 6 11 9 4 14 ...... For a given row, using conditional formatting, I highlight the lowest number. (min function). From the column perspective, I would like to total the number of times a column (person) had the lowest number. One thing I tried was using a formula such as: =offset(min(A3:E3),-1,0) I was trying to get the name of the person with the lowest number, per row. Then I thought I could do a countif down the columns of names.... Anyway, the offset function I tried has an error in it, and I just can't seem to clear it. Any help would be appreciated. Thanks John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif with conditional formatting
=Index($A$1:$E$1,1,match(min(A2:E2),A2:E2,0))
in F2, then drag fill down the column. this will give the person with the min for that row. then you can use countif on Column F =Countif(F:F,"John") -- Regards, Tom Ogilvy "Johnny" wrote in message oups.com... I have rows of numbers such as: John Mike Jason Joe Tom 11 12.5 15 17 4 12 14 11 6 5 6 11 9 4 14 ..... For a given row, using conditional formatting, I highlight the lowest number. (min function). From the column perspective, I would like to total the number of times a column (person) had the lowest number. One thing I tried was using a formula such as: =offset(min(A3:E3),-1,0) I was trying to get the name of the person with the lowest number, per row. Then I thought I could do a countif down the columns of names.... Anyway, the offset function I tried has an error in it, and I just can't seem to clear it. Any help would be appreciated. Thanks John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif with conditional formatting
1. Suppose that you data table (John-Mike-Jason-Joe-Tom) commences at
Cell A2. 2. Enter the Formula =INDEX(A$2:E$2,MATCH(MIN($A3:$E3),$A3:$E3,0)), into Cell F3 and fill-down. 3. Enter the Formula =COUNTIF($F$3:$F$5,A2), into Cell A1 and fill-right. 4. Last but not least, Is there a temptation to integrate the two formulae into One ? 5. Regards. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif with conditional formatting
Tom,
Exactly what I needed, thank you. John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting with COUNTIF | Excel Worksheet Functions | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
Countif and conditional formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting And Countif | Excel Discussion (Misc queries) | |||
Countif and Conditional Formatting | Excel Worksheet Functions |