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.

  • Why is this a problem (not making fun of; genuinly interested) if you know it equals 1? – Rook 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). – Rook Aug 18 '10 at 21:45

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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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