-4

I want to find the max value of a column, and save it in the next column in the corresponding row.

For example, if I have four rows and the max value is in the third one, then I want to show the max in the third row of the second column.

example:

1
6
12 12
3

or

18 18
1
3
8
halfer
  • 19,824
  • 17
  • 99
  • 186
Gabriele Mandalari
  • 25
  • 5
  • Still going to need an example of what you're trying to achieve here... The edit that you added provides no additional information beyond the comment to my original answer below. Just trying to help. :) – Derrik Nov 16 '13 at 18:36

3 Answers3

2

To find the maximum value in a string of cells (or a row in this case) use the following:

=MAX([row])

As an example, the maximum value in row 1 will be returned using the below equation:

=MAX(1:1)

This can also be used to search for maximum values in columns such as:

=MAX(A:A)

Cheers.

::EDIT FOR RESPONSE:: Thank you for adding an example. To achieve what you are looking for, I would recommend using the following formula if your data falls in column A, and then copy it downward as far as you need:

=IF(MAX($A:$A)=A1,A1,"")

Do note that if you have more than 1 row that contains the same maximum value for your data series, the maximum value will appear in your adjacent target cells for each instance that this happens.

Derrik
  • 1,053
  • 7
  • 10
  • no it's not good for me! i'm sorry! i want to save the result in the same row! for example if i have 3 row and the max value is in the second one i want to save the max in the secon row and second column. – Gabriele Mandalari Nov 16 '13 at 17:52
  • Can you include an example of what the layout looks like? As I'm picturing things you will run into Circular Reference warnings unless you use VBA to write over everything. – Derrik Nov 16 '13 at 18:07
  • thank you for your answer? but i don't understand where should I put the formula! if i put it after the last value of first column it give me error! – Gabriele Mandalari Nov 18 '13 at 12:13
  • Although it will work anywhere, I would suggest placing the formula in the column adjacent to your data. Per your example, you will have your data in the left column, and this formula in the right column as far down as you have data. When you copy it downward, the cell address will need to change from A1 to A2 to A3, etc. which Excel will do automatically for you if you copy and paste it. – Derrik Nov 18 '13 at 16:52
0

How do I get the highest value in a row in excel

Or

Return Max Value of range that is determined by an Index & Match lookup

Community
  • 1
  • 1
محمد النعيمي
  • 570
  • 2
  • 9
  • 21
0

One solution would be - not a pretty one - to add an if-statement in the column next to the column that you would like to check. Just copy it all rows in such a way that it adjusts the relative references. The formula would be something like:

=IF(B5=MAX($B$5:$B$8);B5;"")

Where the numbers (18,1,3, and 8) are in fields B5:B8. You can make the code less CPU intensive by making one max-statement in a column and check it against that.

Alternative would be to write a VBA-macro that does this for you.

Jochem
  • 3,295
  • 4
  • 30
  • 55