what's wrong with the formula?

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 Zhong wrote:
> 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

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

"Jack Zhong" <[email protected]> wrote in message
news:[email protected]...
> 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)))
>
>

Biff wrote:
> 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

> 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 Verga wrote:
> Biff wrote:
>> 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...

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!

> 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

> "Jack Zhong" <[email protected]> wrote in message
> news:[email protected]...
>> 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)))
>>
>>

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...

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.

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