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

#### Pascalvrolijk

##### Board Regular
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

##### MrExcel MVP
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

##### MrExcel MVP
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

##### Board Regular
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

##### Board Regular
Hi RickXL,

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

#### Rick Rothstein

##### MrExcel MVP
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

##### MrExcel MVP
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... Code: ``````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.``````

Last edited:

#### RickXL

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

If not can you discard some of the smaller terms?

#### Rick Rothstein

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

 Bear with me on this... Code: ``````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.``````

Last edited:

#### RickXL

##### MrExcel MVP
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

1,082,017
Messages
5,362,695
Members
400,686
Latest member
Aakash

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...