Microsoft KB Archive/142152

= How to Create Functions to Calculate Light Years =

Article ID: 142152

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q142152





SUMMARY
Microsoft Excel does not include a function that will convert miles into light years or light years to miles. However, you can use the following formulas for this calculation

Miles to Light Years = M / 5,877,000,000,000

Light Years to Miles = LY * 5,877,000,000,000

where M is the number of miles and LY is the number of light years.



MORE INFORMATION
Suppose you want to calculate the number of miles between the earth and the closest star system, Alpha Centauri. Alpha Centauri is 4.3 light years away.

The following formula typed into a cell on a worksheet, using the information above, will return a grand total of 25,271,100,000,000 miles:

=4.3*5,877,000,000,000

Creating a Function Macro
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To create this function using Visual Basic for Applications, enter the following in a new Visual Basic module: Function LightYearsToMiles(LY) As Double Dim Convert As Double Convert = 5877000000000 'Defines variables LightYearsToMiles = LY * Convert 'Performs computation End Function

Function MilesToLightYears(M) As Double Dim Convert As Double Convert = 5877000000000 'Defines variables MilesToLightYears = M / Convert 'Performs calculation End Function To use the custom function, follow these steps:

 Type the following in a worksheet:

A1: 4.3

A2: =LightYearsToMiles(A1)

A3: =MilesToLightYears(A2)

 The total number of miles, located in cell A2, between Earth and Alpha Centauri is 25,271,100,000,000 miles. The number of light years, located in cell A3, is 4.3. Format cell A2 with the following format code: ###,###,###,###,###. To do this, select the Format menu, and choose Cells, the Number tab and Custom.

