I am trying to collect data from a darts team and these values reflect
the number of points won in a team game (ranging from 0 - 3). This will
probably be simple for the experts here, so I'll let fly:

I have a table with six names across the top. In the rows below there
will always be two cells that have number values ranging from 0 - 3
while the rest will be blank. Those two numbers however, will always be
the same. I am trying to write a formula that will determine which
columns have values and return a single letter value (first initial)
for each name. Since all the names are different that is not an issue.

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

The intended results for the first row would be "WS" and "RM" for the
second row. I have been trying IF() constructions but am now stymied.

Hi!

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

Assume that table is in the range A1:G3. Names are in B1:G1

Enter this formula in H2 and copy down as needed:

=IF(COUNT(B2:G2)<2,"",LEFT(INDEX(B$1:G$1,MATCH(MIN (B2:G2),B2:G2,0)))&LEFT(LOOKUP(4,B2:G2,B$1:G$1)))

Biff

"Lorne Oliver" wrote in message
ps.com...
I am trying to collect data from a darts team and these values reflect
the number of points won in a team game (ranging from 0 - 3). This will
probably be simple for the experts here, so I'll let fly:

I have a table with six names across the top. In the rows below there
will always be two cells that have number values ranging from 0 - 3
while the rest will be blank. Those two numbers however, will always be
the same. I am trying to write a formula that will determine which
columns have values and return a single letter value (first initial)
for each name. Since all the names are different that is not an issue.

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

The intended results for the first row would be "WS" and "RM" for the
second row. I have been trying IF() constructions but am now stymied.

Thanks Biff

I adjusted the ranges to fit the actual ones used but I end up with
#N/A error in the MIN() if I am reading the step-by-step calculation
correctly. I think I see what you are doing here though and that has
given me some ideas to work with. I could simplify things by just using
the initials in the header row anyway.

The actual table looks like this:
Games Rodney Welles Scott Mina Barry Lorne
Singles 3 1 2 0
Wk 1 Dbl -01 0 0
Dbl Cr 0 0
Singles
Wk 2 Dbl -01
Dbl Cr
Despite obvious references to how badly I play darts, does this help
with clearing up the errors?

Data ranges from C2:H4 for one week with the names in C1:H1. The first
row of data for each week is not relevant to this problem, only the
second and third rows for each week.

Biff wrote:
Hi!

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

Assume that table is in the range A1:G3. Names are in B1:G1

Enter this formula in H2 and copy down as needed:

=IF(COUNT(B2:G2)<2,"",LEFT(INDEX(B$1:G$1,MATCH(MIN (B2:G2),B2:G2,0)))&LEFT(LOOKUP(4,B2:G2,B$1:G$1)))

Biff

"Lorne Oliver" wrote in message
ps.com...
I am trying to collect data from a darts team and these values reflect
the number of points won in a team game (ranging from 0 - 3). This will
probably be simple for the experts here, so I'll let fly:

I have a table with six names across the top. In the rows below there
will always be two cells that have number values ranging from 0 - 3
while the rest will be blank. Those two numbers however, will always be
the same. I am trying to write a formula that will determine which
columns have values and return a single letter value (first initial)
for each name. Since all the names are different that is not an issue.

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

The intended results for the first row would be "WS" and "RM" for the
second row. I have been trying IF() constructions but am now stymied.

Thanks for the tips Bill... I've solved it now. The formula looks like
this:

=IF(COUNT(C6:H6)<2,"",LEFT(INDEX($C$1:$H$1,MATCH(0 ,C6:H6,0)))&LEFT(INDEX($C$1:$H$1,MATCH(0,C6:H6,1)) ))

The key for me was useing & in functions. I had never done that before.

Lorne

Lorne Oliver wrote:
Thanks Biff

I adjusted the ranges to fit the actual ones used but I end up with
#N/A error in the MIN() if I am reading the step-by-step calculation
correctly. I think I see what you are doing here though and that has
given me some ideas to work with. I could simplify things by just using
the initials in the header row anyway.

The actual table looks like this:
Games Rodney Welles Scott Mina Barry Lorne
Singles 3 1 2 0
Wk 1 Dbl -01 0 0
Dbl Cr 0 0
Singles
Wk 2 Dbl -01
Dbl Cr
Despite obvious references to how badly I play darts, does this help
with clearing up the errors?

Data ranges from C2:H4 for one week with the names in C1:H1. The first
row of data for each week is not relevant to this problem, only the
second and third rows for each week.

Biff wrote:
Hi!

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

Assume that table is in the range A1:G3. Names are in B1:G1

Enter this formula in H2 and copy down as needed:

=IF(COUNT(B2:G2)<2,"",LEFT(INDEX(B$1:G$1,MATCH(MIN (B2:G2),B2:G2,0)))&LEFT(LOOKUP(4,B2:G2,B$1:G$1)))

Biff

"Lorne Oliver" wrote in message
ps.com...
I am trying to collect data from a darts team and these values reflect
the number of points won in a team game (ranging from 0 - 3). This will
probably be simple for the experts here, so I'll let fly:

I have a table with six names across the top. In the rows below there
will always be two cells that have number values ranging from 0 - 3
while the rest will be blank. Those two numbers however, will always be
the same. I am trying to write a formula that will determine which
columns have values and return a single letter value (first initial)
for each name. Since all the names are different that is not an issue.

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

The intended results for the first row would be "WS" and "RM" for the
second row. I have been trying IF() constructions but am now stymied.

Thanks for the tips Bill... I've solved it now. The formula looks like
this:

=IF(COUNT(C6:H6)<2,"",LEFT(INDEX($C$1:$H$1,MATCH(0 ,C6:H6,0)))&LEFT(INDEX($C$1:$H$1,MATCH(0,C6:H6,1)) ))

The key for me was useing & in functions. I had never done that before.

Lorne

Lorne Oliver wrote:
Thanks Biff

I adjusted the ranges to fit the actual ones used but I end up with
#N/A error in the MIN() if I am reading the step-by-step calculation
correctly. I think I see what you are doing here though and that has
given me some ideas to work with. I could simplify things by just using
the initials in the header row anyway.

The actual table looks like this:
Games Rodney Welles Scott Mina Barry Lorne
Singles 3 1 2 0
Wk 1 Dbl -01 0 0
Dbl Cr 0 0
Singles
Wk 2 Dbl -01
Dbl Cr
Despite obvious references to how badly I play darts, does this help
with clearing up the errors?

Data ranges from C2:H4 for one week with the names in C1:H1. The first
row of data for each week is not relevant to this problem, only the
second and third rows for each week.

Biff wrote:
Hi!

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

Assume that table is in the range A1:G3. Names are in B1:G1

Enter this formula in H2 and copy down as needed:

=IF(COUNT(B2:G2)<2,"",LEFT(INDEX(B$1:G$1,MATCH(MIN (B2:G2),B2:G2,0)))&LEFT(LOOKUP(4,B2:G2,B$1:G$1)))

Biff

"Lorne Oliver" wrote in message
ps.com...
I am trying to collect data from a darts team and these values reflect
the number of points won in a team game (ranging from 0 - 3). This will
probably be simple for the experts here, so I'll let fly:

I have a table with six names across the top. In the rows below there
will always be two cells that have number values ranging from 0 - 3
while the rest will be blank. Those two numbers however, will always be
the same. I am trying to write a formula that will determine which
columns have values and return a single letter value (first initial)
for each name. Since all the names are different that is not an issue.

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

The intended results for the first row would be "WS" and "RM" for the
second row. I have been trying IF() constructions but am now stymied.

Hi!

I'll be darned if I can see why you would get #N/A.

MIN wouldn't return #N/A but MATCH *could*. If the values entered were
really TEXT numbers then MIN would return 0 and if there wasn't a 0 in the
range then MATCH would return #N/A. However, I have that accounted for using
the IF(COUNT(......). LOOKUP could also return #N/A but that's also covered
in the IF(COUNT.

3 1 2 0

Hmmm......that doesn't "look" anything like:


In the rows below there will always be **two cells** that have
number values ranging from 0 - 3 while the rest will be blank.
Those **two numbers** however, **will always be the same.**

Unless 3 1 2 0 is not part of the data. But even if it was, it still
wouldn't cause an error although the result would be incorrect.

Here's a screencap:

http://img153.imageshack.us/img153/125/sample8ys.jpg

Biff

"Lorne Oliver" wrote in message
oups.com...
Thanks Biff

I adjusted the ranges to fit the actual ones used but I end up with
#N/A error in the MIN() if I am reading the step-by-step calculation
correctly. I think I see what you are doing here though and that has
given me some ideas to work with. I could simplify things by just using
the initials in the header row anyway.

The actual table looks like this:
Games Rodney Welles Scott Mina Barry Lorne
Singles 3 1 2 0
Wk 1 Dbl -01 0 0
Dbl Cr 0 0
Singles
Wk 2 Dbl -01
Dbl Cr
Despite obvious references to how badly I play darts, does this help
with clearing up the errors?

Data ranges from C2:H4 for one week with the names in C1:H1. The first
row of data for each week is not relevant to this problem, only the
second and third rows for each week.

Biff wrote:
Hi!

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

Assume that table is in the range A1:G3. Names are in B1:G1

Enter this formula in H2 and copy down as needed:

=IF(COUNT(B2:G2)<2,"",LEFT(INDEX(B$1:G$1,MATCH(MIN (B2:G2),B2:G2,0)))&LEFT(LOOKUP(4,B2:G2,B$1:G$1)))

Biff

"Lorne Oliver" wrote in message
ps.com...
I am trying to collect data from a darts team and these values reflect
the number of points won in a team game (ranging from 0 - 3). This will
probably be simple for the experts here, so I'll let fly:

I have a table with six names across the top. In the rows below there
will always be two cells that have number values ranging from 0 - 3
while the rest will be blank. Those two numbers however, will always be
the same. I am trying to write a formula that will determine which
columns have values and return a single letter value (first initial)
for each name. Since all the names are different that is not an issue.

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

The intended results for the first row would be "WS" and "RM" for the
second row. I have been trying IF() constructions but am now stymied.

The First data row, marked "singles" is not used in this situation so
it can be ignored. In the end I got the results I wanted and learned a
thing or three here, so Kudus to you for that.

Always good to learn something new.

Biff wrote:
Hi!

I'll be darned if I can see why you would get #N/A.

MIN wouldn't return #N/A but MATCH *could*. If the values entered were
really TEXT numbers then MIN would return 0 and if there wasn't a 0 in the
range then MATCH would return #N/A. However, I have that accounted for using
the IF(COUNT(......). LOOKUP could also return #N/A but that's also covered
in the IF(COUNT.

3 1 2 0

Hmmm......that doesn't "look" anything like:


In the rows below there will always be **two cells** that have
number values ranging from 0 - 3 while the rest will be blank.
Those **two numbers** however, **will always be the same.**

Unless 3 1 2 0 is not part of the data. But even if it was, it still
wouldn't cause an error although the result would be incorrect.

Here's a screencap:

http://img153.imageshack.us/img153/125/sample8ys.jpg

Biff

"Lorne Oliver" wrote in message
oups.com...
Thanks Biff

I adjusted the ranges to fit the actual ones used but I end up with
#N/A error in the MIN() if I am reading the step-by-step calculation
correctly. I think I see what you are doing here though and that has
given me some ideas to work with. I could simplify things by just using
the initials in the header row anyway.

The actual table looks like this:
Games Rodney Welles Scott Mina Barry Lorne
Singles 3 1 2 0
Wk 1 Dbl -01 0 0
Dbl Cr 0 0
Singles
Wk 2 Dbl -01
Dbl Cr
Despite obvious references to how badly I play darts, does this help
with clearing up the errors?

Data ranges from C2:H4 for one week with the names in C1:H1. The first
row of data for each week is not relevant to this problem, only the
second and third rows for each week.

Biff wrote:
Hi!

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

Assume that table is in the range A1:G3. Names are in B1:G1

Enter this formula in H2 and copy down as needed:

=IF(COUNT(B2:G2)<2,"",LEFT(INDEX(B$1:G$1,MATCH(MIN (B2:G2),B2:G2,0)))&LEFT(LOOKUP(4,B2:G2,B$1:G$1)))

Biff

"Lorne Oliver" wrote in message
ps.com...
I am trying to collect data from a darts team and these values reflect
the number of points won in a team game (ranging from 0 - 3). This will
probably be simple for the experts here, so I'll let fly:

I have a table with six names across the top. In the rows below there
will always be two cells that have number values ranging from 0 - 3
while the rest will be blank. Those two numbers however, will always be
the same. I am trying to write a formula that will determine which
columns have values and return a single letter value (first initial)
for each name. Since all the names are different that is not an issue.

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

The intended results for the first row would be "WS" and "RM" for the
second row. I have been trying IF() constructions but am now stymied.

Good deal. Throw 'em straight!

Biff

"Lorne Oliver" wrote in message
ups.com...
The First data row, marked "singles" is not used in this situation so
it can be ignored. In the end I got the results I wanted and learned a
thing or three here, so Kudus to you for that.

Always good to learn something new.

Biff wrote:
Hi!

I'll be darned if I can see why you would get #N/A.

MIN wouldn't return #N/A but MATCH *could*. If the values entered were
really TEXT numbers then MIN would return 0 and if there wasn't a 0 in
the
range then MATCH would return #N/A. However, I have that accounted for
using
the IF(COUNT(......). LOOKUP could also return #N/A but that's also
covered
in the IF(COUNT.

3 1 2 0

Hmmm......that doesn't "look" anything like:


In the rows below there will always be **two cells** that have
number values ranging from 0 - 3 while the rest will be blank.
Those **two numbers** however, **will always be the same.**

Unless 3 1 2 0 is not part of the data. But even if it was, it still
wouldn't cause an error although the result would be incorrect.

Here's a screencap:

http://img153.imageshack.us/img153/125/sample8ys.jpg

Biff

"Lorne Oliver" wrote in message
oups.com...
Thanks Biff

I adjusted the ranges to fit the actual ones used but I end up with
#N/A error in the MIN() if I am reading the step-by-step calculation
correctly. I think I see what you are doing here though and that has
given me some ideas to work with. I could simplify things by just using
the initials in the header row anyway.

The actual table looks like this:
Games Rodney Welles Scott Mina Barry Lorne
Singles 3 1 2 0
Wk 1 Dbl -01 0 0
Dbl Cr 0 0
Singles
Wk 2 Dbl -01
Dbl Cr
Despite obvious references to how badly I play darts, does this help
with clearing up the errors?

Data ranges from C2:H4 for one week with the names in C1:H1. The first
row of data for each week is not relevant to this problem, only the
second and third rows for each week.

Biff wrote:
Hi!

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

Assume that table is in the range A1:G3. Names are in B1:G1

Enter this formula in H2 and copy down as needed:

=IF(COUNT(B2:G2)<2,"",LEFT(INDEX(B$1:G$1,MATCH(MIN (B2:G2),B2:G2,0)))&LEFT(LOOKUP(4,B2:G2,B$1:G$1)))

Biff

"Lorne Oliver" wrote in message
ps.com...
I am trying to collect data from a darts team and these values
reflect
the number of points won in a team game (ranging from 0 - 3). This
will
probably be simple for the experts here, so I'll let fly:

I have a table with six names across the top. In the rows below
there
will always be two cells that have number values ranging from 0 - 3
while the rest will be blank. Those two numbers however, will always
be
the same. I am trying to write a formula that will determine which
columns have values and return a single letter value (first initial)
for each name. Since all the names are different that is not an
issue.

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

The intended results for the first row would be "WS" and "RM" for
the
second row. I have been trying IF() constructions but am now
stymied.

Biff,
I have come across a strange problem. It all seems to work properly but
for three rather wacky combinations:
Scott/Barry and Scott/Lorne both produce SS as a result
Mina/Lorne produces MM as a result.

Any ideas on why? Or how to fix it?
Lorne




Biff wrote:
Good deal. Throw 'em straight!

Biff

"Lorne Oliver" wrote in message
ups.com...
The First data row, marked "singles" is not used in this situation so
it can be ignored. In the end I got the results I wanted and learned a
thing or three here, so Kudus to you for that.

Always good to learn something new.

Biff wrote:
Hi!

I'll be darned if I can see why you would get #N/A.

MIN wouldn't return #N/A but MATCH *could*. If the values entered were
really TEXT numbers then MIN would return 0 and if there wasn't a 0 in
the
range then MATCH would return #N/A. However, I have that accounted for
using
the IF(COUNT(......). LOOKUP could also return #N/A but that's also
covered
in the IF(COUNT.

3 1 2 0

Hmmm......that doesn't "look" anything like:


In the rows below there will always be **two cells** that have
number values ranging from 0 - 3 while the rest will be blank.
Those **two numbers** however, **will always be the same.**

Unless 3 1 2 0 is not part of the data. But even if it was, it still
wouldn't cause an error although the result would be incorrect.

Here's a screencap:

http://img153.imageshack.us/img153/125/sample8ys.jpg

Biff

"Lorne Oliver" wrote in message
oups.com...
Thanks Biff

I adjusted the ranges to fit the actual ones used but I end up with
#N/A error in the MIN() if I am reading the step-by-step calculation
correctly. I think I see what you are doing here though and that has
given me some ideas to work with. I could simplify things by just using
the initials in the header row anyway.

The actual table looks like this:
Games Rodney Welles Scott Mina Barry Lorne
Singles 3 1 2 0
Wk 1 Dbl -01 0 0
Dbl Cr 0 0
Singles
Wk 2 Dbl -01
Dbl Cr
Despite obvious references to how badly I play darts, does this help
with clearing up the errors?

Data ranges from C2:H4 for one week with the names in C1:H1. The first
row of data for each week is not relevant to this problem, only the
second and third rows for each week.

Biff wrote:
Hi!

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

Assume that table is in the range A1:G3. Names are in B1:G1

Enter this formula in H2 and copy down as needed:

=IF(COUNT(B2:G2)<2,"",LEFT(INDEX(B$1:G$1,MATCH(MIN (B2:G2),B2:G2,0)))&LEFT(LOOKUP(4,B2:G2,B$1:G$1)))

Biff

"Lorne Oliver" wrote in message
ps.com...
I am trying to collect data from a darts team and these values
reflect
the number of points won in a team game (ranging from 0 - 3). This
will
probably be simple for the experts here, so I'll let fly:

I have a table with six names across the top. In the rows below
there
will always be two cells that have number values ranging from 0 - 3
while the rest will be blank. Those two numbers however, will always
be
the same. I am trying to write a formula that will determine which
columns have values and return a single letter value (first initial)
for each name. Since all the names are different that is not an
issue.

ex: Rodney Welles Scott Mina Barry Lorne
DBL -01 1 1
DBL -Cr 3 3

The intended results for the first row would be "WS" and "RM" for
the
second row. I have been trying IF() constructions but am now
stymied.

"Lorne Oliver" wrote in message
oups.com...
Biff,
I have come across a strange problem. It all seems to work properly but
for three rather wacky combinations:
Scott/Barry and Scott/Lorne both produce SS as a result
Mina/Lorne produces MM as a result.

Any ideas on why? Or how to fix it?

Yeah, send me a copy of the file and I'll fix it! I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff