Rant!

[Biff]

I've always had problems with Excel 2002 crashing when I use the formula
auditing tool, evaluate formula, on really "complex" array formulas.

I'm sad to say Excel has hit a new low and even crashes on a simple formula
like this:

=IF(A1>0,(MATCH(TRUE,A2:A$10>0,0)-1)*A1,"")

It crashes evaluating the array:

A2:A$10>0

I pretty much have learned what formulas will cause a crash and don't use
the evaluate formula command on those formulas but this latest one is a joke
(but I'm not $%^*($%% laughing).

Yes, I know I can highlight expressions and press F9 but it's not the same
as evaluate formula. Evaluate formula is a great tool when it doesn't cause
a crash!

Rant over.

Biff

 

[Dave Peterson]

I just tried this in xl2003.

xl2003 didn't crash, but tools|formula auditing|evaluate formula didn't work
either.

The evaluation box just cleared when it was evaluating a2:a$10>0.

 

[Dave Peterson]

Oops. Never mind that last post. xl2003 crashed too.

(I didn't have anything in A1 and the formula evaluated to "" in my other test
(doh!!).)

 

[Biff]

Hi Dave!

This is very frustrating. I would just like to know whether it is in fact a
problem with Excel or maybe my machine configuration isn't up to the task.
(enough ram?) I have 256mb. That isn't a lot by *TODAYS* standard but that
was pretty strong when I got the machine and I've had this problem since day
1.

I'm getting tired of sending error reports to M$. It'd be nice to get some
feedback from them.

Biff

 

[Roger Govier]

Hi Biff

No consolation I know, but it's not your machine.
I have 1 GB RAM and just tested with XL2002 and XL2003 and experienced
the same crash.
I did wonder though, why you were using an array formula as opposed to
=IF(A1>0,(COUNTIF(A2:A$10,">0")-1)*A1,"")
which produced the expected results for me in both versions.

--
Regards

Roger Govier

Hi Dave!

This is very frustrating. I would just like to know whether it is in
fact a problem with Excel or maybe my machine configuration isn't up
to the task. (enough ram?) I have 256mb. That isn't a lot by *TODAYS*
standard but that was pretty strong when I got the machine and I've
had this problem since day 1.

I'm getting tired of sending error reports to M$. It'd be nice to get
some feedback from them.

Biff

 

[Dave Peterson]

I have 1GB of memory and xl still crashed. It sure doesn't look like a hardware
problem to me.

 

[Biff]

Hi Roger!

I did wonder though, why you were using an array formula as opposed to
=IF(A1>0,(COUNTIF(A2:A$10,">0")-1)*A1,"")

I'm looking for the first position of a value that is >0. Countif would
count how many meet that criteria in the entire range.

Biff

Hi Biff

No consolation I know, but it's not your machine.
I have 1 GB RAM and just tested with XL2002 and XL2003 and experienced the
same crash.
I did wonder though, why you were using an array formula as opposed to
=IF(A1>0,(COUNTIF(A2:A$10,">0")-1)*A1,"")
which produced the expected results for me in both versions.

 

[Roger Govier]

Hi Biff

Quite right, Countif provides totally the wrong answer.
However, on retrying your formula in XL2003, with data of 2,0,0,
,0,6,7,8,9,8 in cell a1:a10, I now get the correct result of 8 returned
to the formula posted in cell C1, whereas previously I was getting #
VALUE.
On trying to Evaluate the formula with Auditing, I still experience the
crash.
Very peculiar.