Article ID: 214260 - Last Review: June 23, 2005 - Revision: 4.0

HOW TO: Create Functions to Calculate Light Years in Excel 2000

View products that this article applies to.

This article was previously published under Q214260

SUMMARY

This step-by-step article shows you how to create functions in Excel 2000 to calculate light years.

Microsoft Excel does not include a function that converts miles into light years or light years into miles. This article explains how to create custom functions to make these calculations.

Back to the top

Example Calculations

The following formulas give the conversion rate between miles and light years

   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.

Suppose that 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.

If you type the following formula into a cell on a worksheet, including the information about Alpha Centauri shown earlier in this article, the formula returns a total of 25,271,100,000,000 miles:

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

Back to the top

Create 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 with Microsoft Visual Basic for Applications, type 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

Back to the top

Use the Custom 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, click Cells on the Format menu. On the Number tab, click Custom. Type the format code in the Type box.

Back to the top

REFERENCES

For more information about writing function procedures, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type writing a Function Procedure in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Back to the top