MMult & Transpose

[CyFoo]

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Have been reading the forum for Excel and VBA tips for the past few years and many thanks to everybody for your efforts in posting enquiries and answering them. It has been very informative.<o></o>
<o></o>
I have an enquiry on MMult & Transpose in Excel 2007.<o></o>
<o></o>
Have written some UDF and subs in VBA that auto generate the following equation from LinEst() function,<o></o>
= MMULT(A1^{0,3,2,1},TRANSPOSE({405019.817326055,-0.0195348471345153,-1.51284947109658,371.838931959456}))<o></o>
<o></o>
Theoretically, if A1 is 0, the equation should have returned 405019.817326055. Nevertheless, it returned #NUM! instead. This condition will only happen if A1 contained a 0.<o></o>
<o></o>
Can anyone please help or guide me to the relevant threads, please?

 

[GlennUK]

Sorry, but in Excel 0^0 returns #NUM!

 

[pgc01]

Hi

Like Glenn said 0^0 returns a number error.

In your case, it seems you'd like 0^0 to return 1?

In that case, add it to the formula:

= MMULT(IF((A1=0)*({0,3,2,1}=0),1,A1^{0,3,2,1}),TRANSPOSE({405019.817326055,-0.0195348471345153,-1.51284947109658,371.838931959456}))

This is an array formula, you have to confirm it with Ctrl-Shift-Enter.

 

[CyFoo]

Thanks Glenn & pgc01

0^0 is indeed undefined in Excel, and pgc01's AND array is right on spot, output-ing {1,0,0,0} array directly.

IF((A1=0)*({0,3,2,1}=0)

great reply and many thanks!

just wondering if there is any built in excel error checking options that automatically output 0^0=1? it would make my function simpler and only need to make one cell reference.
imagine the output of a 16 variable regression model, with an IF( AND array ) for each variable, which makes a total of 32 cell references

 

[Domenic]

Also, unless I'm missing something, I think the following should suffice...

=MMULT(IF((A1=0)*({0,3,2,1}=0),1,A1^{0,3,2,1}),{405019.817326055;-0.0195348471345153;-1.51284947109658;371.838931959456})

Hope this helps!

 

[CyFoo]

Thanks Domenic for shortening the function string.

do you also have any suggestion for the following?

just wondering if there is any built in excel error checking options that automatically output 0^0=1? it would make my function simpler and only need to make one cell reference.
imagine the output of a 16 variable regression model, with an IF( AND array ) for each variable, which makes a total of 32 cell references

<!-- / message -->