LN and exp function on big numbers in excel, a bit mathematical question maybe

[Pascalvrolijk]

Dearest helping hand,

I have been wondering for almost a day now how to handle the following problem (which might also be a bit mathematic).
In column A i have 100.000 numbers and each of them is very big, around 9.000.000.
I need to calculate the following: LN( exp(A1)+exp(A2)+exp(A3)+..+exp(A100.000) )

LN(exp(A1)) = A1 because LN and exp cancel out, but how to do it with the summation? Anybody some ideas?

Thank you very much,
Greetings,
Pascal.

 

[Rick Rothstein]

I am guessing that the dot is your thousands separator. If so, the numbers in your cells are way too big for the EXP function. A quick test shows that the largest value it will handle for its argument is 709. What is that calculation actually supposed to be?

 

[RickXL]

Hi,

Does it help if you take out a common factor?

For example:

LN( exp(A1)+exp(A2)+exp(A3)+..+exp(A100.000) )

==> LN(exp(A1) * ( exp(A1-A1)+exp(A2-A1)+exp(A3-A1)+..+exp(A100.000-A1) )

==> A1 + LN( exp(A1-A1)+exp(A2-A1)+exp(A3-A1)+..+exp(A100.000-A1) )

==> A1 + LN( 1 +exp(A2-A1)+exp(A3-A1)+..+exp(A100.000-A1) )

 

[Pascalvrolijk]

Hi Rick,

thanks for thinking with me. The question comes from trying to calculate LN(E[exp(X)]), for X I have created 100.000 drawings from a sample. E stands for expectation. Programmed in Excel this would be LN[1/100.000*( exp(A1)+exp(A2)+exp(A3)+..+exp(A100.000) ) ]
Therefore my question might be a bit mathematic too: "LN(exp(A1)) = A1 because LN and exp cancel out, but how to do it with the summation? Anybody some ideas?"

 

[Pascalvrolijk]

Hi RickXL,

the numbers range from 10 until 15,000,000 so taking out a common factor does not help unfortunately.

 

[Rick Rothstein]

Does it help if you take out a common factor?

In order for that to work, all the values would have to be within 709 of the common factor, otherwise the remaining values to be summed would still have incalculable arguments for the EXP function. Given that the values are in the range of 9 million, I'm guessing they won't all be within 1418 of each other (assuming the common factor was the average of the individual EXP arguments).

 

[Rick Rothstein]

Hi RickXL,

the numbers range from 10 until 15,000,000 so taking out a common factor does not help unfortunately.

Bear with me on this... There is a math problem that should give you some perspective on this. Assume you had a piece of paper 0.1 millimeters thick that was large enough for the task at hand... assume you folded that piece of paper in half, then you folded what you now had in half again, then you folded what you now had in half again, and so on until you had produced 50 such folds... how thick would you guess the wad of folded paper would be? **** Scroll Down To See Answer **** The answer is approximately 3/4 the distance from earth to the sun!!! If I had asked you to fold the paper in half 79 times, the wad of paper would be thicker than the estimated diameter of the observable universe!!! Now think about it... the above problem involves raising 2 to either the 50th or 79th power... EXP(X) involves raising 2.71828.. (a number larger than 2, hence its powers grow faster) to the 15 millionth power! There has to be something wrong with the calculation you are attempting to do as the calculated value for just one of the terms you want to add together would be beyond any known physical entity's size... the resulting number would, I dare say, be [B]far[/B] beyond the calculating ability of any computer now in existence.

 

[RickXL]

Are you expecting your answer to be accurate to over 1400 digits?

If not can you discard some of the smaller terms?

 

[Rick Rothstein]

I should have mentioned that the distances I quoted at the bottom of my scroll area was in miles.

Bear with me on this... There is a math problem that should give you some perspective on this. Assume you had a piece of paper 0.1 millimeters thick that was large enough for the task at hand... assume you folded that piece of paper in half, then you folded what you now had in half again, then you folded what you now had in half again, and so on until you had produced 50 such folds... how thick would you guess the wad of folded paper would be? **** Scroll Down To See Answer **** The answer is approximately 3/4 the distance [COLOR="#0000FF"](in miles)[/COLOR] from earth to the sun!!! If I had asked you to fold the paper in half 79 times, the wad of paper would be thicker than the estimated diameter [COLOR="#0000FF"](in miles)[/COLOR] of the observable universe!!! Now think about it... the above problem involves raising 2 to either the 50th or 79th power... EXP(X) involves raising 2.71828.. (a number larger than 2, hence its powers grow faster) to the 15 millionth power! There has to be something wrong with the calculation you are attempting to do as the calculated value for just one of the terms you want to add together would be beyond any known physical entity's size... the resulting number would, I dare say, be [B]far[/B] beyond the calculating ability of any computer now in existence.

 

[RickXL]

I should have mentioned that the distances I quoted at the bottom of my scroll area was in miles.

I scrolled down quite a long way but it wasn't miles