Excel Returning 0 Values using =MAX

[Barking_Mad]

Hi,

I have a row of referenced cells (from another worksheet) that i am trying to find the maximum value of. Unfortunately the =MAX value is returning a 0 value.

The row of referenced cells are the result of a formula which is as follows:

=MAX(E6:E30)&REPT("*",ISNUMBER(MATCH(MAX(E6:E30)&"~*",INDEX(E6:E30&F6:F30,0),0)))

I suspect that the use of this formula is the reason that the 0 value is being returned. Can anyone help?

Thanks

[DonkeyOte]

The values being referenced are Text not Numbers, given the above you would want to use something like:

=SUBSTITUTE(MAX((0&SUBSTITUTE(Sheet1!$A$1:$F$1,"*",".1"))+0)&"",".1","*")
confirmed with CTRL + SHIFT + ENTER enter alone will not suffice

where Sheet1!A1:F1 is your row of batting scores

this should ensure Not Out's (*) get a higher value than the same score when dismissed and that where the Max result is a not out it is denoted as such (*)

[Barking_Mad]

Cheers (as always) DonkeyOte! Works perfectly.