Formulas for Week Numbering in Excel

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-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:

=YEAR(theDate) & "-" & "W" & TEXT(1+INT((theDate - DATE(YEAR(theDate + 4 - WEEKDAY(theDate + 6)), 1, 5) +
WEEKDAY(DATE(YEAR(theDate + 4 - WEEKDAY(theDate + 6)), 1, 3)))/7), "00") & "-" & WEEKDAY(theDate, 2)

For U.S. week numbering, week 1 is the week containing January 1 and day 1 is a Sunday. Here's the formula:

=YEAR(theDate) & "-" & "W" & TEXT(WEEKNUM(theDate, 1), "00") & "-" & WEEKDAY(theDate, 1)

In these formulas, replace "theDate" with a reference to a cell containing the date.

See this Yearly Calendar with Week Numbers for an example of using the week number in a calendar. You can see the ISO week number in action in my daily planner and weekly planner templates.

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.

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 amounts less than 1 million into words without VBA. I wasn't able to extend this to numbers larger than a million because the formula exceeded the maximum length.

I use the formula in my new Receipt Template spreadsheet, where I highly doubt anyone would need to write a receipt for a value over a million.

FIRST, you need to define two named ranges:

numbers={"";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine";"Ten";"Eleven";"Twelve";"Thirteen";"Fourteen";"Fifteen";"Sixteen";"Seventeen";"Eighteen";"Nineteen"}
tens={"";"";"Twenty";"Thirty";"Forty";"Fifty";"Sixty";"Seventy";"Eighty";"Ninety"}

THEN, you can use one of the following formulas, where cell B8 contains the numeric value. Important: The value in B8 must already be rounded to the nearest cent (0.01). A value like 999.995 will mess up the formula.

Style 1: ... and ../100
=IF(OR(B8>1000000,B8<0),"Err: Out of Range",IF(B8<1000,"",IF(MOD(B8,1000000)>=100000,INDEX(numbers,TRUNC(MOD(B8,1000000)/100000,0)+1)&" Hundred","")&IF(MOD(B8,100000)>=20000," "&INDEX(tens,TRUNC(MOD(B8,100000)/10000,0)+1)&IF(MOD(MOD(B8,100000),10000)>1000,"-"&INDEX(numbers,TRUNC(MOD(MOD(B8,100000),10000)/1000,0)+1),""),IF(MOD(B8,100000)>=1000," "&INDEX(numbers,TRUNC(MOD(B8,100000)/1000,0)+1),""))&" Thousand") & IF(B8<1,"Zero Dollars",IF(MOD(B8,1000)>=100," "&INDEX(numbers,TRUNC(MOD(B8,1000)/100,0)+1)&" Hundred","")&IF(MOD(B8,100)>=20," "&INDEX(tens,TRUNC(MOD(B8,100)/10,0)+1)&IF(MOD(MOD(B8,100),10)>1,"-"&INDEX(numbers,TRUNC(MOD(MOD(B8,100),10),0)+1),""),IF(MOD(B8,100)>=1," "&INDEX(numbers,TRUNC(MOD(B8,100),0)+1),""))&"") & " and " & IF(MOD(B8,1)<0.01,"NO",ROUND(MOD(B8,1)*100,0)) & "/100")

Example: B8=123,456.78
Result: "One Hundred Twenty-Three Thousand Four Hundred Fifty-Six and 78/100"

Style 2: ... Dollars and ... Cents

=IF(OR(B8>1000000,B8<0),"Err: Out of Range",IF(B8<1000,"",IF(MOD(B8,1000000)>=100000,INDEX(numbers,TRUNC(MOD(B8,1000000)/100000,0)+1)&" Hundred","")&IF(MOD(B8,100000)>=20000," "&INDEX(tens,TRUNC(MOD(B8,100000)/10000,0)+1)&IF(MOD(MOD(B8,100000),10000)>1000,"-"&INDEX(numbers,TRUNC(MOD(MOD(B8,100000),10000)/1000,0)+1),""),IF(MOD(B8,100000)>=1000," "&INDEX(numbers,TRUNC(MOD(B8,100000)/1000,0)+1),""))&" Thousand") & IF(B8<1,"Zero Dollars",IF(MOD(B8,1000)>=100," "&INDEX(numbers,TRUNC(MOD(B8,1000)/100,0)+1)&" Hundred","")&IF(MOD(B8,100)>=20," "&INDEX(tens,TRUNC(MOD(B8,100)/10,0)+1)&IF(MOD(MOD(B8,100),10)>1,"-"&INDEX(numbers,TRUNC(MOD(MOD(B8,100),10),0)+1),""),IF(MOD(B8,100)>=1," "&INDEX(numbers,TRUNC(MOD(B8,100),0)+1),""))&" Dollars") & IF(MOD(B8,1)>0," and"&IF(MOD(B8,1)>=0.2," "&INDEX(tens,MOD(B8,1)*10+1)&IF(MOD(MOD(B8,1)*100,10)>=0.5,"-"&INDEX(numbers,MOD(MOD(B8,1)*100,10)+1),""),IF(MOD(B8,1)*100>=1," "&INDEX(numbers,MOD(B8,1)*100+1),""))&" Cents"," and Zero Cents"))

Example: B8=123,456.78
Result: "One Hundred Twenty-Three Thousand Four Hundred Fifty-Six Dollars and Seventy-Eight Cents"

You could create a formula in Excel that would handle much larger numbers if you split the formula into multiple cells. I suspect that the formula could be shortened further with some more effort, but I just don't care enough to do that.

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. 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 to a date or a date created using a function like DATE(2010,5,25).

=Date-MOD(WEEKDAY(Date,1)-DoW,7)

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=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 function can be used to calculate the present value of the annuity. When the payment amount represents withdrawals from a retirement account, the present value would represent the initial principal:

P =PV( i, n, -A, 0, type)

The FV function can be used to calculate the future value of an annuity:

F =FV( i, n, -A, 0, type)

The NPER function can be used to calculate the number of payments:

n =NPER( i, -A, P, 0, type)

The RATE function can be used to calculate the interest rate. It requires iteration and an initial guess at the rate (default is 0.1):

i =RATE(n, -A, P,0, type, guess)

You can also use these functions for an inflation-adjusted annuity payment, which is actually an exponential gradient series cash flow. See Annuity for the details.

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.

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 Present Value, you need to subtract the initial investment or in other words, add the initial negative value at t=0.

The NPV formula requires periodic cash flows. If you have non-periodic cash flows, you can use the XNPV formula.

Download an NPV Calculator for Excel.

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.

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 represents the initial investment. The future cash flows can be negative or positive, but they need to be periodic (occuring at t=1, t=2, t=3, etc). If you have non-periodic cash flows, you can use the XIRR formula.

Download an IRR Calculator for Excel.

Exponential Growth Rate

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.

Linear Interpolation In Excel

There are many ways to perform linear interpolation (or extrapolation) in Excel. The examples below assume that you know at least two points (x₁,y₁) and (x₂,y₂) 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 (y₂-y₁)/(x₂-x₁):

y=y1+(x-x1)*(y2-y1)/(x2-x1)
=B2+(B5-A2)*(B3-B2)/(A3-A2)

2. Using the SLOPE and INTERCEPT functions to solve for m and b:

y=SLOPE(y's,x's)*x+INTERCEPT(y's,x's)
=SLOPE(B2:B3,A2:A3)*B5+INTERCEPT(B2:B3,A2:A3)

3. The TREND function uses linear regression to solve for y if you have two or more points. This is also a way to estimate y from a linear fit of many points.

y=TREND(y's,x's,x,TRUE)
=TREND(B2:B3,A2:A3,B5,TRUE)

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. 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 returns LN(G(x)), so to calculate x! for values greater than zero, you can use

=x*EXP(GAMMALN(x))

Ref: Factorial at Wikipedia.com

Array Formulas

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)))

4. Sequential Number Vector (1, 2, 3, ...): To return an n x 1 vector of sequential numbers starting with 1, use the following formula:
ROW(OFFSET(INDIRECT("A1"),0,0,n,1))
or
ROW(INDIRECT("1:"&n))

Note: The INDIRECT function is used in these examples to avoid problems that would occur if cell A1 were deleted or if rows were inserted above cell A1 or columns were inserted to the left of A1.