Large Numbers in Excel

Excel can display large numbers either in full or in scientific notation. As an example, 24,100,000 in scientific notation is 2.41E+07 - which is OK for pure mathematicians and scientists but not for everyday spreadsheet users. Note also that Excel does not automatically enter comma separators in large numbers; should you wish to display large numbers with such a separator, Select the comma icon on the toolbar. Similarly, you will find icons on the toolbar which will allow you to increase or decrease the number of decimal places displayed in the cell.

Open the SolSys.xls spreadsheet. This is set up with particular information about the planets in the Solar System. (Note that it is a protected spreadsheet: password = science.) The radius of each body is displayed as millions of metres, so your first task is to display radii as kilometres in full. Unlock the spreadsheet and insert a new column to the right of the radius column (in reality, Select the column to the right of the radius column before Selecting Columns from the Insert menu).

Now enter the following formula in the cell to the right of the first radius value (C4): =B4*10^6/10^3 which translates into: multiply the contents of cell B4 by 10 to the power of 6 then divide by 10 cubed (mathematicians may know a simpler formula!). Change the value displayed to zero decimal places and comma separated. The image shows the value in B4 (values in millions of metres) converted in C4 to kilometers in full with 1000 separators (comma) and no decimal places. Fill the formula down for the planetary radius data and resave the file. Why does the value for Pluto (1.5) convert to 1,470 kilometers? How could you have made the formula simpler?

This spreadsheet requires that data for Volume and Density be inserted (if you're not sure what the formula is for the volume of a sphere, check out this web site: http://www.projectgcse.co.uk/maths/volume.htm). You should have found that the formula is ⁴/₃r³. Enter this formula for the Sun and each planet in the Volume column. To use pi, Excel recognises the function as PI(). Be careful that you follow the BODMAS rule when entering the Volume formula! If all is well, you should find that the Volume of the Sun is a staggering 1,406,793,894,580,590,000,000,000,000 cubic metres. How would you display these values in a simpler way? Would this affect the accuracy of the data? For more information on planetary statistics, go to this website: http://www.bifrost.co.uk/Gaming/Utils/PlanetStats.html

Now you need to calculate the density of each body in kg per cubic metre! The formula for density is Mass divided by Volume. In the spreadsheet, Mass is displayed as the number of Earth masses (mass of the earth is 6 x 10²⁴kg). How would you enter this equation into the spreadsheet? Display the number with no decimal values and comma separated. Save the spreadsheet. Click here to open the completed spreadsheet.

Finally, create a chart for all of the planets which plots Volume against Density and another which plots Orbital Radius against Surface Temparature.