up vote 0 down vote favorite
share [g+] share [fb]

I am using Excel to get some hairy calculations done (a significance test on a trinomial distribution, ugly!) and I just noticed that when I enter a formula like =D47^0, I get a #NUMBER error, when math textbooks insist that I should get a 1.

Is this a bug and if yes, how come nobody noticed it before?! Am I doing something wrong? Is there a workaround other than use a IF formula which checks for a zero in the exponent?

I already tried using the "normal" POWER(number;exponent) formula instead of the shortcut circumflex, without success.

link|improve this question
76% accept rate
Why is this a problem (not making fun of; genuinly interested) if you know it equals 1? – ldigas Aug 18 '10 at 17:30
I'm using Excel 2003 and it correctly evaluated that as 1. I even put the exponent in a separate cell and plugged the cell reference into the formula and it worked fine. – Michael Aug 18 '10 at 17:33
The formula for a trinomial distribution contains 3 exponential terms. I am using the formula up to 20 times per data set, and I have 38 data sets now, but expect more in the future. Manually inserting 1 everywhere is too much (error-prone) work, and using if($E52<>0;D47^$E52;1) instead of D47^$E52 everywhere bloats an already unreadable formula to a non-debuggable monstrosity, because I don't have the space on the sheet to calculate each term in its own cell, so the whole trinomial formula goes into a single cell, the ones I posted are the fourth level of nesting. – rumtscho Aug 18 '10 at 17:41
This worked fine for me--evaluating to 1. Can you give an example of the numbers you are working with? – Sux2Lose Aug 18 '10 at 17:57
Understood. Yes, that makes sense. I also just tried (I wasn't on a machine that had Excel before) evaluating in several ways some number^0, and in all cases it worked out fine. Could you provide a working example of where the error occurs (Excel 2007 here). – ldigas Aug 18 '10 at 21:45
feedback

1 Answer

up vote 4 down vote accepted

Excel correctly gives you 1 as long as D47 is not 0. If you try to take 0^0, only THIS is equal to "#NUM!". The mathematical reason is x^0 is always 1, but 0^x is always 0, so 0^0 is undefined.

link|improve this answer
Thank you, you found it out. Only after you told me this, I noticed that the exponent and base in my case are calculated (using two different methods) from the same data set, so whenever the calculation for the exponent gives a zero, the calculation for the base also results in a zero. I must have been staring at cells for too long to not notice it myself :( – rumtscho Aug 18 '10 at 18:44
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.