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?

In column A "=MAX(B1:IV1)" and copy down.


--
mdmackillop
------------------------------------------------------------------------
mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61293

One play, along these thoughts
-use CODE to base convert source data in each row range to numbers
- use MAX to grab the highest number in the row range (rowR), excluding code
45 (real dashes)
- use MATCH(MAX(rowR),rowR,0) to get the relative position num where the MAX
is
- use INDEX(rowR,MATCH(...)) to return the desired item in rowR

Insert a new col A for the purpose. Data as posted is now assumed in B2:G2
down, where the dashes are assumed to be real dashes (code 45)

Place in A2, array-enter, ie confirm the formula by pressing
CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=INDEX(B2:G2,MATCH(MAX(IF(CODE(B2:G2)<45,CODE(B2: G2))),IF(CODE(B2:G2)<45,CODE(B2:G2)),0))
Copy A2 down to return required results
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"DaniMa" wrote:
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?

In column A "=MAX(B1:IV1)" and copy down.

Trouble is, that simply returns a zero instead of the desired "C" for the
2nd row

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

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?

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?

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?

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?

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?

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?