Using PRODUCT for large data set

**readams07** · 01-06-2014, 06:00 PM

I have a set of 5,800+ data points between 0 and 1 that I would like to multiply together. When I use PRODUCT for the whole set, the formula returns 0. However, I can use a smaller subset of the data to return a very small number. I'm curious if Excel has a closest-number-to-0 or number-of-cells-for-PRODUCT limitation. Is there another way to perform this calculation?

**protonLeah** · 01-06-2014, 10:03 PM

Is the zero in the cell or the formula bar? how many decimal places are you showing? What's the cell format?

**readams07** · 01-07-2014, 12:30 PM

Everything in the sheet is formatted as a number. I have the specific PRODUCT cell formatted in scientific notation with 4 decimals. When I calculate =PRODUCT(N6:N5863), it returns 0.0000E+00. If I calculate =PRODUCT(N1:N500), it returns 8.6668E-248, so I realize I am dealing with really small numbers and theorize that I am getting so small that Excel is just telling me it's basically 0...

**Pepe Le Mokko** · 01-07-2014, 12:42 PM

Perhaps you are the "victim" of Floating point arithmetic as used by MS

**MrShorty** · 01-07-2014, 12:53 PM

Or, perhaps more specifically, according to Excel specifications and limits (http://office.microsoft.com/en-us/ex...495.aspx?CTT=1), the smallest value excel can work with is 2.2251E-308. Anything smaller than this is identically 0. I'm not sure if this is part of the IEEE double precision standard, or if this is specific to Excel.

(On edit: Pepe Le Mokko's link confirms that this is an accepted part of the IEEE standard, so this will be a limitation of most any program/programming language that uses the IEEE double precision standard).

If you want to perform this calculation in Excel, you will probably have to review what scientific notation means, review rules for manipulating exponents, and such. Then build formulas to:

1) Separate the value into mantissa and exponent parts
2) Use the PRODUCT() function to multiply the mantissas
3) Use the SUM() function to add up the exponents.
4) Display as desired, though you will need to recognize that you will not be able to recombine the answer into a single value (a text string will be ok, but not a number).