I have a spreadsheet which when refreshed comes up with scientific notation rather than zero e.g. 2.3283064365387E-10. The data comes from an external system and I can't control the output of that system.

It does display correctly as zero but when I use this data to calculate percentages it comes up with a random %. I can go in an manually change them to zero but was wondering if there was a better way of doing this.

CodePudding user response：

Use CCur in your calculations if no more than four decimals:

? CCur(2.3283064365387E-10)
 0

CodePudding user response：

In the formula that references the cell to calculate percentages, use the Round function to round these numbers to 0, keeping the required level of precision on cells with legitimate numbers.

E.g. If your current formula is referencing the problem value in cell A1 as:

=A1/B1

Modify it to:

=Round(A1,8)/B1

This will still keep 8 decimal places of precision on legitimate number, but will change numbers with exponents less than -8 to exactly 0.