tag:blogger.com,1999:blog-93125422017-11-12T07:29:36.537-07:00Excel FormulasA blog for publishing examples of Excel formulas.Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.comBlogger23125tag:blogger.com,1999:blog-9312542.post-22222873909750154692011-09-29T08:52:00.004-07:002013-07-13T14:32:33.568-07:00Formulas for Week Numbering in ExcelUsing the ISO or U.S. week number is common when calendars and other templates are used for project planning.
A week number is written as
"YYYY-Www-d" such as 2011-W05-3
where 2011 is of course the year, 05 means the 5th week and the 3 means the 3rd day of the week.
For ISO week numbering, week 1 is defined as the week containing the first Thursday, and day 1 is a Monday. Here's the formula:
Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com1tag:blogger.com,1999:blog-9312542.post-8839860306584761162009-12-14T20:45:00.007-07:002013-07-13T14:34:31.273-07:00Formula for Converting Dollar Amount to WordsThe 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.
I try to avoid using VBA in my spreadsheets whenever possible, and sometimes that leads to some very very long formulas. This one took some doing, but it IS possible to use a formula to convert dollar Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com6tag:blogger.com,1999:blog-9312542.post-71269197713152617152009-11-30T09:19:00.002-07:002013-07-13T14:34:49.607-07:00Calculating Holidays like Victoria DayI recently was asked how to use an Excel formula to calculate the date for the Canadian holiday Victoria Day.
This holiday is defined as the "Monday on or before May 24th".
To handle this type of calculation, I worked out the following general formula for the "Day of the Week on or before a given Date". The variable DoW below equals 1 for Sunday, 2 for Monday, etc. The Date variable is a Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com2tag:blogger.com,1999:blog-9312542.post-13651357269266131882009-10-21T12:01:00.005-07:002013-07-13T14:34:59.011-07:00Annuity FormulaThe 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).
The PMT function can be used to calculate the annuity payment amount given the annual interst rate (i), number of payments (n), and initial principal (P).
A =PMT( i, n, -P, 0, type)
Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com6tag:blogger.com,1999:blog-9312542.post-74235768632385414402009-10-08T15:04:00.003-07:002013-07-13T14:35:08.012-07:00NPV FormulaTo 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.
The NPV can then be calculated using the following formula:
=NPV(rate,values_t1_to_t4)+value_t0
=NPV(D2,B3:B6)+B2
The Excel NPV formula only calculates the sum of the present value of future cash flows (t=1, t=2, ... t=n), so to obtain the true Net Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com1tag:blogger.com,1999:blog-9312542.post-16343520637106353762009-10-08T14:51:00.004-07:002013-07-13T14:35:17.317-07:00IRR FormulaTo 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.
The IRR can then be calculated using the following formula, with 0.1 being the initial guess at the rate:
=IRR(values,guess)
=IRR(B2:B6,0.1)
The IRR formula requires at least one negative and one positive value. Normally, the negative value at t=0 Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com4tag:blogger.com,1999:blog-9312542.post-83552580463176652032009-09-29T11:23:00.004-07:002013-07-13T14:35:52.379-07:00Linear Interpolation In ExcelThere are many ways to perform linear interpolation (or extrapolation) in Excel.
The examples below assume that you know at least two points (x1,y1) and (x2,y2) and want to solve for y given x based on the equation for a line: y=mx+b, where m is the slope and b is the y-intercept.
1. Using the Point-Slope formula, where the slope is (y2-y1)/(x2-x1):
y=y1+(x-x1)*(y2-y1)/(x2-x1)
=B2+(B5-A2)*(Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com5tag:blogger.com,1999:blog-9312542.post-4354876214573910622009-09-14T20:01:00.005-07:002013-07-13T14:36:22.256-07:00Factorial of Positive Non-Integer Values in ExcelI'm in the proces of creating a control chart template and needed to calculate the factorial of a non-integer value in Excel.
Turns out that the factorial function, FACT(), only works for integers. Luckily, you can still get the factorial of a positive non-integer value by using the Gamma function. By definition, x! = G(x+1) = xG(x). In Excel, you have access to the GAMMALN() function which Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312542.post-37380959797692152142009-09-08T08:24:00.012-07:002013-07-13T14:36:32.658-07:00Array FormulasHere are some tips related to the use of Array Formulas in Excel.
1. When using an Array Formula, you press Ctrl+Shift+Enter instead of just Enter after entering or editing the formula.
2. An Array Formula will show curly brackets around the formula in the Formula Bar like this: {=…}
3. Vector of 1's: The following formula will return an n x 1 vector of ones:
(1+0*ROW(OFFSET(INDIRECT("A1"),Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com2tag:blogger.com,1999:blog-9312542.post-6842525048706561282009-07-15T13:57:00.004-07:002013-07-13T14:36:47.271-07:00Display 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".
Let's say you have the decimal value 5.708333 for feet in cell A1. To display feet and inches, use INT(A1) to get the 5 and use A1-INT(A1) to get the remainder (inches). Multiply the remainder by 12 and Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com13tag:blogger.com,1999:blog-9312542.post-31929391523829697012009-05-25T09:11:00.002-07:002013-07-13T14:37:01.332-07:00Round Price to the Nearest NickelIf you want to avoid pennies, you can round prices to the nearest nickel using the following formula:
=ROUND(value/0.05,0)*0.05
If you want to round UP to the nearest nickel, you can change the above formula to use ROUNDUP instead of ROUND, or you can use the CEILING formula:
=CEILING(value,0.05)
=ROUNDUP(value/0.05,0)*0.05
To round DOWN, you can use the FLOOR formula:
=FLOOR(value,0.05)
=Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com2tag:blogger.com,1999:blog-9312542.post-48752996016472021982009-05-23T10:54:00.012-07:002013-07-13T14:41:43.975-07:00Calculating Number of Working DaysYou can use the NETWORKDAYS() function in Excel to calculate the number of working days (excluding weekends) between and including two dates.
=NETWORKDAYS(start_date,end_date,holidays)
In Excel 2000/2002/2003, this function requires the Analysis ToolPak to be installed. The start_date and end_date must be dates stored as serial numbers or defined using the DATE function. The holidays parameterJon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com2tag:blogger.com,1999:blog-9312542.post-38284531391316625422009-04-11T08:25:00.018-07:002013-07-13T14:45:33.029-07:00Warning: Excel Performs Negation Before ExponentiationWhat 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.
If you look up "operator precedence" in Excel's help system, you'll see that Excel performs Negation before Exponentiation, so it evaluates –5² as (–5)².
By the way, in Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312542.post-65582680589130354142009-04-09T15:16:00.007-07:002013-07-13T14:43:04.246-07:00The BMI FormulaI 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.
Metric Units: BMI = Weight[kg] / ( Height[m] x Height[m] )
English Units: BMI = 703 x Weight[lb] / ( Height[in] x Height[in] )
If you were wondering, the "703" in the English version of the formula is just a conversion factor. The Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com2tag:blogger.com,1999:blog-9312542.post-49212722258347039312009-03-18T07:52:00.005-07:002013-07-13T14:43:14.607-07:00Highlighting Every Other Row Using Conditional FormattingConditional 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.
That is, unless the user knows about conditional formatting. Anyway, here is a common but useful tip for highlighting every other row within a table. Excel 2007 can autoformat tables Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com3tag:blogger.com,1999:blog-9312542.post-86709843101343980682009-03-14T15:42:00.005-07:002013-07-13T14:43:38.842-07:00Line Break in a Cell Using a FormulaThe basic way to add a Line Break within a cell is to press Alt+Enter (on a Mac press Ctrl+Option+Return).
After pressing Enter, the Wrap Text property will automatically be set.
You can also add a line break in a cell with a formula using the CHAR(10) function and the concatenation function "&". The example below will add a line break between "abc" and "def".
="abc"&CHAR(10)&"def"
When Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com39tag:blogger.com,1999:blog-9312542.post-23056890932636653442007-04-25T21:54:00.002-07:002009-04-11T13:12:43.579-07:00Amortization CalculationThis 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.Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com1tag:blogger.com,1999:blog-9312542.post-1162444210243295942006-11-01T21:36:00.001-07:002013-07-13T14:44:08.547-07:00Analyze Survey Results with the FREQUENCY FunctionSurveys 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.
Below is an example where the ratings are integers between 1 and 5. To use the frequency function, follow these 3 steps:
1) Select cells A1:A5
2) Enter Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com1tag:blogger.com,1999:blog-9312542.post-1123983107292621062005-08-13T18:05:00.001-07:002017-09-01T19:55:05.962-07:00Calculate Age in ExcelThere 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.
UPDATE 9/1/2017: See Use DATEDIF to Calculate Age in Excel for a more detailed explanation of various formulas for calculating age.
For Birthdates After 1900
To calculate the age of a person on date since their birthdate:
=INT((Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com55tag:blogger.com,1999:blog-9312542.post-1123823845766665072005-08-11T22:04:00.001-07:002013-07-13T14:50:15.384-07:00Excel Last Day of Month FormulaThe quick answer to finding the last day of the month is to use the EOMONTH() function.
For example, to determine the serial date number for the last day of the current month, use
=EOMONTH(NOW(),0)
To avoid using the EOMONTH formula, you can use the fact that the zeroth day of the next month is the last day of the current month:
=DATE(YEAR(NOW()),MONTH(NOW())+1,0)
There are many ways to do Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com3tag:blogger.com,1999:blog-9312542.post-1123821615847334242005-08-11T21:32:00.001-07:002009-04-11T13:08:43.838-07:00Adding Months to a Date in ExcelAdding days to a date in Excel is pretty straight forward. For example, the formula for adding 45 days would be =xldate+45 where xldate is the named reference to a standard Excel date.Adding months to a date is almost as easy. Let's say you want to add 5 months to today's date (8/11/2005). The formula is =DATE(YEAR(xldate),MONTH(xldate)+5,DAY(xldate)) This formula works because 13/11/2005 is Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com25tag:blogger.com,1999:blog-9312542.post-1123567629408952712005-08-08T22:32:00.001-07:002013-07-13T14:47:50.749-07:00Julian Calendar Date Conversion FormulasThere are three main uses of the term "Julian Date": (1) A date in the Julian Calendar (2) Another term for "Julian Day Number" which is the number of days since noon GMT on Jan 1, 4713 BC, and (3) A common date format used in the computer indusry "yyyyddd" or "yyddd".
Peter Meyer's article, "Julian Day Number" includes references suggesting that the third use should no longer be used and that Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com4tag:blogger.com,1999:blog-9312542.post-1101367068822975662004-11-25T01:17:00.001-07:002009-04-11T13:21:26.764-07:00Rounding Significant Figures in ExcelUse the following formula to round a value to a certain number of sigfigs:=ROUND(value,sigfigs-(1+INT(LOG10(ABS(value)))))Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com2