XL2000: How to Create Functions to Calculate Light Years

ID: Q214260

The information in this article applies to:

Microsoft Excel 2000

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft Support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

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

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.

Additional query words: XL2000

Keywords : xlformula
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto