Using the ISO or U.S. week number is common when calendars and other templates are used for project planning.
Formula for Converting Dollar Amount to Words
The article on Microsoft.com "How to convert a numeric value into English words in Excel" provides some VBA code for converting a dollar value into words, like you would see on checks or receipts.
Calculating Holidays like Victoria Day
I recently was asked how to use an Excel formula to calculate the date for the Canadian holiday Victoria Day.
Annuity Formula
The PV, FV, NPER, RATE, and PMT functions in Excel can be used for both an ordinary annuity (payments made at the end of the period, type=0) and annuity due (payments made at the beginning of the period, type=1).
NPV Formula
To calculate Net Present Value using the NPV formula in Excel, you need a series of periodic cash flows like that shown in the figure below.
IRR Formula
To calculate Internal Rate of Return using the IRR formula in Excel, you need a series of periodic cash flows like that shown in the figure below.
Linear Interpolation In Excel
There are many ways to perform linear interpolation (or extrapolation) in Excel.
Factorial of Positive Non-Integer Values in Excel
I'm in the proces of creating a control chart template and needed to calculate the factorial of a non-integer value in Excel.
Display Feet and Inches as 5' 8 1/2"
When converting metric distances to English units of feet or inches, you'll end up with a decimal value. But, you may want to display the value as feet and inches using the format 5' 8 1/2".
Round Price to the Nearest Nickel
If you want to avoid pennies, you can round prices to the nearest nickel using the following formula:
Calculating Number of Working Days
You can use the NETWORKDAYS() function in Excel to calculate the number of working days (excluding weekends) between and including two dates.
Warning: Excel Performs Negation Before Exponentiation
What does –5² equal? –25 or 25? If you said –25, then you probably listened in school when you were told that negation should be performed on the same level as subtraction, which comes after exponentiation, so –5²=-(5²)=–25.
The BMI Formula
I recently created a BMI Chart with Excel and figured I'd list the formulas below, just in case you couldn't find them on the other 756000 BMI calculator sites.
Highlighting Every Other Row Using Conditional Formatting
Conditional Formatting (accessed via the Format menu) often makes spreadsheets seem to behave like 'magic' because when a user copies cells, the formatting can change automatically without the user knowing why.
Line Break in a Cell Using a Formula
The basic way to add a Line Break within a cell is to press Alt+Enter (on a Mac press Ctrl+Option+Return).
Amortization Calculation
This article provides a basic explanation of how amortization works. The formula for calculating the periodic payment of a loan is given, along with a web-based calculator. The article also explains how to create an amortization table.
Analyze Survey Results with the FREQUENCY Function
Surveys that ask you to rate something on a scale of 1-5 or 1-7 or 1-10 are extremely common. If you need to summarize the results of such a survey quickly, it is very simple to use Excel's FREQUENCY(data_array,bins_array) function to do this.
Calculate Age in Excel
There are many ways to calculate a person's age in Excel. However, most formulas are only valid for birthdates after 1/1/1900, because they rely on serial numbers.
Excel Last Day of Month Formula
The quick answer to finding the last day of the month is to use the EOMONTH() function.
Subscribe to:
Posts (Atom)