# Calculate Highest number per row on Excel

Calculate Highest number per row on Excel

I have Excel spreadsheet shown on the screenshot .
Numbers 01 to 11 represent the Months of the year, I skipped number 12 in purpose.

I would like to know on each row which number is the Highest using a formula and the highest numbers will show on the Cells of the Column located after month 11

Thank you
High.JPG
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IT System ArchitectCommented:
Hi,
If you would like to return the highest value in a given row, use the =max([range]) function.
More details about it you can find here.

Correct me if my understanding of your question is wrong.
0
VBA ExpertCommented:
Can't you just use =MAX(A1:L1) and copy down?

Would be the first row for month 0 and L1 would be the first row for month 11, as you copy down the formula should adjust as required.
0
Older than dirtCommented:
You can't (as far as I can figure out) simply copy down a formula from row 1 because if you do, the row references will change and not the columns, and it's the columns that need to change. So my suggestion is to put

=MAX(A\$1:A\$12)

in row 1 of any column you want, copy it down, and manually change the column references in the other 11 rows. It's not ideal but it only needs to be done once.
0
VBA ExpertCommented:
Martin

Why do the column references need to change?

In the image the column headings are 0-11, let's assume they are in columns A-L.

jskfan want's to know the highest value in each row, to get the maximum of row 2 we would use =MAX(A2:L2), for row 2 it would be =MAX(A3:L3) and so on.

So it's the row reference that's changing in the formula not the column reference.
0
Older than dirtCommented:
Norie, you are absolutely right. I totally misread the question and thought (as you can see in my formula) that the author wanted the max for each month.
0
Author Commented:
This is worked. =MAX(A\$1:A\$12)
If you can make the cell that has the Max with different color that would be Great.
0
VBA ExpertCommented:
jskfan

Did that work for all the rows of data?
0
Author Commented:
it did.
All I need is to color the Cells that have the maximum value
0
VBA ExpertCommented:
jskfan

Can you upload a sample workbook indicated whether it's rows or columns you want to find the maximum value for?

This formula will find the maximum value from rows 1-12 of column A:

=MAX(A\$1:A\$12)

This formula will find the maximum value from columns A-L in row 1:

=MAX(\$A1:\$L1)
0
Finance AnalystCommented:
To colour the cell with the max value for that row you will need to use Conditional Formatting.

A sample of data would make it easier to demonstrate.
0
Author Commented:
Conditional Formatting examples I found , did not work for me. Can you please look at the attached file, and tell me how Conditional Formating will apply to to those cells ?
0
Finance AnalystCommented:
No attachment. You need to click the Upload button before clicking Submit
0
Older than dirtCommented:
Conditional formatting for the maximum value in either a row or a column is easy.
Select the row or column and then
1. Conditional Formatting
2. Top/Bottom Rules
3. Top 10 items...
4. Choose '1' from the list on the left of the displayed window and the formatting you want
5. Click OK
0
Author Commented:
Martin Liss

You can apply it just for 1 row, and it will color the Max Value, Good.
But you cannot apply to all Cells in all rows.
0
Finance AnalystCommented:
With a formula based Conditional Format, you can.

Please upload an excel file of sample data; just the data you used for the screenshot in the question will do.
0
Older than dirtCommented:
You can apply it just for 1 row, and it will color the Max Value, Good.
But you cannot apply to all Cells in all rows.
Copy the row (or column) and then paste->Formatting.
0
Author Commented:
Copy the row (or column) and then paste->Formatting.  ?? not clear
0
Older than dirtCommented:
• Copy the row or column
• Select the other rows or columns where you also want to highlight the maximum
• Then right-click on those cells and select the 4th icon (Formatting) in Paste option.
0
Finance AnalystCommented:
See attached sample, gave up waiting for one so made my own with random numbers.

Two sets of same data in the sheet.

The first set shows the MAX by row to the right of the table and cell is highlighted if cell is MAX in that row.

The second set shows the MAX by column below the table and cell is highlighted if cell is MAX in that column.

Hope that helps.
EE-Example.xlsx
1

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Rob Henson

That's what I am looking for . how did you set it up ?
0
Author Commented:
I mean the coloring Part
0
Finance AnalystCommented:
Which sample is correct, MAX number by row or MAX number by column?
0
Author Commented:
Rob Henson

Got it working now.
Thank you
0
Finance AnalystCommented:
Both options use the Conditional Formatting using formula to determine which cells to format.

Max by rows formula is:
=A2=MAX(\$A2:\$K2)

Max by columns formula is:
=A20=MAX(A\$20:A\$32)

Equivalent for first set would be:
=A2=MAX(A\$2:A\$14)

The applies to range would be \$A\$2:\$K\$14
0
Author Commented:
Thank you
0
Older than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
Finance AnalystCommented: