I must be making a very moronic mistake here. ;)
Please tell me how the final formula listed contains an error.

If the following simple formula returns *W*
=P12

And the following *-LOOKUP-* formula -also- returns *W*
*=VLOOKUP(C53,Schedule_1,14)* <<(since this formula
-points- to cell p12)

And -*THIS*- formula works:
*=CONCATENATE(COUNTIF(P12,"W"),"-",COUNTIF(P12,"L"))*
<<(returning *1-0*)

Then why does Excel tell me that -*THIS*- formula contains an error?::

*=CONCATENATE(COUNTIF((VLOOKUP(C53,Schedule_1,14)) ,"W"),"-",COUNTIF((VLOOKUP(C53,Schedule_1,14)),"L"))*


--
HuskerBronco
------------------------------------------------------------------------
HuskerBronco's Profile: http://www.excelforum.com/member.php...fo&userid=4803
View this thread: http://www.excelforum.com/showthread...hreadid=559396

=IF(ISNA(VLOOKUP(C53,Schedule_1,14)),0,IF(VLOOKUP( C53,Schedule_1,14)="W",1,0
))&"-"&
IF(ISNA(VLOOKUP(C53,Schedule_1,14)),0,IF(VLOOKUP(C 53,Schedule_1,14)="L",1,0)
)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"HuskerBronco"
wrote in message
news:HuskerBronco.2al1yo_1152288608.4699@excelforu m-nospam.com...

I must be making a very moronic mistake here. ;)
Please tell me how the final formula listed contains an error.

If the following simple formula returns *W*
=P12

And the following *-LOOKUP-* formula -also- returns *W*
*=VLOOKUP(C53,Schedule_1,14)* <<(since this formula
-points- to cell p12)

And -*THIS*- formula works:
*=CONCATENATE(COUNTIF(P12,"W"),"-",COUNTIF(P12,"L"))*
<<(returning *1-0*)

Then why does Excel tell me that -*THIS*- formula contains an error?::


*=CONCATENATE(COUNTIF((VLOOKUP(C53,Schedule_1,14)) ,"W"),"-",COUNTIF((VLOOKUP
(C53,Schedule_1,14)),"L"))*


--
HuskerBronco
------------------------------------------------------------------------
HuskerBronco's Profile:
http://www.excelforum.com/member.php...fo&userid=4803
View this thread: http://www.excelforum.com/showthread...hreadid=559396

HuskerBronco wrote:
Then why does Excel tell me that -*THIS*- formula contains an error?::

*=CONCATENATE(COUNTIF((VLOOKUP(C53,Schedule_1,14)) ,"W"),"-",COUNTIF((VLOOKUP(C53,Schedule_1,14)),"L"))*

Because you have asterisks in the formula :-) :-). Just kidding! But
please stop the "moronic" use of asterisks to provide emphasis or to
quote. It is confusing when you are asking about potential syntax
questions because an asterisk has a specific function in some Excel
formulas

Seriously, Excel probably complains because the 1st parameter of
COUNTIF() should be a range. VLOOKUP returns a value, not a range.

HuskerBronco wrote...
....
If the following simple formula returns *W*
=P12

And the following *-LOOKUP-* formula -also- returns *W*
*=VLOOKUP(C53,Schedule_1,14)* <<(since this formula
-points- to cell p12)

No, it evaluates to the value of P12. It's not a reference to P12.

And -*THIS*- formula works:
*=CONCATENATE(COUNTIF(P12,"W"),"-",COUNTIF(P12,"L"))*
<<(returning *1-0*)

So this formula could return "1-0", "0-1" or "0-0" (the last of P12 is
neither W or L)? Far easier to use the following instead.

=CHOOSE(1+SUMPRODUCT((P12={"W";"L"})*{1;2}),"0-0","1-0","0-1")

Then why does Excel tell me that -*THIS*- formula contains an error?::

*=CONCATENATE(COUNTIF((VLOOKUP(C53,Schedule_1,14) ),"W"),"-",
COUNTIF((VLOOKUP(C53,Schedule_1,14)),"L"))*

Because COUNTIF only accepts single area range references as 1st
argument. To use VLOOKUP in this, try

=IF(NOT(ISNA(VLOOKUP(C53,Schedule_1,1))),
CHOOSE(1+SUM((VLOOKUP(C53,Schedule_1,14)={"W";"L"} )*{1;2}),"0-0","1-0","0-1"),
"'"&C53&"' not found")

Thanks, so much, everybody for your quick response to my query.

I am VERY new to creating my own formulas, and it never occured to me
to use the ISNA function in this particular formula. Thanks, Bob.

And Harlan, the suggestion of using CHOOSE(1+SUM....) and
CHOOSE(1+SUMPRODUCT....) is also greatly appreciated. Those are
functions that I have no experience with, but I see how they can be
very helpful. There are other areas in my worksheet where they will
come in handy.

As for the asterisks, Joe...I don't know where they came from. I
didn't put them in my original post. I would like to ask a question
about something in your response, though. I understand when you say
that "the 1st parameter of COUNTIF() should be a range"....but why,
then, did the COUNTIF function work properly when I used the CELL
REFERENCE alone, as in the formula stated in my post?

Thanks, again, for your input and expertise.

=CONCATENATE(COUNTIF(P12,"W"),"-",COUNTIF(P12,"L"))


--
HuskerBronco
------------------------------------------------------------------------
HuskerBronco's Profile: http://www.excelforum.com/member.php...fo&userid=4803
View this thread: http://www.excelforum.com/showthread...hreadid=559396

HuskerBronco wrote:
As for the asterisks, Joe...I don't know where they came from. I
didn't put them in my original post.

Interesting. I suspect it is a function of your news reader ("writer")
and/or the format that you choose for posting (perhaps HTML instead of
plain text?). I no longer have the ability to look at the "raw" text
of your posting to see what you might have done. Suffice it to say:
try to avoid whatever it is that you did that caused the asterisks to
be inserted.

I understand when you say
that "the 1st parameter of COUNTIF() should be a range"....but why,
then, did the COUNTIF function work properly when I used the CELL
REFERENCE alone, as in the formula stated in my post?

A cell reference is a range of one cell. P12 is equivalent to the
range P12:P12. (But you cannot use P12:P12 where a single value is
expected.) VLOOKUP() returns a value, not a cell reference.

wrote...
HuskerBronco wrote:
As for the asterisks, Joe...I don't know where they came from. I
didn't put them in my original post.

Interesting. I suspect it is a function of your news reader ("writer")
and/or the format that you choose for posting (perhaps HTML instead of
plain text?). . . .
....

OP posts through the ExcelForum portal which provides HTML formatting
and HTML quoting for messages viewed therein - a bad idea. When
ExcelForum propagates messages to USENET (so to the
microsoft.public.excel* newsgroups), it converts its HTML formatting to
plain text. The OP has no control over that. And not too likely
ExcelForum's admin will think this is something broken in need of
fixing. In this sense, Google Groups is a better portal.

. . . (But you cannot use P12:P12 where a single value is expected.)
....

Not true. The formulas

=VLOOKUP(P12,{"";"bar";"foo"},1)

and

=VLOOKUP(P12:P12,{"";"bar";"foo"},1)

produce identical results. In Excel P12 and P12:P12 are semantically
identical.

Harlan Grove wrote:
wrote...
. . . (But you cannot use P12:P12 where a single value is expected.)

Not true. [....] In Excel P12 and P12:P12 are semantically identical.

Well, that is certainly what I would like. But I tried P12:P12 in
__some__ context where a single value is expected -- e.g. an arithmetic
expression -- and I thought it failed. However, when I tried it again
just now, it does indeed work -- at least in all the cases I have tried
so far. (I am not trying very hard, since I trust Harlan's insight.)
That is good. IMHO, there is no reason why P12:P12 should not be
treated the same as P12 when a single value is expected. On the other
hand, I can imagine some parsing algorithm taking a short-cut and
refusing a range out-of-hand without seeing if it is single-valued. So
I would not surprise me if there are exceptions to the rule, albeit
undesirable.