Yes that was it, i had it set to text. Thought i was being clever justifying
the column but obviously not. Thanks again!

"T. Valko" wrote:

If the formula is displayed rather than the result of the formula:

1. Make sure the cell isn't formatted TEXT. Select the cell with the
formula. Goto the menu FormatCells. On the Number tab select General and OK
out. With the formula cell still selected hit function key F2 then hit
Enter. This will re-register the formula with the correct format. If that
doesn't work...

2. Make sure you're not in View Formula Mode. Goto the menu
ToolsOptionsView tabin the Window Options group, uncheck FormulasOK

--
Biff
Microsoft Excel MVP


"DaniMa" wrote in message
...
Ok, now something strange is going on. The formula worked perfectly on the
day i copied it across, and i changed the cell range to the ones i wanted.
Now, three days later, whenever i change the cell range (eg H37:Z37 for
all
three sums) the formula is displayed, rather than a result. I have tried
hide
all and show all formulae, and shift+ctrl+enter.

Any suggestions?

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"DaniMa" wrote in message
...
Thankyou thankyou thankyou that works perfectly. (I did want the max
non-dash
entry - should have written that more clearly!)

Cheers!

"T. Valko" wrote:

Assuming there won't be any rows that contain both text and numbers as
is
demonstrated in your sample data.

This will return the "max" entry excluding the dashes:

=IF(COUNT(A2:F2),MAX(A2:F2),LOOKUP(2,1/((COUNTIF(A2:F2,""&A2:F2)=0)*(A2:F2<"-")),A2:F2))

Or, are you wanting the rightmost entry that isn't a dash? In your
sample
data the rightmost non-dash entry also happens to be the "max" entry.
If
that's what you want:

=LOOKUP(2,1/((A2:F2<"")*(A2:F2<"-")),A2:F2)

This will work if there are both text and numbers on the same row.

--
Biff
Microsoft Excel MVP


"DaniMa" wrote in message
...
I am trying to create a formula so that i can have one column which
displays
the highest number or letter in any given row.
eg
1 - 2 - - 3
- - A B C -
- 1 - - -
2
etc.
Some cells are left blank (marked by dashes above). I would like a
column
which displays the highest ranking (so 3 for the first row, C for
the
second
row and 2 for the third row) and automatically updates when new
columns
are
added.
Anyone know if this can be done?