Counting rows, then counting values.

[Michael via OfficeKB.com]

I have a lot of spreadsheets with many rows and columns of numbers. I need
a way to find the highest value in each row, (mark it some how, bold the
number maybe), then count the number of "highest" values are in each
column.

Is there an easy way to do this?

Thank You,
Michael

 

[Ron de Bruin]

Hi Michael

You can use this to get the value

=MAX(A1:A20)

and this to count

=COUNTIF(A1:A20,MAX(A1:A20))

 

[barrfly]

I would use the MAX function to determine the highest value in eac
column for example to find the highest value in column A insert a ro
at row 1 and inter this function =Max(A2:A1000).

Use the conditional formatting too to identify the location of the ma
value by selecting the entire column, select format on the toolbar
then conditional formatting then choose under condition 1 cell value
equal to - then enter "=A1" in the value field and choose the format t
differentiate the max value from the rest of the field.

To count the number of occurences of that max value, use the counti
function.

Barrfl

 

[Michael via OfficeKB.com]

How can I count how many of the highest values are in each column. Example:
Rows = Hours, Columns = Days. I need to find out which of the hours in each
day is the highests number. Next I need to count how many days have the
same hour as its highest number.

1:00 2:00 3:00 4:00
Monday 12 13 14 15
Tuesday 16 18 25 10
Wednes. 20 14 40 30

Total: 1 1 2 0 <- this is the output im looking for

 

[Michael via OfficeKB.com]

First part works great, is there a way to do a fill so that I don't have to
do it for every line? How do you I use the countif statement to count the
number of bolded numbers in each column?

Thank you everyone for the help!

Michael

 

[Ron de Bruin]

See the second formula that I posted
=COUNTIF(A1:A20,MAX(A1:A20))

 

[Guest]

Is there a way in which you can make Excel mark the higest number in a row or
column?

Example:

A B C
1 1 3 2

In this example, I would like the cell B3 (or the text in the cell)
automatically highlighted, as it being the higest value of the three cells
A1:C1

Is this possible?

Thank you for any answers!
Thomas Bentsen



"Ron de Bruin" skrev:

 

[JE McGimpsey]

Check out Conditional Formatting in XL Help. One way:


Select A1:C1. Choose Format/Conditional Formatting. Set the dropdowns
and textbox to read

CF1: Formula is =A1=MAX($A$1:$C$1)
Format1: Patterns/<highlight color>