Large numbers cause #NUM! Error

[gozparlak]

Firstly, I multiply 0,003*0,005*x,xxx*.....................x,xxx ( 250 Pcs number each other). So, I provide a very huge number as a result.

Secondly, I take Log (X) the result. Then, Excel give me #NUM! Error. Do you know the reason or could you show me a way to solve the problem.

Thank you very much, in advance.

Best Regards

[MrShorty]

I am not sure I can recreate the problem. I put =LOG(10^A1) into B1. As long as A1 is between -307 (representing 1E-307 a very small number) and 308 (representing 1E308 a very large number), then I get no error from the LOG() function. If I go outside of those limits (which are basically the limits of double precision floating point numbers), then I get the error. So, that is my first thought -- is that you are simply exceeding the limits of double precision values.

If this is the case, then the workaround might be to review your basic properties of logarithms (http://www.purplemath.com/modules/logs.htm and http://www.purplemath.com/modules/logrules.htm ), recognize that the logarithm of a product is the sum of the separate logarithms, and turn your multiplication problem into a summation problem instead.

If that is not the case, then I think we will need a better example of what you are seeing with actual numbers.

[gozparlak]

Thank you very much MrShorty. I sent a sample of my studying.

If I delete half of my data series, I can provide a number. Other hand, I get the #NUM! error.

I cant understand excatly this

[Glenn Kennedy]

G2 is equal to zero. Therefore H2 is equal to zero,. Therefore you get an error at I2. I don't understand what you are trying to do at H2, but clearly, it is mathematically incorrect!!

[gozparlak]

Thank you Glenn Kennedy.

There are 214 numbers Column E. I multiply them each other in G2. Then, I get so minimal number. But, it is not equal to Zero.

If you check the formula G2, It musnt be equal to Zero. I am right?

[Glenn Kennedy]

When you multiply 0.1 by 0.1, you get 0.01. So with evey multiplication, your number gets smaller and smaller. It is not a large number. It is an incredibly small number that Excel eventually regards as zero.

[Glenn Kennedy]

See sheet. By the time you get to row 60, Excel can no longer display th enumber, it is so small. by the time you get to row 145 Excel regards it as zero.

[Glenn Kennedy]

Maybe you should explain what you THINK you are doing. Clearly what you ARE doing is incorrect.

[gozparlak]

Yes, Glenn you are right that Excel eventually regards as zero. Is there any remedy that Excel see them different then Zero?

[Glenn Kennedy]

What are you trying to do????????

How many times do I have to ask you?

[gozparlak]

this is a part of Sentiment Analyze of Twitter Msgs. If the twitter message include a positive words, then I give 1. If not then zero.

Second step, I have to multiply all of value each other according to Binom Model.

[AliGW]

Having zero values in any multiplication will give a zero result. Sounds to me like the whole process is flawed.

[gozparlak]

You are right AliGW that Having zero values in any multiplication will give a zero result. But I dont have any zero number in column E as you see. The Problem is incredibly small numbers and Excel eventually regards as zero. But I dont want this

[Glenn Kennedy]

Please explain the purpose of the formula in E2...

[gozparlak]

I cant send the orjinal Excel, Bcs there is upload limit in the Website, but I am sending a print screen. I hope it can be clear.

Thank you very much

[MrShorty]

I will agree with the others, this is a classic case of underflow error, due to the limitations of double precision floating point.

Did you look at the solution I first suggested: converting the log(product) into a sum(logs)? By doing this, I can see that the result of this calculation is about 10^-435, which I can never work with in double precision, but as long as I work the calculation in log-space, I can get a result.

[gozparlak]

Thank you very much MrShorty, could you explain more, I could understand your solution properly

[gozparlak]

when I put LOG (10 ^ A1) instead of LN( A1) or LOG ( A1), I dont take any error warn. But I cant understand difference as mathematical.

[MrShorty]

The first part of understanding my solution is to make sure you review the algebra tutorials I linked to above so you understand the properties of logarithms (links again http://www.purplemath.com/modules/logs.htm http://www.purplemath.com/modules/logrules.htm ). In particular, the solution depends on the property of logs that log(a*b*c)=log(a)+log(b)+log(c). If you don't understand that, then you won't understand the solution I am suggesting.

I simply put =LOG(E2) into a suitable cell and copied down to make a nice helper column. Then entered =SUM(helper column) into another cell to get -435.

[MrShorty]

Researching other essays that talk about working around overflow and underflow errors, I came across this page (https://www.codeproject.com/Articles...nt-Programming ) that linked to this page specifically about calculating binomial probabilities: https://www.johndcook.com/blog/2008/...probabilities/ I'm not sure how applicable these will be to your specific analysis, but perhaps they will give you a place to start researching these kinds of problems from a computer scientist perspective.

[joeu2004]

I dont have any zero number in column E as you see. The Problem is incredibly small numbers and Excel eventually regards as zero. But I dont want this

It is "unavoidable" arithmetically because that is the way the hardware (CPU), not Excel, works: if the arithmetic combination of values (multiplication, in this case) is less than 2^-1022 (about 2.22E-308), it is an "underflow", and the CPU stores zero instead.

You might change the formula in H2 as follows:

=MAX(G2*D2, 2^-1022)

But I think the following is better:

=IF(G2*D2=0, "", G2*D2)

You would also need:

=IF(H2="", "", LOG(H2))

Finally, you might look at your design to determine why you are multiplying numbers that result in such an infinitesimal amount. That is unusual, and it seems unlikely that it is desirable. Perhaps the design needs to be changed in some way.