6 posts / 0 new
Last post
Unable to deal with scientific notation and large numbers?

I've been really enjoying playing around with the StatPlanet software, and hope to shortly publish something on my website using it. However, the dataset I'm wanting to publish has a very broad range, with values from 8 to 3.24E+65. My data is all in a suitable format and the import data macro has no troubling handling it correctly. However, when I run the flash part it all falls down when it encounters scientific notation. Owing to Excel only handling up to 15 sig figs properly there's no way of representing the largest numbers without the use of scientific notation. The only solution I can think of is to use a mix of units (e.g. tonnes, million of tonnes, etc.), but this would mean a frustrating amount of reclassication of a very large dataset. Does anyone have a solution to this problem?
Cheers, Jannik
P.S. I tried removing the numbers from the dataset and replacing them all with single digit entries, and it worked fine (using the same data structure). This is why I believe it is the scientific notation that is causing the problem.

StatSilk's picture

Hi Jannik, 
You are right StatPlanet does not support scientific notation. If you did not have such a broad range, you could divide all the numbers by x amount and specify this in the unit (e.g. using the method described here http://www.excelforum.com/excel-programming/519994-convert-percentage-to-number.html). However I guess this would not be ideal when you also have small numbers in the same data set. What about just using the actual numbers without scientific notation?
Best,
Frank

Thanks for your reply Frank. Unfortunately, by default, Excel stores numbers over 15 digits in scientific form. I don't believe there is any way around that. I guess this means I'll just have to bite the bullet and use a variety of different units for each indicator, and spend a while reclassifying them. Can I suggest that this problem be looked at further for future versions of the software?
Thanks, Jannik

StatSilk's picture

Hi Jannik,
It does convert them automatically, but you should be able to convert it back by setting the format to 'number' (right click -> format cells -> number). StatPlanet does not read the Excel file directly, but rather the 'data.csv' file. You could also check the data.csv file to see how it shows up once it is saved in this format (after clicking on 'Save data').
Best,
Frank

Changing the format of the cells to number only changes how they are displayed. Excel still stores numbers over 15 digits in the scientific form. Consequently, the generated CSV still has the scientific notation. The only way I can think around this is to generate the CSV using other software. However, then I couldn't take advantage of the speedy reformatting in the import data tool. Changing all of the data to match by other means would probably take even longer than using the mixed units approach mentioned above.

StatSilk's picture

Changing to number format does remove the scientific notation. You can check this by opening the data.csv file in notepad (which shows the true format - opening the data.csv in Excel will automatically convert it into scientific notation again). I have tested the one example you provide above (3.24E+65) and it shows up differently depending on whether you use the number formatting or not. However you mention it fails completely, so it sounds like there may be another issue here. Perhaps you can send the file so I can check the issue.
Best,
Frank

USER LOGIN