Why does Excel say this formula contains an error?

[HuskerBronco]

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"))*

 

[Bob Phillips]

=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(C53,Schedule_1,14)="L",1,0)
)

--
HTH

Bob Phillips

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

"HuskerBronco" <[email protected]>
wrote in message
news:[email protected]...

 

[joeu2004]

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.

 

[Harlan Grove]

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")

 

[HuskerBronco]

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"))

 

[joeu2004]

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 P1212. (But you cannot use P1212 where a single value is
expected.) VLOOKUP() returns a value, not a cell reference.

 

[Harlan Grove]

(e-mail address removed) wrote...

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 P1212 where a single value is expected.)

....

Not true. The formulas

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

and

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

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

 

[joeu2004]

(e-mail address removed) wrote...

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

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

Well, that is certainly what I would like. But I tried P1212 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 P1212 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.

 

[HuskerBronco]

Thanks, so much for all of the information. I am absolutely amazed a
the scope of the Excel program, especially since I, personally, haven'
even scratched the surface of what I can do with this amazing program.

I hope that you haven't abandoned this thread, since I have anothe
question. The formula above was an attempt to produce a WIN-LOSS coun
for a specific team. (There is no possibility for a TIE, sinc
overtimes are played until a winner is determined).

Now, for each game on a team's page, I have a summary box. This summar
box compiles data about THAT particular game. (i.e.: Final score
opponent, game stats, HOME or AWAY, etc...). The top right cell i
this summary box is for the WIN-LOSS record for THAT WEEK ONLY. Thi
is the cell that I needed the formula above to calculate data for.
And, to a certain extent, I can use the formula that you have shown m
to make this calculation...with three major shortfalls.

First, until a final score is entered for a specific game, I would lik
that cell to show the WIN-LOSS record from the PREVIOUS game. (No
0-0...unless, of course, it's the cell in the summary box for Game 1).

Second, after each score is entered, and the current WIN-LOSS recor
appears in the proper cell, I don't ever want it to change. I want th
WIN-LOSS record to reflect ONLY up to THAT PARTICULAR GAME. When futur
scores are entered, PAST game summaries SHOULD NOT be recalculated.

And, third...as stated earlier, I CAN use the formula you'v
constructed to achieve this. (Although, a slightly modified version).
But, then I have to modify the formula for EACH SUMMARY BOX.

So, here's my question:

Is there a formula that I can enter into the FIRST WEEK's summary bo
that can be COPY/PASTED into the other summary boxes withou
modification, (other than the cell reference adjustments that Exce
makes automatically)??

I hope the answer is YES. And, again, thanks a million for taking tim
to read and respond to this thread. I am quickly discovering that th
books I've purchased on Excel and Excel formulas might explain eac
function adequately, but it's often difficult to incorporate THEI
examples into my own formulas.

(I should probably mention that the WIN-LOSS information is to b
obtained from a RANGE (P1225) within a chart named Schedule_
(C12:R25). Column C12:C25 in Schedule_1 contains the numerical marke
for each game.

 

[HuskerBronco]

Thanks, so much for all of the information. I am absolutely amazed a
the scope of the Excel program, especially since I, personally, haven'
even scratched the surface of what I can do with this amazing program.

I hope that you haven't abandoned this thread, since I have anothe
question. The formula above was an attempt to produce a WIN-LOSS coun
for a specific team. (There is no possibility for a TIE, sinc
overtimes are played until a winner is determined).

Now, for each game on a team's page, I have a summary box. This summar
box compiles data about THAT particular game. (i.e.: Final score
opponent, game stats, HOME or AWAY, etc...). The top right cell i
this summary box is for the WIN-LOSS record for THAT WEEK ONLY. Thi
is the cell that I needed the formula above to calculate data for.
And, to a certain extent, I can use the formula that you have shown m
to make this calculation...with three major shortfalls.

First, until a final score is entered for a specific game, I would lik
that cell to show the WIN-LOSS record from the PREVIOUS game. (No
0-0...unless, of course, it's the cell in the summary box for Game 1).

Second, after each score is entered, and the current WIN-LOSS recor
appears in the proper cell, I don't ever want it to change. I want th
WIN-LOSS record to reflect ONLY up to THAT PARTICULAR GAME. When futur
scores are entered, PAST game summaries SHOULD NOT be recalculated.

And, third...as stated earlier, I CAN use the formula you'v
constructed to achieve this. (Although, a slightly modified version).
But, then I have to modify the formula for EACH SUMMARY BOX.

So, here's my question:

Is there a formula that I can enter into the FIRST WEEK's summary bo
that can be COPY/PASTED into the other summary boxes withou
modification, (other than the cell reference adjustments that Exce
makes automatically)??

I hope the answer is YES. And, again, thanks a million for taking tim
to read and respond to this thread. I am quickly discovering that th
books I've purchased on Excel and Excel formulas might explain eac
function adequately, but it's often difficult to incorporate THEI
examples into my own formulas.

(I should probably mention that the WIN-LOSS information is to b
obtained from a RANGE (P1225) within a chart named Schedule_
(C12:R25). Column C12:C25 in Schedule_1 contains the numerical marke
for each game.

 

[Bob Phillips]

I think some sample data/results would help.

--
HTH

Bob Phillips

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

"HuskerBronco" <[email protected]>
wrote in message

 

[HuskerBronco]

Perhaps a copy of the workbook would help.

The attachment is the beginning stages of my workbook. I want to ge
all of the formulas working on the first page of the workbook, before
copy that page to successive pages for each team. I would like to b
able to copy these pages, and make minimal changes to the formulas fo
each new page.

For example, for the second page....I only needed to change the INDE
reference in cell A1...and REPLACE all references to Schedule_1 wit
references to Schedule_2...(and Define Schedule_2)

+-------------------------------------------------------------------
|Filename: Book 2.zip
|Download: http://www.excelforum.com/attachment.php?postid=5002
+-------------------------------------------------------------------

 

[HuskerBronco]

It seems as if you've all given up on me.

No problem. I finally found the formula to calculate my worksheet'
W-L statistics properly, without recalculating games after curren
results are entered. The SumProduct function suggested by Harlan Grov
was the key. Thanks a bunch Harlan. I paid a visit to 'xlDynamic.com
(http://www.xldynamic.com/source/xld.SUMPRODUCT.html) which has
fantastic article about the SumProduct function. From there I adapte
one of their formulas to my suit my needs, resulting in the followin
formula:

=SUMPRODUCT((Game_number<=C53)*(SU_record="W"))&"-"&SUMPRODUCT((Game_number<=C53)*(SU_record="L")