Excel Formula Error

[Griffey5]

What is wrong with this formula?

=SUM(IF(('Batch Log'!A2:A2001="February")*('Batch
Log'!G2:G2001="530204-03")*('Batch Log'!H2:H2001="3000272.02"),'Batch
Log'!F2:F2001,0))

It will compute successfully with just the first two conditions, but cannot
when the third condition is added.

 

[joeu2004]

What is wrong with this formula?

=SUM(IF(('Batch Log'!A2:A2001="February")*('Batch
Log'!G2:G2001="530204-03")*('Batch Log'!H2:H2001="3000272.02"),
'Batch Log'!F2:F2001,0))

It will compute successfully with just the first two conditions, but
cannot when the third condition is added.

Look at the format of H2:H2001. Is truly text? I can only guess that
perhaps that 3rd term should have 3000272.02 instead of "3000272.02".
Alternatively, perhaps you intended H2:H2001 to be formatted as text,
but it is not.

Having said that, I should add that I think it is a bad idea to test
for equality of a non-integral value (unless it is text). Even if you
form 3000272.02 from integers (e.g. 1000*A1+mod(A2,1000)+mod(A3,100)/
100), it is not exactly what it appears to be.

Finally, if my guesses prove to be incorrect, it would be prudent for
you to explain what you mean when you say the formula "cannot
compute". Do you get an Excel error, as your subject line suggests?
If so, what error? Or do you simply get an unexpected result, as the
phrases "cannot compute" suggests to me? If so, what do you get, and
what do you expect instead?

HTH.

 

[David Biddulph]

See the other group where you posted the same question.

Please don't multi-post; cross-post if you need to.
http://www.cs.tut.fi/~jkorpela/usenet/xpost.html

 

[Ragdyer]

Perhaps the values in Column H are true XL numbers, and *not* text!

Your parenthesis placed around "3000272.02" are telling the formula to look
for *text* values.

It they're numbers, just remove the parens.

And of course, you *do know* that your formula is an *array* formula, and
must be entered using CSE.

 

[joeu2004]

See the other group where you posted the same question.

Can you provide a link? Alternatively, date, subject and author
information?

Using Google Groups search for author "griffey5", the only other
similar posting that I could find was under m.p.e.crashesgpfs(!). And
I found no responses there. (No surprise!) However, I know that not
everything posted to MS "discussion groups" find their way into Google
Groups (sigh).

 

[joeu2004]

Your parenthesis placed around "3000272.02" are telling the formula to look
for *text* values.

It they're numbers, just remove the parens.

I think you mean quotes or double-quotes.

And of course, you *do know* that your formula is an *array* formula,
and must be entered using CSE.

Good point. But if the OP does not understand how to work with array
formula, it's a good bet that the OP does not know that CSE means ctrl
+shift+Enter.

To the OP: if that might be your problem, select the cell with the
formula, press F2, then press ctrl+shift+Enter.

 

[Ragdyer]

Since I've already finished my Sunday morning pot of coffee, there's really
*no* excuse for making that mistake ... except ... of course ... old
age!<bg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Your parenthesis placed around "3000272.02" are telling the formula to look
for *text* values.

It they're numbers, just remove the parens.

I think you mean quotes or double-quotes.

And of course, you *do know* that your formula is an *array* formula,
and must be entered using CSE.

Good point. But if the OP does not understand how to work with array
formula, it's a good bet that the OP does not know that CSE means ctrl
+shift+Enter.

To the OP: if that might be your problem, select the cell with the
formula, press F2, then press ctrl+shift+Enter.

 

[Rick Rothstein \(MVP - VB\)]

It looks like he posted it earlier today in a newsgroup named...

microsoft.public.excel.crashesgpfs

(which I never heard of before) under the Subject line "Arrays in Excel",
but Google didn't show him getting an answer there that I can see.

Rick

See the other group where you posted the same question.

Can you provide a link? Alternatively, date, subject and author
information?

Using Google Groups search for author "griffey5", the only other
similar posting that I could find was under m.p.e.crashesgpfs(!). And
I found no responses there. (No surprise!) However, I know that not
everything posted to MS "discussion groups" find their way into Google
Groups (sigh).

 

[Ragdyer]

It's in the .misc group, with a single response from David.
"Excel Formula Errors"

 

[David Biddulph]

The place where I'd seen the question was in a somewhat less obscure group
(microsoft.public.excel.misc).

The posting was http://groups.google.co.uk/group/mi...f/de174ed6aa75cd82?lnk=st&q=#de174ed6aa75cd82
http://preview.tinyurl.com/yqgm8q

 

[Rick Rothstein \(MVP - VB\)]

I looked physically looked there (as its one of the groups I monitor) and
didn't see it, so I did a Google search which didn't show the misc group
either... instead, it showed the group I posted. I'm not sure why I missed
it in the misc group (it's there now), but didn't Google used to be much
quicker about getting things in its archive than it seems to do now?

Rick

 

[joeu2004]

I looked physically looked there (as its one of the groups I monitor) and
didn't see it, so I did a Google search which didn't show the misc group
either... instead, it showed the group I posted. I'm not sure why I missed
it in the misc group (it's there now), but didn't Google used to be much
quicker about getting things in its archive than it seems to do now?

Perhaps. But I don't think it's a question of how quickly Google
Groups gets things into its archive. I have used GG for a few years
now, and lately, it seems to have a lot of consistency problems --
articles not found in searches; articles found in the index, but not
readable; articles that come and go. For newly posted articles, some
of those anomalies could be explained as internal servers being out of
sync. But I suspect the more general explanation is that GG is either
having trouble with its in internal network of servers, or its
networked application have become less tolerant of errors or delays.

That conclusion is consistent with another observation that is
happening more often: articles posted in GG do not appear in GG,
sometimes ever, but usually not for a very long time. Yet they do
appear (sometimes) first in MS Discussion Groups. I believe that can
only be explained by problems in GG's internal network, being it
slowness or unreliability.

BTW, there has always been a problem with GG seeing articles that were
originally posted in MS Discussion Groups -- at least, the base
article of a thread. Frequently, I never see the base article in GG,
only the replies; but some base articles do come through. Moreover,
GG usually shows replies that were posted in MSDG; I can only remember
seeing problems with base articles. I don't know if GG or MSDG is at
fault.

 

[Griffey5]

Tried removing the parenthesis and it did not work as a number. Also tried
changing it to text so it would read it as text, did not work. I even tried
changing the number to 3000272-02 so it would read as text, did not work.

Yes I know this is an array.

 

[yshridhar]

Try with Sumproduct.
=SUMproduct(--('Batch Log'!A2:A2001="February")*--('Batch
Log'!G2:G2001="530204-03")*--('Batch Log'!H2:H2001="3000272.02"),'Batch
Log'!F2:F2001,0))
With regards
Sreedhar

 

[David Biddulph]

Perhaps you've got spaces or other invisible characters in your text string.
For one of the column H cells where you think you've got "3000272.02", does
=ISTEXT(H2) return TRUE, and does =LEN(H2) return 10 ?

 

[David Biddulph]

I don't think you need the double unary minus where you're doing a multiply
operation with *