what's wrong with the formula?

  • Thread starter Thread starter Jack Zhong
  • Start date Start date
J

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)))
 
Jack said:
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)))
Click to expand...


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
 
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
 
Biff said:
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
Click to expand...


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
 
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.
 
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 said:
I have Excel 2003 SP2 and it doesn't crash...
Click to expand...

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
 
Biff brought next idea :
Hi!
Click to expand...
It crashed on me too. Also using Excel 2002.
Click to expand...
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!
Click to expand...
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!
Click to expand...

My Excel and Access might crash if it contained too many data or codes
without any notice. :/
 
I tried the formula with empty cells, but with some data in cells, Excel
crashed when I tried to evaluate it with Evaluate Formula...
Click to expand...

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 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.
Click to expand...
=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)))
Click to expand...

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