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.

 

[Pascalvrolijk]

thank you guys for thinking with me,

about the long distance problem: LN(EXP(1,000,000)) can be calculated with a little trick; it equals 1,000,000. I had kind of hoped for such a trick for this problem..

 

[RickXL]

Hi,

The only way there would be a "trick" is if the numbers formed some sort of series that someone had derived a formula for.

However, just looking at the numbers, you have a bit of a problem. If you use my suggestion and remove a common factor - and this should be the largest number in the sequence - you might get the following scenario.
A1=15,000,000
A2=14,000,000

So A2-A1 = -1,000,000

But exp(-1,000,000) is a number with a decimal point followed by about 430,000 zeros. (i.e. four hundred and thirty thousand). That is a very small number. If you add it to 1 you will not see any difference in Excel or most other computer programs. The smaller numbers will be even smaller than that.

Unless you have several numbers which are very close to the largest number then I don't think you need a program at all. You just take the largest number as the answer.

If you do want to calculate it then you could use something like this:

Excel 2013
	A	B	C	D
1	A	Diff	Formula 1	Formula 2
2			#NUM!	15000000.00
3	15,000,000		#NUM!	0
4	14,000,000	-1000000	#NUM!	0
5	1	-14999999	2.718281828	0
Sheet3

Cell Formulas
Range	Formula
C2	=LN(SUM(C3:C121))
C3	=EXP(A3)
C4	=EXP(A4)
C5	=EXP(A5)
D2	=A3+D3
D3	=LN(1+SUM(D4:D121))
D4	=EXP(B4)
D5	=EXP(B5)
B4	=A4-$A$3
B5	=A5-$A$3

The yellow cells contain the answers.
Note: Column C with "Formula 1" in it is working out the answer directly. That is, without using any factorization. If you make the numbers in column A small enough it will produce answers that match those in column D.

 

[shg]

There's a simple log identity for the sum of two numbers (any base):

log(a+b) = log(a) + log(1+b/a)

So

ln(exp(a) + exp(b)) = ln(exp(a)) + ln(1 + exp(b)/exp(a)) = a + ln(1 + exp(b-a))

Extrapolating from that, and because exp(negative big number) underflows gracefully,

ln(exp(maxnum) + exp(b) + exp(c) + exp(d) ...) = maxnum + ln(1 + exp(b - maxnum) + exp(c - maxnum) + exp(d - maxnum) + ...)

So ...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td]

10,074.00​

[/td][td]A1: =RANDBETWEEN(10000, 10100)[/td][/tr]
[tr][td]

2​

[/td][td]

10,014.00​

[/td][td][/td][/tr]
[tr][td]

3​

[/td][td]

10,026.00​

[/td][td][/td][/tr]
[tr][td]

4​

[/td][td]

10,088.00​

[/td][td][/td][/tr]
[tr][td]

5​

[/td][td]

10,003.00​

[/td][td][/td][/tr]
[tr][td]

6​

[/td][td]

10,044.00​

[/td][td][/td][/tr]
[tr][td]

7​

[/td][td]

10,000.00​

[/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

10,057.00​

[/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

10,073.00​

[/td][td][/td][/tr]
[tr][td]

10​

[/td][td]

10,090.00 ​

[/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

10,042.00​

[/td][td][/td][/tr]
[tr][td]

12​

[/td][td]

10,067.00​

[/td][td][/td][/tr]
[tr][td]

13​

[/td][td]

10,018.00​

[/td][td][/td][/tr]
[tr][td]

14​

[/td][td]

10,059.00​

[/td][td][/td][/tr]
[tr][td]

15​

[/td][td]

10,018.00​

[/td][td][/td][/tr]
[tr][td]

16​

[/td][td][/td][td][/td][/tr]
[tr][td]

17​

[/td][td]

10,090.76​

[/td][td]A17: =MAX(A1:A15) + LN(1 + SUMPRODUCT(EXP(A1:A15 - MAX(A1:A15))))[/td][/tr]
[/table]

I generated numbers close together because the largest is very dominant.

 

[shg]

Oops: when you subtract the largest number from each of the others, including itself, you have one too many numbers, so the LN(1+...) needs to become just LN(...)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td]

10,074.00​

[/td][td]A1: =RANDBETWEEN(10000, 10100)[/td][/tr]
[tr][td]

2​

[/td][td]

10,014.00​

[/td][td][/td][/tr]
[tr][td]

3​

[/td][td]

10,026.00​

[/td][td][/td][/tr]
[tr][td]

4​

[/td][td]

10,088.00​

[/td][td][/td][/tr]
[tr][td]

5​

[/td][td]

10,003.00​

[/td][td][/td][/tr]
[tr][td]

6​

[/td][td]

10,044.00​

[/td][td][/td][/tr]
[tr][td]

7​

[/td][td]

10,000.00​

[/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

10,057.00​

[/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

10,073.00​

[/td][td][/td][/tr]
[tr][td]

10​

[/td][td]

10,090.00 ​

[/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

10,042.00​

[/td][td][/td][/tr]
[tr][td]

12​

[/td][td]

10,067.00​

[/td][td][/td][/tr]
[tr][td]

13​

[/td][td]

10,018.00​

[/td][td][/td][/tr]
[tr][td]

14​

[/td][td]

10,059.00​

[/td][td][/td][/tr]
[tr][td]

15​

[/td][td]

10,018.00​

[/td][td][/td][/tr]
[tr][td]

16​

[/td][td][/td][td][/td][/tr]
[tr][td]

17​

[/td][td]

10,090.13​

[/td][td]A17: =MAX(A1:A15) + LN(SUMPRODUCT(EXP(A1:A15 - MAX(A1:A15))))[/td][/tr]
[/table]

 

[RickXL]

So shg and I both get: 10090.1269281467 using shg's data and showing all the decimal places. (No, I don't know if they are all correct!)

It just shows how, even if the numbers are close together, just picking the largest one is quite close to the answer.

 

[Pascalvrolijk]

thank you shg and RickXL,
It was a good idea, but unfortunately the differences between the numberes: (b-maxnum), (c-maxnum) ... are just to big to follow the proposed method. I generated 100,000 drawings for 5 variables. And for the first variable the minimum is 2,000,000 and the maximum is 35,000,000. Another variable differs between 800,000 and 1,800,000. All need to have the same calculation. I have been thinking about scaling the numbers before calculating the exponent but that would mess up everything, it wouldnt be the same calculation anymore..

Do you have any more ideas maybe?

 

[Pascalvrolijk]

EUREKA!!!

I have the answer finally: LN(EXP(X1)+EXP(X2)..+EXP(X100,000)) < LN[100,000*EXP(MAX(X1:X100,0000))]=LN[100,000] + LN[EXP(MAX(X1:X100,0000))]=11.51+MAX(X1:X100,0000) and 11.51 is negligable.

Therefore LN(EXP(X1)+EXP(X2)..+EXP(X100,000)) can well be estimated by the value of the largest number

Thanks guys!

 

[RickXL]

Therefore LN(EXP(X1)+EXP(X2)..+EXP(X100,000)) can well be estimated by the value of the largest number

Is that so different from what I have been saying?

Unless you have several numbers which are very close to the largest number then I don't think you need a program at all. You just take the largest number as the answer.

It just shows how, even if the numbers are close together, just picking the largest one is quite close to the answer.

 

[shg]

The largest the result can be if there are N terms is = largestValue + ln(N)

So if your largest number is 1,500,000 and you have 100,000 values, the result can be no larger than ~ 1,500,012