Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.fr.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
excel spreadsheet function
Hi all,
I have run across one of my project and could not figure this out. This is mathematically correct, totally, but excel could not give me the desired answer: (-3)^(-2/3) returns #NUM!, but ((-3)^(-2))^(1/3) is ok. This leads to other problems also if you have time to change the base for the power function (negative and possitive) I am using Microsoft XP Professional. Thanks, Lmn. |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.fr.excel
|
|||
|
|||
excel spreadsheet function
Hi Nguyen, your problem is not from excel but in your mathematic...
You are asking excel to give you imaginary number... In your exemple, try to do your (result ^(-1/2))^3. (-3^[(-2)*(1/3)*(3)*(-1/2)=1] You will have 3 not -3 because by doing ^-2 first, u take out the -.... In fact, you want a 1/ (-3)^2/3 or you won't have a real number with a fraction on ^ exept if the fraction is 1/ (1 or 3 or 5 or 7....) I let you search the mathematical poof :-) Best regards Benjamin "Dailoc Nguyen" a écrit : Hi all, I have run across one of my project and could not figure this out. This is mathematically correct, totally, but excel could not give me the desired answer: (-3)^(-2/3) returns #NUM!, but ((-3)^(-2))^(1/3) is ok. This leads to other problems also if you have time to change the base for the power function (negative and possitive) I am using Microsoft XP Professional. Thanks, Lmn. |
#3
Posted to microsoft.public.fr.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
excel spreadsheet function
HI!
=3^-2= Positif =-3^-2=Positif In your example: (-3)^(-2/3) returns #NUM!, because (-2/3)< that 1 -3^-(number<1) it's impossible. in your other example: ((-3)^(-2))^(1/3) is ok.because ((-3)^(-2))=Positif Try =3^(-2/3) Starwing |
#4
Posted to microsoft.public.fr.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
excel spreadsheet function
Hi
and just for the fun of it the result for your original formula would be -0.240375 - 0.416342i -- Regards Frank Kabel Frankfurt, Germany "Dailoc Nguyen" schrieb im Newsbeitrag m... Hi all, I have run across one of my project and could not figure this out. This is mathematically correct, totally, but excel could not give me the desired answer: (-3)^(-2/3) returns #NUM!, but ((-3)^(-2))^(1/3) is ok. This leads to other problems also if you have time to change the base for the power function (negative and possitive) I am using Microsoft XP Professional. Thanks, Lmn. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel spreadsheet function
Thanks for quick reply. We have some reply that are not quite getting along with each other. Mathematically, you can take an odd root of a negative number. Also those 2 statements are equivalent mathematically [(-3)^(-2/3) = ((-3)^(-2))^(1/3)] and should not yield an imaginary number. This question seems to be very interesting. Lmn. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel spreadsheet function
It probably has to do with Excel's internal parsing routines. If you add
parentheses, (i.e. what you show below), =((-3)^(-2))^(1/3) it doesn't give an error. But (-3)^(-2/3) does. On Thu, 28 Oct 2004 18:34:57 -0700, Dailoc Nguyen wrote: Thanks for quick reply. We have some reply that are not quite getting along with each other. Mathematically, you can take an odd root of a negative number. Also those 2 statements are equivalent mathematically [(-3)^(-2/3) = ((-3)^(-2))^(1/3)] and should not yield an imaginary number. This question seems to be very interesting. Lmn. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel spreadsheet function
This rearrangement also works without error.
=(-3)^(1/3)^(-2) On Thu, 28 Oct 2004 20:53:55 -0500, Myrna Larson wrote: It probably has to do with Excel's internal parsing routines. If you add parentheses, (i.e. what you show below), =((-3)^(-2))^(1/3) it doesn't give an error. But (-3)^(-2/3) does. On Thu, 28 Oct 2004 18:34:57 -0700, Dailoc Nguyen wrote: Thanks for quick reply. We have some reply that are not quite getting along with each other. Mathematically, you can take an odd root of a negative number. Also those 2 statements are equivalent mathematically [(-3)^(-2/3) = ((-3)^(-2))^(1/3)] and should not yield an imaginary number. This question seems to be very interesting. Lmn. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel spreadsheet function
Remember that Excel and almost all other computer programs do math
numerically in binary, not algebraically. Rational numbers of the form 1/n (where n is an integer) can only be exactly represented in Excel if n is an integer power of 2. Otherwise 1/n must be approximated. Similarly, rational numbers of the form m/n can only be exactly represented if n/g is an integer power of 2, where g is the greatest common divisor of both m and n. Otherwise m/n must be approximated. When Excel is asked to raise a negative number to a power that is close enough to 1/n for some integer n, then the power routine makes a guess that the number is an approximation to 1/n. This guess may be wrong, but the developers were willing to take that chance for numbers of the form 1/n, though not for general rational numbers m/n. It is a design decision. Presumably you do know whether the power is rational or not, where Excel can only guess. When it is rational, you can help Excel and insure that you get the answer you want by restructuring the calculation so that Excel is looking at ...^(1/n) not ...^(m/n). Note that even algebraic software may take the primary branch to be complex rather than real. For example, in Maple simplify((-3)^(-2/3)) is complex. Excel's Analysis ToolPak has functions for complex arithmetic http://groups.google.com/groups?selm...0no_e-mail.com Jerry Dailoc Nguyen wrote: Thanks for quick reply. We have some reply that are not quite getting along with each other. Mathematically, you can take an odd root of a negative number. Also those 2 statements are equivalent mathematically [(-3)^(-2/3) = ((-3)^(-2))^(1/3)] and should not yield an imaginary number. This question seems to be very interesting. Lmn. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel function to reverse the order of rows in a spreadsheet | Excel Worksheet Functions | |||
Looking for a macro/function in an excel spreadsheet (Help) | Excel Worksheet Functions | |||
Excel function button in spreadsheet | Excel Discussion (Misc queries) | |||
Excel function button in spreadsheet | Links and Linking in Excel | |||
What is the spreadsheet function in excel | Excel Worksheet Functions |