ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Why does Excel say this formula contains an error? (https://www.excelbanter.com/excel-worksheet-functions/98110-why-does-excel-say-formula-contains-error.html)

HuskerBronco
Why does Excel say this formula contains an error?
 

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?::
:confused:
*=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

Bob Phillips
Why does Excel say this formula contains an error?
 
=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?::
:confused:

*=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

[email protected]
Why does Excel say this formula contains an error?
 
HuskerBronco wrote:
Then why does Excel tell me that -*THIS*- formula contains an error?::
:confused:
*=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
Why does Excel say this formula contains an error?
 
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?::
:confused:
*=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
Why does Excel say this formula contains an error?
 

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

[email protected]
Why does Excel say this formula contains an error?
 
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.

Harlan Grove
Why does Excel say this formula contains an error?
 
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.

[email protected]
Why does Excel say this formula contains an error?
 
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.

HuskerBronco
Why does Excel say this formula contains an error?
 

Thanks, so much for all of the information. I am absolutely amazed at
the scope of the Excel program, especially since I, personally, haven't
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 another
question. The formula above was an attempt to produce a WIN-LOSS count
for a specific team. (There is no possibility for a TIE, since
overtimes are played until a winner is determined).

Now, for each game on a team's page, I have a summary box. This summary
box compiles data about THAT particular game. (i.e.: Final score,
opponent, game stats, HOME or AWAY, etc...). The top right cell in
this summary box is for the WIN-LOSS record for THAT WEEK ONLY. This
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 me
to make this calculation...with three major shortfalls.

First, until a final score is entered for a specific game, I would like
that cell to show the WIN-LOSS record from the PREVIOUS game. (Not
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 record
appears in the proper cell, I don't ever want it to change. I want the
WIN-LOSS record to reflect ONLY up to THAT PARTICULAR GAME. When future
scores are entered, PAST game summaries SHOULD NOT be recalculated.

And, third...as stated earlier, I CAN use the formula you've
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 box
that can be COPY/PASTED into the other summary boxes without
modification, (other than the cell reference adjustments that Excel
makes automatically)??

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

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


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

HuskerBronco
Why does Excel say this formula contains an error?
 

Thanks, so much for all of the information. I am absolutely amazed at
the scope of the Excel program, especially since I, personally, haven't
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 another
question. The formula above was an attempt to produce a WIN-LOSS count
for a specific team. (There is no possibility for a TIE, since
overtimes are played until a winner is determined).

Now, for each game on a team's page, I have a summary box. This summary
box compiles data about THAT particular game. (i.e.: Final score,
opponent, game stats, HOME or AWAY, etc...). The top right cell in
this summary box is for the WIN-LOSS record for THAT WEEK ONLY. This
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 me
to make this calculation...with three major shortfalls.

First, until a final score is entered for a specific game, I would like
that cell to show the WIN-LOSS record from the PREVIOUS game. (Not
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 record
appears in the proper cell, I don't ever want it to change. I want the
WIN-LOSS record to reflect ONLY up to THAT PARTICULAR GAME. When future
scores are entered, PAST game summaries SHOULD NOT be recalculated.

And, third...as stated earlier, I CAN use the formula you've
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 box
that can be COPY/PASTED into the other summary boxes without
modification, (other than the cell reference adjustments that Excel
makes automatically)??

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

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


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

Bob Phillips
Why does Excel say this formula contains an error?
 
I think some sample data/results would help.

--
HTH

Bob Phillips

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

"HuskerBronco"
wrote in message
news:HuskerBronco.2aqhj2_1152542062.0326@excelforu m-nospam.com...

Thanks, so much for all of the information. I am absolutely amazed at
the scope of the Excel program, especially since I, personally, haven't
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 another
question. The formula above was an attempt to produce a WIN-LOSS count
for a specific team. (There is no possibility for a TIE, since
overtimes are played until a winner is determined).

Now, for each game on a team's page, I have a summary box. This summary
box compiles data about THAT particular game. (i.e.: Final score,
opponent, game stats, HOME or AWAY, etc...). The top right cell in
this summary box is for the WIN-LOSS record for THAT WEEK ONLY. This
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 me
to make this calculation...with three major shortfalls.

First, until a final score is entered for a specific game, I would like
that cell to show the WIN-LOSS record from the PREVIOUS game. (Not
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 record
appears in the proper cell, I don't ever want it to change. I want the
WIN-LOSS record to reflect ONLY up to THAT PARTICULAR GAME. When future
scores are entered, PAST game summaries SHOULD NOT be recalculated.

And, third...as stated earlier, I CAN use the formula you've
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 box
that can be COPY/PASTED into the other summary boxes without
modification, (other than the cell reference adjustments that Excel
makes automatically)??

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

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


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

HuskerBronco
Why does Excel say this formula contains an error?
 

Perhaps a copy of the workbook would help.

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

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


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

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

HuskerBronco
Why does Excel say this formula contains an error?
 

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

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

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


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


All times are GMT +1. The time now is 04:45 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com