Help getting origianl value from ending value

michaelsmith559

Well-known Member
Joined
Oct 6, 2013
Messages
881
Office Version
  1. 2013
  2. 2007
In excel, I have a starting value in J371. Starting in K371 I have this formula: "=J371^(1/10)". L371 contains this formula: "=k371^(1/10)". And so on.
The list of values is below. My question is, as long as there is a formula in the cells, I can reverse the operations to get back to the starting value.
I could start in S372 with this formula: "=T371^10" and get the 1.000000000042070 and continue on all the way back to the original value. However,
if I take the ending value 1.00000000000421 by itself and raise it ^10 it does not give me 1.000000000042070. I want to know why. I'm asking because if
I format the cell, there is only zeroes after the 1. Is there a way to start with the ending value and get back to the original value if there are no
formulas in the cells to get to that value? To ask another way, Why can't I start with the value in T371 by itself (no formula), raise it to ^10 and get
the value of S371 and so on? Thanks for any help.

J371 = 1.042964685342690
K371 = 1.00421559238756
L371 = 1.000420761662500
M371 = 1.000042068201560
N371 = 1.000004206740520
O371 = 1.000000420673260
P371 = 1.000000042067320
Q371 = 1.000000004206730
R371 = 1.000000000420670
S371 = 1.000000000042070
T371 = 1.00000000000421

Mike
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Here is what excel does:

10th Root 10th Power
1.52298643503866 1.00000000000421
1.04296468534269 1.0000000000421
1.00421559238756 1.0000000004210
1.00042076166250 1.0000000042100
1.00004206820156 1.0000000420997
1.00000420674052 1.0000004209967
1.00000042067326 1.0000042099745
1.00000004206732 1.0000421005429
1.00000000420673 1.0004210851982
1.00000000042067 1.0042188400213
1.00000000004207 1.0429984153162
1.00000000000421 1.5234790477757
 
Upvote 0
I also checked using goal seek to see what power 1.00000000000421 had to be raised to, to get 1.00000000004207 and it is 9.99............etc. If the value is calculated using a 10th root why does excel get it wrong? Is there a way to correct this?
 
Upvote 0
Yes, I was wondering if that was the problem. Is there any add-in to do math for excel that doesn't have this problem or do I need to look at another software?
 
Upvote 0
The are extended-precision add-ins for Excel; see for example, xlPrecision

I expect they are hundreds to thousands of times slower than native floating-point calculations.
 
Upvote 0
Yes, I was wondering if that [binary arithmetic] was the problem. Is there any add-in to do math for excel that doesn't have this problem or do I need to look at another software?

In addition to the difference between binary and decimal arithmetic, bear in mind that in general, non-integer exponentiation can only be done by an approximating series.

Also, even though Excel formats only up to 15 significant digits (14 decimal places in your examples), values are stored with more precision.

So some of the arithmetic difference you see arises because you entered the displayed value instead of the actual calculated value in the cell.

For example, if we start with 1.52298643503866 in A1 [1] and propagate =A1^(1/10) in A2 down through A12, then enter =A12 into B12 and propagate =B12^10 in B11 up through B1, the result in B1 is displayed as 1.52297171209273 [2]. FYI, that is a difference of only -0.000967%.

Compare with about 1.52347904777567 that you show, a difference of 0.0323%.

[1] 1.52298643503866 is probably not the correct starting value exactly. Presumably, it is the result of a calculation. So the actual value might differ by as much as about +/-4.88E-15. But that does make any difference after a few steps.

[2] The result in B1 is actually 1.52297171209273+3.77E-15.


I also checked using goal seek to see what power 1.00000000000421 had to be raised to, to get 1.00000000004207 and it is 9.99............etc. If the value is calculated using a 10th root why does excel get it wrong? Is there a way to correct this?

I don't quite understand the question. But bear in mind that Goal Seek is likely to derive a less accurate result than direct arithmetic, since GS has arbitrary limits on the precision of its internal algorithms.

In particular, using the calculated values (not the displayed values) in A11 and A12 generated by the procedure described above, the exponential factor to go from A12 to A11 (i.e. x for A12^x = A11) is:

=LOG(A11)/LOG(A12) = 10.000211137314 (about)

and A12^10.000211137314 is exactly A11.

Granted, 10.000211137314 is not 10. But it is not 9.99...9 either. ;)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,150
Messages
6,170,377
Members
452,322
Latest member
CrimsonCoure

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top