tag:blogger.com,1999:blog-93125422012-01-31T08:45:54.606-07:00A blog for publishing examples of Excel formulas.Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.comBlogger311100tag:blogger.com,1999:blog-9312542.post-22222873909750154692011-09-29T08:52:00.004-07:002011-09-29T09:01:11.796-07:00

Using 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-3where 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:=YEAR(

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312542.post-8839860306584761162009-12-14T20:45:00.006-07:002009-12-16T14:37:15.444-07:00

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.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.com3tag:blogger.com,1999:blog-9312542.post-71269197713152617152009-11-30T09:19:00.002-07:002009-11-30T09:29:08.502-07:00

I 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 25th".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 reference

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com1tag:blogger.com,1999:blog-9312542.post-13651357269266131882009-10-21T12:01:00.003-07:002009-10-21T12:20:35.664-07:00

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=1) and annuity due (payments made at the beginning of the period, type=0).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)The PV

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com3tag:blogger.com,1999:blog-9312542.post-74235768632385414402009-10-08T15:04:00.003-07:002009-10-08T15:12:30.666-07:00

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.The NPV can then be calculated using the following formula:=NPV(rate,values_t1_to_t4)+value_t0=NPV(D2,B3:B6)+B2The 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 Present Value

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com1tag:blogger.com,1999:blog-9312542.post-16343520637106353762009-10-08T14:51:00.004-07:002009-10-08T15:02:48.063-07:00

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.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.com1tag:blogger.com,1999:blog-9312542.post-64389090723371707362009-10-01T09:57:00.003-07:002009-10-01T09:59:24.233-07:00

This article explains how to use the LOGEST function in Excel to calculate exponential growth rate, how to get the growth rate from an exponential curve fit, and how to use Excel's GROWTH function to make future predictions.

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312542.post-83552580463176652032009-09-29T11:23:00.004-07:002009-09-29T11:49:06.014-07:00

There 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)*(B3-B2)/(

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312542.post-4354876214573910622009-09-14T20:01:00.004-07:002009-09-14T20:25:14.608-07:00

I'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.011-07:002009-10-15T10:55:30.074-07:00

Here 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"),0,0,n,1)))

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com1tag:blogger.com,1999:blog-9312542.post-6842525048706561282009-07-15T13:57:00.004-07:002009-07-15T14:45:45.341-07:00

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). Multiple the remainder by 12 and then

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com4tag:blogger.com,1999:blog-9312542.post-31929391523829697012009-05-25T09:11:00.002-07:002009-05-25T09:20:11.139-07:00

If you want to avoid pennies, you can round prices to the nearest nickel using the following formula:=ROUND(value/0.05,0)*0.05If 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.05To round DOWN, you can use the FLOOR formula:=FLOOR(value,0.05)=ROUNDDOWN(

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com1tag:blogger.com,1999:blog-9312542.post-48752996016472021982009-05-23T10:54:00.012-07:002009-05-25T09:10:33.307-07:00

=NETWORKDAYS(start_date,end_date,holidays)You can use the NETWORKDAYS() function in Excel to calculate the number of working days (excluding weekends) between and including two dates. 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 parameter is

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com2tag:blogger.com,1999:blog-9312542.post-65712542417394557422009-04-30T09:40:00.003-07:002009-04-30T09:49:06.424-07:00

My latest article, "Formulas for Dynamic Named Ranges", explains how to use the OFFSET function to create a named range that can expand to the last Numeric value, Text value, or Non-Blank cell within a column or row. Although there are many possible applications of this technique, I recently used it to create a meal planner where the cells within the planner reference a dynamic named range to

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312542.post-38284531391316625422009-04-11T08:25:00.018-07:002009-04-11T13:23:16.506-07:00

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.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 case

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312542.post-65582680589130354142009-04-09T15:16:00.007-07:002009-04-09T15:36:14.113-07:00

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.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 more exact

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com2tag:blogger.com,1999:blog-9312542.post-49212722258347039312009-03-18T07:52:00.005-07:002009-04-11T13:18:12.546-07:00

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. 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 quite

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com3tag:blogger.com,1999:blog-9312542.post-86709843101343980682009-03-14T15:42:00.005-07:002009-04-11T13:12:15.831-07:00

The 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 using a

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com10tag:blogger.com,1999:blog-9312542.post-23056890932636653442007-04-25T21:54:00.002-07:002009-04-11T13:12:43.579-07:00

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.

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312542.post-1162444210243295942006-11-01T21:36:00.001-07:002009-04-11T13:10:37.719-07:00

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. 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:A52) Enter the

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com1tag:blogger.com,1999:blog-9312542.post-1155939382219603502006-08-18T15:09:00.001-07:002009-04-11T13:10:14.906-07:00

I created a condensed list of all the finance functions and formulas in Excel, including those that are in the Analysis ToolPak. This list is oganized into subcategories. Some of the function names are cryptic, so I underlined the letters in the description that make up the function name. A more detailed list is available in Excel's Help system.

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312542.post-1149571032772520882006-06-05T22:13:00.001-07:002009-04-11T13:20:14.022-07:00

This online calculator was converted from an Excel spreadsheet that implemented a very basic formula for calculating a person's Body Mass Index (BMI). The formula is provided below the calculator and is simply a person's weight (in kilograms) divided by their height (in meters) squared, or W/H^2. Other units require some conversion factors, of course.Updated: see http://

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com1tag:blogger.com,1999:blog-9312542.post-1134111802835400432005-12-08T23:55:00.001-07:002009-04-11T13:21:43.699-07:00

If you download and install the free MSN Money Stock Quote Add-in, you will be able to use the MSNStockQuote() function to pull stock quote data directly into your worksheet. One of the benefits of using this function over a web query is that you can place this function wherever you want within your spreadsheet.The following article provides more information about downloading stock quote data

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com3tag:blogger.com,1999:blog-9312542.post-1123983107292621062005-08-13T18:05:00.001-07:002009-04-11T13:09:09.192-07:00

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.For Birthdates After 1900To calculate the age of a person on date since their birthdate:=INT((date-birthdate)/365.25)To figure their age as of today, date can be replaced with a function like TODAY() or NOW(). The INT() function is used

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com31tag:blogger.com,1999:blog-9312542.post-1123823845766665072005-08-11T22:04:00.001-07:002009-04-11T13:08:56.531-07:00

The quick answer to finding the last day of the month is to use the EOMONTH formula, which is part of the Analysis ToolPak Add-in. 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(

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com1tag:blogger.com,1999:blog-9312542.post-1123821615847334242005-08-11T21:32:00.001-07:002009-04-11T13:08:43.838-07:00

Adding 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.com15tag:blogger.com,1999:blog-9312542.post-1123567629408952712005-08-08T22:32:00.001-07:002009-04-11T13:08:30.197-07:00

There 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-1105828046885632792005-01-15T15:27:00.001-07:002009-04-11T13:15:51.377-07:00

Download an example spreadsheet showing how to use COUNTIF to count the number of values in an array that are between two numbers.

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312542.post-1103186544617268452004-12-15T22:36:00.001-07:002009-04-11T13:20:46.829-07:00

This page on AndrewsExcelTips.com lists many useful date and time formulas, text manipulation formulas, and formulas for conditional formatting.

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.comtag:blogger.com,1999:blog-9312542.post-1101367068822975662004-11-25T01:17:00.001-07:002009-04-11T13:21:26.764-07:00

Use 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.com1tag:blogger.com,1999:blog-9312542.post-1101337703432502242004-11-24T15:47:00.001-07:002004-11-26T15:22:41.080-07:00

The purpose of this web log is to provide an archive of useful Microsoft Excel formulas. This log can provide a convenient method for quickly posting solutions to problems in Excel or linking to websites that contain useful formulas for solving a variety of problems in Excel. The exciting thing about this web log is that it can be used for RSS newsfeeds to other websites. So, if you are an Excel

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0