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
jskfanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Michal ZiembaIT AdministratorCommented:
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.
NorieAnalyst Assistant Commented:
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.
Martin LissOlder 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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

NorieAnalyst Assistant Commented:
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.
Martin LissOlder 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.
jskfanAuthor 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.
NorieAnalyst Assistant Commented:
jskfan

Did that work for all the rows of data?
jskfanAuthor Commented:
it did.
All I need is to color the Cells that have the maximum value
NorieAnalyst Assistant Commented:
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)
Rob HensonFinance 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.
jskfanAuthor 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 ?
Rob HensonFinance AnalystCommented:
No attachment. You need to click the Upload button before clicking Submit
Martin LissOlder 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
jskfanAuthor 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.
Rob HensonFinance 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.
Martin LissOlder 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.
jskfanAuthor Commented:
Copy the row (or column) and then paste->Formatting.  ?? not clear
Martin LissOlder 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.
Rob HensonFinance 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

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
jskfanAuthor Commented:
Rob Henson

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

Got it working now.
Thank you
Rob HensonFinance 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
jskfanAuthor Commented:
Thank you
Martin LissOlder 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
Rob HensonFinance AnalystCommented:
Glad to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.