find largest value in a row

V

vicki.wells

I have a spreadsheet with bridge component ratings in three columns. I
need to find the largest of the three and have it put into another
column. I've tried to use INDEX and MATCH, but can't quite get it to
work. All the examples I've found have had slightly different end
goals, and I'm not adjusting the formula correctly. Can someone help?
(I tried to include a sample table, but how do you get it to line up
correctly?)
 
H

HS Hartkamp

Do you mean to find the largest of 3 values and put that into the adjacent
column ?

Try MAX(B2:D2).

Bas Hartkamp.
 
V

vicki.wells

Yes, when I use MAX by itself I get 0. Does it matter if some values
are the same?
 
M

Mark Lincoln

Let's say you're using columns A through C to store your ratings. If
the numbers in row 2 show as 10, 20 and 30, respectively, and
MAX(A2:C2) is zero, then the cells containing your ratings are probably
formatted as Text. MAX works with numbers and ignores text. You would
need to format the cells as either General or a number format.
 
R

Ron Rosenfeld

Let's say you're using columns A through C to store your ratings. If
the numbers in row 2 show as 10, 20 and 30, respectively, and
MAX(A2:C2) is zero, then the cells containing your ratings are probably
formatted as Text. MAX works with numbers and ignores text. You would
need to format the cells as either General or a number format.
Click to expand...

You're correct in your analysis, but to make it absolutely clear to the OP, I
should point out that merely changing the format will NOT change the nature of
the values that have previously been entered. The format needs to General or
Number PRIOR to entering the values.

Other reasons for the values being seen as text include formulas that
explicitly enclose their results in quotes. For example:

=IF(A1="xyz","3","2")

If that is the cause, the formula will need to be rewritten:

=IF(A1="xyz",3,2)


--ron
 
V

vicki.wells

I'm still getting 0. The source of the data is from Access, if that
makes a difference. I've both copy/pasted and exported the data from
there because Access won't do the MAX function across a row (at least I
don't think it will)and the Access forum suggested doing it in Excel. I
checked the number formats in Access and Excel before and after
exporting, and they're OK. The numbers are very simple, 1-10. Any more
ideas?
 
R

Ron Rosenfeld

I'm still getting 0. The source of the data is from Access, if that
makes a difference. I've both copy/pasted and exported the data from
there because Access won't do the MAX function across a row (at least I
don't think it will)and the Access forum suggested doing it in Excel. I
checked the number formats in Access and Excel before and after
exporting, and they're OK. The numbers are very simple, 1-10. Any more
ideas?
Click to expand...

If they are numbers and not the results of formulas, and you are getting a zero
result, then they are likely textual representations of numbers.

You can check this with the ISTEXT function in Excel, using the cell reference
of the numbers as the argument.

e.g. if your data is in A1:K1, then

A2: =istext(a1)
copy/drag across to K2 and see what you get.


--ron
 
M

Mark Lincoln

Ron said:
You're correct in your analysis,
but to make it absolutely clear to
the OP, I should point out that merely
changing the format will NOT change
the nature of the values that have
previously been entered. [...]
Click to expand...

I keep forgetting about that, and I shouldn't because the topic comes
up often enough on the lists. But I (ahem) never make that kind of
mistake. (Yeah, right!) Thanks for reminding me.
 
H

HS Hartkamp

True that Access doesn't do MAX within a row, but since you only have three
values to compare, I'd do it in Access with the IIF function:

HighestValue =
iif([col1]>[col2];iif([col1]>col3;[col1];[col3]);(iif[col2]>col3;[col2];[col3]))

as one of the columns of your query.

Bas Hartkamp.
 
Top