VBA to Clear Negative Values with Negative Exponent

[jski21]

Good morning Mr. Excel Team,

I think I have an unusual request. I work for a large governmental organization. I've made great strides in getting their data management and reporting up to speed. One issue I've come across is there is frequently large negative numbers in the data sets. The cell shows "0.00" however, if you select the cell, you can see the negative number carried out to 8, 9, sometimes 10 decimal places along with a negative exponent. An example would be -6.40284270048142E-10. I'm working towards getting this cleared up (easier said than done in a large govt. org.), but for all intents and purposes, I treat this field truly as 0 for analysis and reporting purposes. I find myself sorting the often large datasets from smallest to largest and copying/pasting all zero values with a true 0. Has anyone run across this?

My request is does anyone have/seen VBA code that would search for these values in a dataset and convert them to zero?

Thanks in advance for your advice and insight on this rather strange issue.


jski

 

[Joe4]

Those aren't "large negative numbers", but rather "miniscule negative numbers" that round to zero.

You may have this situation going on: Floating-point arithmetic may give inaccurate result in Excel - Office
Note the two solutions offered in that link.

 

[jski21]

Good deal Joe4. Thanks.

 

[Joe4]

You are welcome.