Is IF() Conditional the way to do this?

[Lorne Oliver]

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]

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]

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.

 

[Lorne Oliver]

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]

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

 

[Biff]

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]

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]

Good deal. Throw 'em straight!

Biff

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.

 

[Lorne Oliver]

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]

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