Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Max values in a range
Howdy All,
I have a table of values in 50 columns by 13 rows. Each row represents Hours for an employee for each project list in the 50 columns What I want to do is count the number of times each employee has the highest hours. So I want to count how many times the highest hours values occurs in each row for the entire range. I hope i'm making sense... Thanks, Brian |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Max values in a range
Hi,
I am not sure if I understood your questions correctly but try this COUNTIF(A1:AX1,MAX(A1:AX1)) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Brian" wrote in message ... Howdy All, I have a table of values in 50 columns by 13 rows. Each row represents Hours for an employee for each project list in the 50 columns What I want to do is count the number of times each employee has the highest hours. So I want to count how many times the highest hours values occurs in each row for the entire range. I hope i'm making sense... Thanks, Brian |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Max values in a range
Thanks for the reply.
I want it to show me how many times A1:AX1 contains the max value for each column in the range A1:AX13. "Ashish Mathur" wrote in message ... Hi, I am not sure if I understood your questions correctly but try this COUNTIF(A1:AX1,MAX(A1:AX1)) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Brian" wrote in message ... Howdy All, I have a table of values in 50 columns by 13 rows. Each row represents Hours for an employee for each project list in the 50 columns What I want to do is count the number of times each employee has the highest hours. So I want to count how many times the highest hours values occurs in each row for the entire range. I hope i'm making sense... Thanks, Brian |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Max values in a range
Hi,
Try this. COUNTIF(A1:AX1,MAX(A1:AX13)) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Brian" wrote in message ... Thanks for the reply. I want it to show me how many times A1:AX1 contains the max value for each column in the range A1:AX13. "Ashish Mathur" wrote in message ... Hi, I am not sure if I understood your questions correctly but try this COUNTIF(A1:AX1,MAX(A1:AX1)) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Brian" wrote in message ... Howdy All, I have a table of values in 50 columns by 13 rows. Each row represents Hours for an employee for each project list in the 50 columns What I want to do is count the number of times each employee has the highest hours. So I want to count how many times the highest hours values occurs in each row for the entire range. I hope i'm making sense... Thanks, Brian |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Max values in a range
On Sat, 20 Sep 2008 01:12:29 -0500, "Brian"
wrote: Howdy All, I have a table of values in 50 columns by 13 rows. Each row represents Hours for an employee for each project list in the 50 columns What I want to do is count the number of times each employee has the highest hours. So I want to count how many times the highest hours values occurs in each row for the entire range. I hope i'm making sense... Thanks, Brian If your data is in A1:AX13 you can try the following: In cell A14 you put the formula =MAX(A1:A13) Copy this formula to cells B14:AX14 Now you have a (helper) row, row 14, with the highest number for each project In cell AY1 you put the formula =SUMPRODUCT(--(A1:AX1=A$14:AY$14)) Copy this formula to cells AY2:AY13 Now you have a column, AY, with the requested result Note that if several employees have the same highest number of hours, that will be counted for all of them. Hope this helps / Lars-Åke |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Max values in a range
On Sat, 20 Sep 2008 12:26:35 +0530, "Ashish Mathur"
wrote: Hi, Try this. COUNTIF(A1:AX1,MAX(A1:AX13)) MAX(A1:AX13) returns the "global" maximum. I think what the OP wants is to compare, per project/column, each employees hours to the maximum hours for that project, not to the "global" maxiumum. Lars-Åke |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Max values in a range
Lars is correct. Thank you.
"Lars-Åke Aspelin" wrote in message ... On Sat, 20 Sep 2008 12:26:35 +0530, "Ashish Mathur" wrote: Hi, Try this. COUNTIF(A1:AX1,MAX(A1:AX13)) MAX(A1:AX13) returns the "global" maximum. I think what the OP wants is to compare, per project/column, each employees hours to the maximum hours for that project, not to the "global" maxiumum. Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count values in a range of cells | Excel Worksheet Functions | |||
count even-numbered values in a range | Excel Discussion (Misc queries) | |||
How do I count unique values within a date range? | Excel Discussion (Misc queries) | |||
how do i count cells with values in a certain range ? | Excel Discussion (Misc queries) | |||
Count the how many different values in a range | Excel Worksheet Functions |