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

Pascalvrolijk

Board Regular
Joined
May 28, 2004
Messages
65
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
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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
Joined
Sep 9, 2013
Messages
4,314
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
Joined
May 28, 2004
Messages
65
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
Joined
May 28, 2004
Messages
65
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
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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
Joined
Sep 9, 2013
Messages
4,314
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
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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:

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top