what's wrong with the formula?

[Jack Zhong]

The Excel 2002 application would crash down if I attempted to audit the
following formula. It exits when steps to the array, I mean when
calculates the first sum() in the formula.

=IF($X$5*X10=0,0,IF($L$1=1,X10-((SUM($D$8:$L$8*D10:L10))*3-$X$9)*0.8,X10-0.8*(SUM($N$8:$V$8*N10:V10)*1.5-$X$9)))

 

[Franz Verga]

The Excel 2002 application would crash down if I attempted to audit
the following formula. It exits when steps to the array, I mean when
calculates the first sum() in the formula.

=IF($X$5*X10=0,0,IF($L$1=1,X10-((SUM($D$8:$L$8*D10:L10))*3-$X$9)*0.8,X10-0.8*(SUM($N$8:$V$8*N10:V10)*1.5-$X$9)))

Hi Jack,

try to array enter your formula, i.e. press Ctrl + Shift + Enter instead of
Enter or try to use this one:

=IF($X$5*X10=0,0,IF($L$1=1,X10-((SUMPRODUCT(($D$8:$L$8)*(D10:L10)))*3-$X$9)*0.8,X10-0.8*(SUMPRODUCT(($N$8:$V$8)*(N10:V10))*1.5-$X$9)))

with this one you have to press just Enter.

If the formula don't work, maybe it's something wrong in your data...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Biff]

Hi!

It crashed on me too. Also using Excel 2002.

Excel seems to crash when using the formula auditing tool, Evaluate Formula,
on certain types of array formulas. I've ranted about this before. I can
crash my version of Excel at will. Some folks have reported the same thing
happens in Excel 2003. MS doesn't have the time or resources to find and/or
fix the problem. They're busy giving us a %^&*() ribbon!

You can do almost the same thing by highlighting the expressions in the
formula bar and hitting function key F9. Although this has limits as to the
size of an array. You'll get a message saying formula is too long but at
least it won't crash!

Biff

 

[Franz Verga]

Hi!

It crashed on me too. Also using Excel 2002.

Excel seems to crash when using the formula auditing tool, Evaluate
Formula, on certain types of array formulas. I've ranted about this
before. I can crash my version of Excel at will. Some folks have
reported the same thing happens in Excel 2003. MS doesn't have the
time or resources to find and/or fix the problem. They're busy giving
us a %^&*() ribbon!
You can do almost the same thing by highlighting the expressions in
the formula bar and hitting function key F9. Although this has limits
as to the size of an array. You'll get a message saying formula is
too long but at least it won't crash!

Biff

I have Excel 2003 SP2 and it doesn't crash...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Jack Zhong]

If the formula don't work, maybe it's something wrong in your data...

Hi, Ciao, thank you. My formula works though the result might be wrong.
The problem lies in tracing it.

 

[Jack Zhong]

I have Excel 2003 SP2 and it doesn't crash...

Hi, Franz,

Sorry I misspelled your name just now. Mine is Excel 2002 SP 2 too.

Jack

 

[Franz Verga]

I have Excel 2003 SP2 and it doesn't crash...

Errata Corrige...

I tried the formula with empty cells, but with some data in cells, Excel
crashed when I tried to evaluate it with Evaluate Formula...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Jack Zhong]

Biff brought next idea :

Hi!

It crashed on me too. Also using Excel 2002.

Excel seems to crash when using the formula auditing tool, Evaluate Formula,
on certain types of array formulas. I've ranted about this before. I can
crash my version of Excel at will. Some folks have reported the same thing
happens in Excel 2003. MS doesn't have the time or resources to find and/or
fix the problem. They're busy giving us a %^&*() ribbon!

You can do almost the same thing by highlighting the expressions in the
formula bar and hitting function key F9. Although this has limits as to the
size of an array. You'll get a message saying formula is too long but at
least it won't crash!

My Excel and Access might crash if it contained too many data or codes
without any notice. :/

 

[Jack Zhong]

I tried the formula with empty cells, but with some data in cells, Excel

crashed when I tried to evaluate it with Evaluate Formula...

The application crashed once again after I evaluated the formula. The
formula works correctly this time but I doubt it would crash again
later on if it is running for a long time. I encountered this problem
with my Access constantly before, I programmed on Access before, it
crashed often when it contained many data.

 

[Jack Zhong]

Jack Zhong has brought this to us :

The Excel 2002 application would crash down if I attempted to audit the
following formula. It exits when steps to the array, I mean when calculates
the first sum() in the formula.

=IF($X$5*X10=0,0,IF($L$1=1,X10-((SUM($D$8:$L$8*D10:L10))*3-$X$9)*0.8,X10-0.8*(SUM($N$8:$V$8*N10:V10)*1.5-$X$9)))

The problem seems lie in the MS Excel itself, not the formula. It
crashed down when I design the userform. Ooops!