Maxa

[DTrain9481]

I checked up on some older threads that dealt with MAX and MAXA, and I know MAXA is the way to go for my application.

In my excel sheet, I have a cell that will impact which column in that row has a value. My hopes was to use MAXA to obtain the value so I didn't have to scroll to see which column had the value. Here's how I did it:

=MAXA(A13:A21)

Relatively straight forward....however it keeps returning 0 instead of the text listed in the box.

Any ideas?

[Andy Pope]

can you post workbook example and tell us what you think the formula should return

[DTrain9481]

I've attached an example workbook.

Now, with =MAXA(D4:L4), I expect to see a value of 14-6 returned, but I get 0. Any ideas?

one little caveat...I'm just using a static example here, but depending on what is in the score column, the potential of the values in D4:L4 can change, but the MAXA function should be able to deal with it.

[Andy Pope]

the function can deal with the text but not in the way you are thinking.

So if the values are text representing numbers then you would get 3 returned.
=MAXA("2","3","1")

But you have 2 problems. The help on the function says,

If an argument is an array or reference, only values in that array or reference are used. Empty cells and text values in the array or reference are ignored.

you have a reference so text is ignored.

secondly your cell content is not a number. 14-6 is not a number.

If you used 14.6 then you could use MAX

[DTrain9481]

Andy, I was actually playing around with the idea as well, but I had another thought. Instead of having "14-6", I am just going to use the number 14 (and no quotes) and using the max.

If the addition of both numbers should be 20, I can just concatenate the value I get (14), a " - " and 20 - the number, getting me 6.

I was so into this and letting it rack my brain for so long, I went for a ride once I figured it out and noticed you typed an update.

Thank you for all the help!