Category Archives: Formulas

All about formulas and functions

Calculate the Xth Weekday of Any Month in Excel

I’ve a simple formula for calculating the Xth Weekday of ANY month. It takes four inputs: Year, Month, Week, and Day. And requires a couple of lookup tables for data validation, one with special formatting.

Xth Weekday of a Month

The History

It all started when my wife mentioned there were a couple of meetings she had to schedule at work, one on the second Tuesday of every month and the other on the third Tuesday of every month. That got me to thinking about how to create a formula in Excel to calculate those dates.

After getting a partial solution, other things took precedence and it slipped from my mind until Chandoo posted a homework article on how to figure out what date Thanksgiving falls on each year, the 4th Thursday of November, in the USA.

His solution and all the variations provided in the comments (I posted one too) was focused and specific to one day (Thursday) of one week (the 4th) in one month (November). Albeit a link in that article showed another formula solution to Canada’s Thanksgiving day that takes place on the 2nd Monday of October.

This served to spur me on and finish what I started. I hope you like it.

The Inputs

Xth Weekday of the Month

Input for the Year in cell A2 is done manually. Just type in a year.

Input for Month in cell B2 is from a data validation drop-down list of values from 1-12. Merely done for convenience.

Input for Week in cell C2 is from a data validation drop-down list of values from 1-4. These are the only values that work correctly in the formula.

Input for Day in cell D2 is from a data validation drop-down list of values from 1-7 that use the “ddd” custom cell format to convert those numbers into an actual date. (You can’t actually type in a text value because what you’re seeing is a formatted date. Below I’ll show an alternate formula where you can use text values like Sun, Mon, … Sat.)

The Formula

The formula for Date Selected in cell E2 is uses the “ddd, m/d/yyyy” custom date format (US) and the formula is:

=DATE(A2,B2,(C2*7+1)-WEEKDAY(DATE(A2,B2,8-DAY(D2))))

Instead of putting all the calculations in the Day argument of the DATE function, you can also write the formula as DATE – WEEKDAY:

=DATE(A2,B2,(C2*7+1))-WEEKDAY(DATE(A2,B2,8-DAY(D2)))

The Explanation – Why This Works

WEEKDAY Inverse RelationshipThis formula has two parts, subtracting the WEEKDAY from the DATE. They both hinge on the fact that WEEKDAY function values are 1-7 for days Sun-Sat, and that an inverse relationship exists for the first week of the month, as shown by the chart. (Eight minus the WEEKDAY value)

Column three is the Day value, or 3rd argument, of the DATE function inside the WEEKDAY function. This relationship works for every day of the week. For a Tuesday, we have WEEKDAY(DATE(Year, Month, 5)). For a Thursday we have WEEKDAY(DATE(Year, Month, 3)).

The DATE Function relies on the Week number 1-4 to generate a date that is one day past that particular week. C2 * 7 +1, is just giving values 8, 15, 22, or 29.

So for a Thursday Thanksgiving in the USA you have

DATE(Year, 11, 29 – WEEKDAY(DATE(Year, 11, 3)))

and for a Tuesday Thanksgiving in Canada you have

DATE(Year, 10, 15 – WEEKDAY(DATE(Year, 10, 6)))

It helps to visualize this formula in the Evaluate Formula dialog box and step through the formula iteration.

Evaluate Formula Xth Weekday of a Month

The Data Validation

One critical aspect of this formula is to get a numerical date value from cell D2 for the DAY function. I use values 1-7 and convert them to Dates by custom formatting the cells with the “ddd” format.

Format Integers to Day Format

Of course this resulted in my date range being 1 Jan, 1900 to 7 Jan, 1900 and is okay for the Windows Date System.

Note: Excel for Mac users check to make sure you’re not using the 1904 Date System, in Excel>Preferences>Calculation look under the Workbook options to see if Use the 1904 date system is unchecked.

If you want to make this work with either date system (Windows or Mac) then choose a month where Sunday is the first day, like 1 Aug, 2010. You’ll need seven dates ending with 7 Aug, 2010.

Alternate Formula Without Custom Formatting

You can, of course, skip the custom formatting for the Day in cell D2 by using straight text values in the data validation list.

Data Validation Text Date List

This requires a different formula and below I’m utilizing the VLOOKUP function.

=DATE(A2,B2,(C2*7+1)-WEEKDAY(DATE(A2,B2,VLOOKUP(D2,{“Sun”,7;”Mon”,6;”Tue”,5;”Wed”,4;”Thu”,3;”Fri”,2;”Sat”,1},2,FALSE))))

The VLOOKUP reads cell D2 then finds the corresponding value in the constant array and returns a number.

Evaluate Xth Weekday of a Month Alternate

To shorten this up considerably, create a Named Constant, like MyWeekday, to replace the array.

Named Constant Array for MyWeekday

The formula can then be reduced to:

=DATE(A2,B2,(C2*7+1)-WEEKDAY(DATE(A2,B2,VLOOKUP(D2,MyWeekday,2,FALSE))))

I’m sure there are other variations. Do you have a better solution?

Download the Files

Here’s a file with the original formula that you can download.

XLSX File Format: Excel 2007, 2010, 2011

XLS File Format: Excel 2003, 2008

Here’s another file with the alternate formula using the VLOOKUP with constant array.

XLSX File Format: Excel 2007, 2010, 2011

XLS File Format: Excel 2003, 2008

Convert Pace to MPH and Back Again in Excel

Excel is a great tool for figuring stuff out, like for conversions that aren’t easy to do in your head. Here I’m converting Pace to MPH and then reversing the process, converting MPH to Pace, to create a conversion chart.

My Conversion Problem for Minutes per Mile to MPH

I track my Average Pace when out walking for exercise by using the iPhone App Walkmeter, then log that information into the Lose It App. The problem I have is converting my Average Pace to Miles per Hour (MPH).

Below you can see my average pace is 13:22 per mile, but Lose It wants me to pick from a list of MPH values.

Walkmeter AppLose It App minutes per mile to MPH

Simple Conversion Equation

You can use algebra to work out how to convert Pace, in minutes per mile, to MPH.

Pace to MPH Equation

The problem with this is that Walkmeter shows the Average Pace in Minutes and Seconds per mile, which is not decimal minutes per mile.

Converting Minutes and Seconds

There are a couple of ways to convert minutes:seconds to decimal minutes. The first mimics what I would do by using a calculator and the second is strictly an Excel thing to convert minutes per mile to MPH.

Decimal Minutes

Divide the seconds by 60 then add the result to the number of minutes to get decimal minutes.

B2 =MINUTE(A2)+SECOND(A2)/60

Convert EQ 2

This solution uses the Excel Functions MINUTE and SECOND.

Decimal Hours

Convert 13:22 to a time serial number by using the TIME Function, then multiply by 24 to get decimal hours.

B2 =TIME(,MINUTE(A2),SECOND(A2))*24

Convert EQ 3

The TIME Function above has three arguments:

  • Hour, which is blank
  • Minute, which uses the MINUTE Function
  • Second, which uses the SECOND Function

Convert to MPH

Now that I’ve converted minutes and seconds to either decimal minutes or hours, converting Pace to MPH can be completed in a second step.

Using Decimal Minutes

Simply divide 60 min/hr by the 13.37 pace/mile to get 4.49 MPH.

C2 =60/B2

Convert EQ 2

Combining equations in cells B2 and C2 gets us the conversion in one big equation:

MPH =60/(MINUTE(A2)+SECOND(A2)/60)

Using Decimal Hours

Here we simply invert the decimal hours to get our answer, one (1) divided by 0.0222778 decimal hours, gives us 4.49 MPH.

C2 =1/B2

Convert EQ 3

Again, we can combine equations to get:

MPH =1/(TIME(,MINUTE(A2),SECOND(A2))*24)

MPH to Pace Conversion

For a given set of MPH values I want to convert to Pace per mile and show the result in a minutes:seconds format. Essentially reversing what I just did above.

The simplest way to do this is to realize that the time serial number is based on seconds. We’ll also use the fact that 1 hour = 3600 seconds.

When we divide 3600 by an MPH value, it gives us the number of seconds it takes to go one mile. Plugging these seconds into the TIME Function will give us our answer, but as a time serial number. We can then use a custom format of mm:ss for the Pace range and the conversion is complete. The equation for cell B2 is:

=TIME(,,3600/A2)

MPH to Pace Chart

This formula works because column B is formatted using the mm:ss custom format.

Format cells mmss

So now I have a conversion chart for MPH to Pace and can probably remember that a pace of 13:22 is close to 4.5 MPH, which is helpful to me. How about you?

Convert Seconds with the TIME Function in Excel

I often download an iTunes playlist just to see the time increments for each song, but iTunes only gives you the number of seconds for each song duration. In a previous post I spent a great deal of time showing how to use a combination of various formulas to convert these seconds, when it’s really very simple.

Excel Formula to Convert Seconds to Minutes

You see, the Excel formula to convert seconds to minutes can be used easily with the TIME Function. All you have to do is format a worksheet cell with the proper time format.

Excel Formula to Convert Seconds to Minutes

The formula for Min:Sec in cell F2 is shown in this Function Arguments dialog box.

Function Arguments Dialog Box Seconds Conversion

The TIME Function has three arguments, but in this formula the first two (Hour, Minute) are not needed. Excel takes the last argument for Second and converts to a time serial number. You can see the formula result is 03:57 because cell F2 is using a custom time format.

Format Cells Custom Time Format

To format a range with a custom time format that shows minutes and seconds, use the keyboard shortcut Ctrl+1 to bring up the Format Cells dialog box, select the Number tab, and under Category select Custom, then enter mm:ss in the Type text box and click OK.

Bonus Formula and Formatting

The Cum Time column has a custom time format of [h]:mm:ss and the formula in cell G2 is:

=SUM($F$2:F2)

which is copied down to give the cumulative time for all song durations. Adding the [H] allows the Hour to show when the cumulative time adds up to over an hour.

Grade Formulas in Excel

There are several ways to turn student scores into letter grades. I recently came across a nested IF formula that did the trick, but it seemed rather complicated.

A better solution would be to use a VLOOKUP formula with a Grade Lookup Table, but then it occurred to me that the VLOOKUP formula could stand alone by using an Array Constant.

For all these solutions I’m using the following grade scale:

  • 0 – 59 = F
  • 60 – 69 = D
  • 70 – 79 = C
  • 80 – 89 = B
  • 90 – 100 = A

The Nested IF Solution

Using the scale above as a guide, the following nested IF formula will turn a score from 0 to 100 into the correct letter grade.

=IF(Score>=90,”A”,IF(Score>=80,”B”,IF(Score>=70,”C”,IF(Score>=60,”D”,”F”))))

This formula uses Score, which is a named range that contains all the student scores.

A VLOOKUP with a Grade Lookup Table

The following formula will also give the correct letter grades.

=VLOOKUP(Score,GradeLU,2,TRUE)

Grade Lookup Table

GradeLU

This formula has four arguments. Score refers to the student score that’s being looked up.

GradeLU is the Grade Lookup Table that’s on another worksheet and is a named range.

The two (2) means that a number from the second column will be returned from GradeLU.

The TRUE means the student score will be approximately matched to the first column from GradeLU. This is what allows a score of 72 to be matched to 70 and consequently return a letter grade of C.

A VLOOKUP with an Array Constant

The Grade Lookup Table can be replaced with an array constant. In the formula above, the second argument, GradeLU, could be replaced with the following:

{0,”F”;60,”D”;70,”C”;80,”B”;90,”A”}

However, instead of typing this into the formula each time, we can create a Named Constant in the Define Name dialog box shown below.

Define Name dialog box

By creating GradeLookup as a named constant array, the formula can be shortened to:

=VLOOKUP(Score,GradeLookup,2,TRUE)

This formula does not need to reference a separate Grade Lookup Table on a worksheet because all the values are located in the named array constant, GradeLookup, which is now located in Excel’s internal memory.

Create a Named Constant Array

Bring up the Define Name dialog box and type in the name GradeLookup. Then delete the contents in the Refers to: text box and type in {0,”F”;60,”D”;70,”C”;80,”B”;90,”A”} exactly and click OK. Excel will add the equals sign (=).

How you access the Define Name dialog box depends on the version of Excel your using:

  • In Excel 2003, and Excel 2008 and 2011 for Mac, use the menu selection Insert-Name-Define…
  • In Excel 2007 and 2010 click the Ribbon tab Formulas and select Define Name.

Download the File

You can download the file with this link.

Nested IF Functions in Excel

The simplest Nested IF Function is using one IF Function inside another. When you have more than a few choices, nesting more IF Functions can quickly get complicated and, quite frankly, there are better ways to make decisions with Excel. Having said that, I have a simple method to account for the different choices that can arise with nested IF Functions.

Two Conditions – Binary Outcome

Two Conditions Binary OutcomeThe IF Function evaluates a logical test to either TRUE or FALSE. A binary outcome. One condition requires only one IF Function.

With two conditions that both evaluate to TRUE/FALSE you must consider all possible outcomes.

This chart shows there are four different outcomes for Condition 1 and 2. I’ve added a third column to determine what action to take for each outcome.

One Nested IF Function

In this example three actions are required, which means one nested IF Function, so the Action column has the following formula:

=IF(Cond_2=TRUE,”Action 1″,IF(Cond_1=TRUE,”Action 2″,”Action 3″))

Which translates to: if condition 2 is TRUE then do action 1, or if condition 2 is  FALSE and condition 1 is TRUE then do action 2, or if condition 2 is FALSE and condition 1 is FALSE then do action 3.

How Many Actions are Required?

Having two conditions with binary outcomes doesn’t mean there will automatically be three actions for these four possible outcomes. The chart above is designed for you to determine how many actions are required, and then construct the IF statement logic.

Two Nested IF Functions

There could be four different actions needed, which would require two nested IF Functions. Assuming each of the four conditions above has a different outcome, and labeling each Action 1 through 4 from top to bottom, then the following formula will work:

=IF(Cond_1+Cond_2=2,”Action 1″,IF(Cond_1+Cond_2=0,”Action 4″,IF(Cond_1=TRUE,”Action 2″,”Action 3″)))

Which takes advantage of the fact that TRUE + TRUE = 2, FALSE + FALSE = 0, and either combination of TRUE + FALSE = 1.

Three Conditions – Binary Outcome

Three Conditions Binary OutcomeI rarely do more than two nested IF functions because they get complicated. Taking into account the binary nature each outcome, if there are N conditions, then there are 2N possible outcomes that must be accounted for with the formula logic.

I always put together a binary outcome chart when dealing with three conditions. Even if I don’t use nested IF Functions for a sloution, all the outcomes must be considered.

I simply add a column for what action needs to be taken and look to see where the commonality lies for a starting point, then try and compose something to fit.

Excel 2003 can nest up to 7 IF Functions, and Excel 2007 and 2010 both allow 64 nested IF Functions. How anybody could do that many Nested IF’s is beyond me.

If you’re trying to nest more IF Functions than I’ve shown here, I wish you Good Luck.

The IF Function in Excel

The IF Function is used to evaluate a logical test, then if TRUE do something or if FALSE do something else. A familiar construct. The IF Function has three arguments:

IF(logical_test, [value_if_true], [value_if_false])

Argument 1

The first argument, logical_test, is required. It’s any value or expression that can be evaluated to TRUE or FALSE. This argument can use any comparison calculation operator. There are four types:

  • Arithmetic operators ( +   –   *   /   %   ^ )
  • Comparison operators ( =   >   <   >=   <=   >< )
  • Text concatenation operator ( & )
  • Reference operators (colon, comma, space )

Argument 2

The second argument, value_if_true, is optional. This value is returned if the logical_test argument evaluates to TRUE.

I didn’t know this second argument was OPTIONAL, but nevertheless you can omit this argument by placing a comma after the first argument, followed by nothing. An example would be =IF(A1= 5,). But here’s the weird part, if the second argument is omitted and TRUE, the function returns a zero (0), and if the second argument is omitted and FALSE the function returns FALSE.

When TRUE = 1 and FALSE = 0 then everything is right with the Excel world. When TRUE = 0 then things are messed up. I think they messed up here. Although how many people omit the second argument in an IF statement? But I digress.

Argument 3

The third argument, value_if_false, is optional. This value is returned if the logical_test argument evaluates to FALSE.

Two Common Uses for the IF Function

Division by zero is undefined mathematically so Excel provides a glaring #DIV/0! when that happens. And sometimes we don’t want to show the result of a formula unless there’s some actual data to calculate.

Avoid Division by Zero

The intent for column D is to divide Num1 by Num 2, but avoid division by zero. The IF function is looking to see if Num 2 is zero (0) and if so, then zero(0)  is returned, otherwise the calculation is done (Num 1 / Num 2). The formula in cell D2 is =IF(C2=0,0,B2/C2).

IF Function Divison by Zero

Knowing what I do now about omitting the second argument, this formula could be re-written as =IF(C2=0,,B2/C2) with the same result. However, not a very good practice.

More commonly this formula is written differently as =IF(C2<>0,B2/C2,0) with the same result; doing the calculation if Num 2 is not zero (0) and returning a zero (0) when it is.

Calculate or Leave Blank

Many times formulas extend beyond currently entered data. This is where the IF function can be useful. The formula in cell D2 is =IF(C2<>””,B2+C2,””), which adds Num 1 and Num 2 if Num2 is not blank, and returns a blank (“”) otherwise.

IF Function No Blanks

This formula could also be written =IF(C2=””,””,B2+C2) which reads like this: if C2 is blank, then return a blank, otherwise add B2 and C2. Both of these formulas give the same result.

Single-Cell Array Formula in Excel

A Single-cell Array formula is powerful, yet can be hard to understand. The Evaluate Formula dialog box helps by revealing array values Excel holds in internal memory.

In the spreadsheet you see pictured, a single-cell array formula for the Cumulative Plan is located in cell B2. It summarizes the historical and current day’s Plan data. The Cumulative Actual formula, in cell C2, summarizes the Actual data in the same manner.

As an introduction to Array formulas, in my last post I added two columns. The multi-cell array formulas for columns D and E are:

{=$A$2>=A5:A18}

{=D5:D18*B5:B18}

Extra Columns with multi-cell array formulas

Both formulas are entered by selecting their respective ranges, typing in the formula, without curly braces, and using Ctrl+Shift+Enter. Excel provides the curly braces, confirming it’s an array formula.

I didn’t include the current day in my last post, but decided to make a slight change in the column D formula here. (>= instead of >)

In the column D formula, the absolute reference to cell $A$2 could be replaced with the TODAY() function with the same result.

To summarize these two formulas: The Past compares Today’s date to the Date in column A and returns FALSE if the Date is in the future, and TRUE if not.

The MTD Plan multiplies this result times the Plan, and because TRUE = 1 and FALSE = 0, all rows with future date values become zero (0) and otherwise shows the value for the Plan.

A Temporary Single-Cell Array Formula

Summarizing the MTD Plan in a single cell with an array formula will give me a temporary solution for the Cum Plan. In cell B2 I enter the array formula:

{=SUM((D5:D18)*(B5:B18))}

Again, this is done by entering =SUM((D5:D18)*(B5:B18)) and pressing Ctrl+Shift+Enter. Excel provides curly braces.

This formula multiplies together two arrays, then summarizes with the SUM function.

With a single-cell array formula, the Evaluate Formula dialog box allows me to see how this formula is being process by Excel. On the Ribbon, select the Formulas tab, then click Evaluate Formula. In Excel 2003 it’s Tools → Formula Auditing → Evaluate Formula. Excel for Mac doesn’t have this feature.

Evaluate Formula Single cell array step 1

To see the result of the underlined expression, click the Evaluate button. You can see below that D5:D18 is an array that Excel is holding in internal memory. The curly braces are a dead give-away for an array.

Evaluate Formula Single cell array step 2

The next time the Evaluate button is clicked, B5:B18 shows up as a second array with numerical values.

Evaluate Formula Single cell array step 3

Recall from my last post that TRUE = 1 and FALSE = 0. Multiplying these two arrays together gives a single array, which you can see by clicking the Evaluate button again.

Evaluate Formula Single cell array step 4

The SUM function will now evaluate the array. Clicking Evaluate one more time will show the resulting answer, which is 2000.

My Single-Cell Array Formula

I now want to get rid of columns D and E so all references to them have to be replaced. To edit the single-cell array formula for Cum Plan, I select cell B2, click inside the Formula Bar, replace D5:D18 with $A$2>=A5:A18, then press Ctrl+Shift+Enter to get:

{=SUM(($A$2>=A5:A18)*(B5:B18))}

Since cell A2 contains the TODAY() Function, the following formula works as well:

{=SUM((TODAY()>=A5:A18)*(B5:B18))}

The formula’s calculation progression is similar to the previous screen-shots of the Evaluate Formula dialog box.

Single-Cell Array Evaluate Formula

The TODAY() Function evaluates to the date serial number 40490, which is November 8, 2010, and is compared to each cell in the array A5:A18 to get an array of TRUE and FALSE values. The second array B5:B18 is expanded to show the numerical values. These two arrays are multiplied together to get a single array of values, which the SUM Function then evaluates and returns the value 2000 to cell B2.

A single-cell array formula can also be applied to the Act Plan for cell C2, which is:

{=SUM((TODAY()>=A5:A18)*(C5:C18))}

And those are my single-cell array formulas.

Note on Excel for Mac

Apparently there is no Evaluate Formula option in Excel 2008 or 2011 for Mac. I have both programs and it simply doesn’t exist. One of the help forum answers suggested using the F9 key, when editing a formula, to show the values in an array, but that doesn’t work for me. back

Multi-Cell Array Formula in Excel

Arrays are difficult to understand because Excel holds them internally and it’s hard to conceptualize how operations are performed on the data. I had a vexing problem that was solved by using a single-cell array formula and wanted to share it with you.

However, because of the subject nature of Arrays, I’m going to show the solution to this problem in two parts. Here I’ll cover a Multi-Cell Array formula and my next post will talk about a Single-Cell Array formula, which, turns out, is my magic formula.

Array Problem Worksheet Example

The Problem

Here’s an example worksheet. The data here is rather simple: Date, Plan, and Actual. The Plan data is set, and Actual data is plugged in when completed.

My ultimate goal is to have a formula in cell B2 for the Cum Plan, which summarizes Plan history, i.e., for all dates in the past. And keep in mind I need the formula to work with earlier versions of Excel, so no SUMIFS availability.

I’ve added two columns that will hold multi-cell array formulas, The Past and MTD Plan. But first, for reasons that will become apparent later, I’ll cover plain, regular formulas for these two columns.

Some Groundwork

The formula in cell D5 uses a comparison operator (>). The formula is:

=($A$2>A5)

The result is TRUE. $A$2 is an absolute reference to cell A2, where the TODAY() Function resides and merely shows today’s computer clock date. I could have entered =TODAY()>A5 in cell D5 with the same result. When the date for Today is less than or equal to the Date in column A, we get FALSE.

Array Problem Change The PastTRUE and FALSE are binary values, where Excel equates TRUE = 1 and FALSE = 0. You can see this clearly by multiplying the previous formula by 1. (see picture)

=($A$2>A5)*1

Any number multiplied by TRUE is like multiplying by 1, and any number multiplied by FALSE is like multiplying by zero (0).

The formula in column E for MTD Plan is to multiply The Past times Plan. The formula in cell E5 is:

=D5*B5

Entering a Multi-Cell Array Formula

To enter any Array formula requires a special hand-shake, er, key press. You select multiple cells, type the formula, then hold the Ctrl+Shift keys down and press Enter.

The array formula in cells D5:D18 is entered by:

  • Selecting cells D5:D18
  • Typing the formula =$A$2>A5:A18
  • Use keyboard combination Ctrl+Shift+Enter

Which gives you the following formula for the entire range:

{=$A$2>A5:A18}

The curly braces are the result of using the special keyboard combination: Ctrl+Shift+Enter. You don’t enter the curly braces, Excel does this automatically.

The array formula in cells E5:E18 is entered by:

  • Selecting cells E5:E18
  • Typing the formula =D5:D18*B5:B18
  • Use keyboard combination Ctrl+Shift+Enter

Which gives you,

{=D5:D18*B5:B18}

in every cell of the range.

Things to Know About a Muli-Cell Array Formula

  • You must select all cells in the range before you enter the formula
  • You can’t change one cell in a multi-cell array formula
  • You can’t insert a row into the range of a multi-cell array formula
  • To edit: select the range then press F2 (re-enter the array by Ctrl+Shift+Enter)
  • To convert to a regular formula: select the range, press F2, then Ctrl+Enter
  • To select the entire range: select a cell in the range, then press Ctrl+/

Uses for a Multi-Cell Array Formula

I haven’t found the need to use a multi-cell array formula, except for a gateway to understanding a single-cell array. At least with a single-cell array you can use the Evaluate Formula dialog box to see how the Array is working in Excel’s internal memory.

Using some of the concepts I’ve introduced here, my next post will cover a Single-Cell Array Formula that solves my problem of getting one formula to show the Cumulative Plan quantity for all days before today. I promise it will be more satisfying than this post.

Use the DATE Function to Find the Last Day of a Month

Zero has a special meaning in the Excel date system and can be used in the DATE Function to return the last day of the month. The simplest way to show this is to use a formula to find the last day of February in the coming years.

How to Find in Excel the Last Day of Month

If you want to find in Excel the last day of month with cell A2 containing the current year 2010, the following formula is entered into cell B2:

=DATE(A2,3,0)

and returns the result 2/28/2010.

There are three arguments for the DATE Function: Year, Month, Day. Notice that the Month argument is 3, which is March, and the Day argument is zero (0).

DATE Function Dialog Box

The DATE Function will return the last day of the month prior to the Month argument, by using zero (0) for the Day argument.

Bonus Formula – The DAY Function

To see how many days are in February — shown above in column C  — wrap the previous formula inside the DAY Function.

=DAY(DATE(A2,3,0))

A Sparkline Example in Excel 2010

Each week I download the over-under calories report from Lose It! and dump the data into a spreadsheet. I just created my first Sparkline graphic to show the last 7 days of this data.

For this example I’ll use an OFFSET Function inside a named range, which was created in my last couple of posts.

A Little Prep Work for a Dynamic Range

In this post I’ll use the formula:

=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)-7,0,7)

inside the named range: LastSeven, which will return the last 7 data points of column B data, dynamically. As I add more data, the range reference always returns the last 7 data points.

Sparklines are New in Excel 2010

Keep in mind that older versions of Excel won’t be able to see Sparklines, because they’re new in Excel 2010. There are three types of sparklines: Line, Column, and Win/Loss. In my example I’ll use Line, then add markers and show negative values differently, and finally add an axis to give it some definition.

Create a Dynamic Sparkline Graphic

To visually see the last seven data points, I’ve added a Sparkline graphic at the top of my spreadsheet. Here are steps used in my example to create a sparkline graphic in cell C1.

  • Select cell C1, or the range you want the sparkline to go
  • Select the Insert menu, then above Sparklines click Line
  • In the Data Range box, enter =LastSeven (don’t forget the (=) equals sign)
  • The Location Range box should have the correct cell address, if not select it now.
  • Click OK

Create Sparklines Dialog Box

Here’s what I got in cell C1 by doing all of that.

Sparkline Line

It’s a bit non-descriptive, so we’ll make some enhancements.

Make Changes to a Sparkline Graphic

By selecting a sparkline cell, a new Excel menu appears on the ribbon: Sparkline Tools. To modify the sparkline in our example, select Sparkline Tools, then above Show, select Markers and Negative Points.

Sparkline Show Settings

Above Group, click Axis then select Show Axis.

Toggle Sparkline Axis

Now the sparkline shows markers at each data point and all points below the axis line are red. That’s about as snappy as it gets. Below you can see the sparkline graphic beside the data range it represents, B263:B269.

Sparkline and Data

Now that I’ve set up a sparkline graphic with a dynamic range, each time I add data the sparkline graphic automatically updates.

Bonus Formulas

Bonus FormulasThe very top picture in this post has two cells, D1 and E1, that represents an evolution from a previous post. I’ve combined a text heading with a formula that’s inside a TEXT Function for formatting purposes. The information is self-explanatory. Here are the formulas:

  • D1 =”Last Date: ” &TEXT(OFFSET(A1,COUNTA(A:A)-1,0),”m/d”)
  • E1 =”7 Day Avg: ” &TEXT(AVERAGE(OFFSET(B1,COUNTA(B:B)-7,0,7)),”#.00″)

And of course, I’ll substitute my named range to make the second formula more readable.

  • E1 =”7 Day Avg: ” &TEXT(AVERAGE(LastSeven),”#.00″)

Put an OFFSET Formula Inside a Named Range

In the last post I used the OFFSET Function inside the AVERAGE Function to return the last 7 days of a range. Here I’ll create a Named Range using a formula with the OFFSET Function.

I use a worksheet to inform me of the Last 7 Day Average for Over-Under Calories data, as shown below.

OFFSET Function Examples

Cell E2 has the following formula.

=AVERAGE(OFFSET(B1,COUNTA(B:B)-7,0,7)

The AVERAGE Function is merely taking the range reference provided by the OFFSET function to return an average.

Create a Named Range with the OFFSET Function

I want to take everything inside the AVERAGE Function and put it inside a Named Range, which I’ll call LastSeven. The key to this formula is that range references have to be absolute and the worksheet name included.

=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)-7,0,7)

In Excel 2010 go to Formulas, Name Manager, and click New.

OFFSET Formula inside Named Range

Type in the Named Range LastSeven, enter the formula listed above, =OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)-7,0,7) then type a description if you like and click OK.

Now the previous formula can be revised to include the named range LastSeven,

=AVERAGE(LastSeven)

with the same result.

The OFFSET Function – Last 7 Data Points

In this example I’m going to use the OFFSET Function to return a range reference to the last 7 cells in a column. Of course, it will be inside the AVERAGE Function so I can see an average for the last week.

Sound complicated? Not really, it’s just an extension of something I did on a previous post for the OFFSET Function.

The example data is a spreadsheet I use to log my over-under calories to goal each day. This data is downloaded periodically and currently the data goes to row 269, as you can see below.

OFFSET Data Last 7

I use the OFFSET Function at the top of the worksheet to inform me of the Last 7 Day Average for the Over-Under Calories data, as shown below.

OFFSET Function Examples

Cell E2 has the following formula.

=AVERAGE(OFFSET(B1,COUNTA(B:B)-7,0,7)

The AVERAGE Function is merely taking the range reference provided by the OFFSET function to return an average. We’ll focus on the OFFSET Function and start with the arguments.

The OFFSET Function Arguments

The OFFSET Function has three required arguments — reference, rows, cols — and two optional arguments — height, width.

OFFSET(reference, rows, cols, [height], [width])

Reference is the anchor point, or ground zero. Everything starts here.

Rows is the number of rows from the anchor point. Or the row offset.  A positive number means down from the anchor point and a negative number means up from the anchor point.

Cols is the number of columns from the anchor point, or the column offset. A positive number means to the right of the anchor point and a negative number means to the left.

Height is the number of rows you want for the height of the range; a positive number, and if omitted is the same height as the reference.

Width is the number of columns you want for the width of the range; a positive number, and if omitted is the same width as the reference.

The OFFSET Function and the Last 7 Data Points

It’s useful knowing the 7-day average for over-under calories to goal. The OFFSET Function can give me this if I use it inside the AVERAGE function.

OFFSET Function Avg 7

For the OFFSET Function, the first argument reference is B1. That also means row 1 and column B when you break it down.

The second argument rows is COUNTA(B:B) -7, which computes to 269 – 7 = 262. The function COUNTA(B:B) is counting all non-blank cells in column B. Note: It’s important to remember that if any blank cells are in column B, the COUNTA Function won’t provide the right number to the rows argument.

Using 262 as the rows number, counting down from row 1 (the reference row) will plant you squarely in row 263, as the first row is not counted.

The third argument cols is zero. We’re not offsetting left or right.

The fourth argument height is 7. From the second argument we have row 263. Counting down 7 rows gives us the range reference B263:B269.

The fifth argument width, is omitted because we only want one column for the range.

OFFSET Function Dialog Box

The OFFSET function returns the reference B263:B269, and that leaves the rest of the formula as AVERAGE(B263:B269) for this example, which results in the answer 28.964.

The formula in this example will always give and average for the last 7 data points in column B, as long as there are no blank cells.

Become the OFFSET Function and Tell a Short Story

The OFFSET Function is a difficult one to learn. I’ve decided to become the OFFSET function and tell a simple story of how I give you a value for the last cell in a range by using an example.

The example data is a spreadsheet I use to log my over-under calories to goal each day. This data is downloaded periodically and currently the data goes down to row 269, as you can see below.

COUNTA Function

I use the OFFSET Function at the top of the worksheet to inform me of the Last Date Entered in the Date column, as shown below.

OFFSET Function Examples

Cell D2 has the following formula to pick up the Last Date Entered from column A.

=OFFSET(A1,COUNTA(A:A)-1,0)

I Am the OFFSET Function

The first thing you have to understand about me is that I will take the arguments you give and return a reference. And by that I mean a reference to a range. In this example I return a single cell reference, A269.

Three arguments are given in this example, which is good because they’re all required for me to give you an answer. There are two optional arguments that weren’t provided and I won’t worry about those now.

The first argument given is the cell reference A1. This is my reference point, or ground zero as I like to call it. Everything starts here. This range reference has two components, row 1 and column A, which are reference points for the following arguments.

The second argument is rows, which is a number I can use for the row offset. The formula COUNTA(A:A) – 1 is given for me to decipher.

The COUNTA function has the argument A:A, which is column A. This function will count all the non-blank cells in column A, which happens to be 269 for this example. Then by subtracting one, I get 268 rows for the second argument. Note: By using the COUNTA Function you assume there are no blank cells in the column A data. Important if you want the correct answer for the last data point.

Now that I have 268 for my row offset number in the second argument, I’ll just count down from the reference point, row 1. And the reference row is ground  zero in counting down. So row 2 is count 1, row 3 is count 2, row 4 is count 3, and so on until I reach 268. Here’s an example of how I’m counting with the row offset, starting with row 1.

Count Rows from Reference

You’ll notice the count is always one less than the number of rows, which explains the formula in the second argument, COUNTA(A:A) – 1.

The third argument is cols, which is a number I’ll use for the column offset. Since I’ve been given a zero in this example I’ll stay in column A, which is the reference column.

Finally, after taking the reference cell A1 and moving down 268 rows and over zero columns I will return the range reference A269, which gives you the last data point in column A.

Surprise! Cell D2 shows the number 40455, which is the date serial number.

OFFSET Date formatting

It’s up to you to format cell D2 with a date format. My job is done.

Enter a Constant Value in a Name

Once upon a time I was working with an unfamiliar Excel spreadsheet and came across a formula that used a Name, which I figured for a constant value and thought it hidden somewhere in the worksheet, yet couldn’t find it anywhere.

It never dawned on me that a value doesn’t have to reside in a worksheet cell.

Enter a Constant Value in a Name — Excel 2007, 2010

You can put a constant value into a Name, which Excel will hold internally.

Go to Formulas, and select Define Name (Excel 2010, 2007)

Named Constant Menu Selection

In the New Name dialog box:

  1. Enter a Name into the Name field (no spaces, please)
  2. Select a scope: Workbook or specific worksheet
  3. Click inside the Refers to text box, and replace any contents with a value of your choosing.
  4. Click OK

New Name Dialog Box with Constant Value

You can use the Comment box (shown above) to provide a comment, which will display (below) when you select the Name in a formula.

Comment for Named Constant

Enter a Constant Value in a Name — Excel 2003, 2008

The menu path is: Insert, Name, Define…

Named Constant Menu Selection 2003

Excel 2003

In the Define Name dialog box:

  1. Enter a Name into: Names in workbook
  2. Click inside the Refers to text box, and replace any contents with a value of your choosing.
  3. Click Add
Add Constant Value with Name Box Excel 2008

Excel 2008

You can now use the Name in a formula and possibly freak somebody out.

Check to See if a Name Has a Constant Value

If you’re stumped as to where a constant value may be hidden, in Excel 2007 or 2010 check the Name Manager. Select Formulas, Name Manager or use the keyboard shortcut Ctrl+F3.

If there’s a value in the Refers To column, then you won’t find it in a worksheet cell.

Name Manager

Excel 2003 and 2008 don’t have a Name Manager. In Excel 2003 using the keyboard shortcut Ctrl+F3 brings up the Define Name dialog box. Similarly, in Excel 2008 Cmd+fn+F3 brings up the Define Name dialog box.

For these and earlier versions of Excel, you can get a freeware Name Manager here.

How to Make It Very Hard to Find a Name with a Constant Value

  1. Create a Name with a constant value
  2. Use VBA to change the Visible property of that Name to FALSE

Watch the confusion.

Figure Those Pesky Fractions in Excel

You’ve probably seen a conversion chart that takes US fractions and converts them to a decimal equivalent.

However, when you need to know the decimal equivalent of 7/16 or 5/32, there’s no chart to be found. And if you’re working on a project that requires more than a few conversions, using a calculator or conversion app is time consuming and frustrating.

Excel does fractions easily. In fact, creating your own custom chart is a simple matter.

Enter a Fraction in Excel

To enter a fraction in Excel, enter the integer portion followed by a space, then the fraction. If the fraction is less than one (1) you have to enter a zero for the integer.

For example, if you enter 0 7/16 into a cell, it will show 7/16 and the formula bar will show the decimal fraction.

Create a Fraction List

To create the fraction list shown above, I used the following steps.

  1. Enter and format the headings (Fraction and Decimal)
  2. Enter 0 1/16 in cell A2 (remember the space between zero and the fraction)
  3. Enter 0 2/16 in cell A3 (fractions are reduced to their lowest common denominator)
  4. Select A2:A3 then grab the fill handle in the lower right corner of A3 and pull down to fill in the series.
  5. Select B2:B16. Enter an equals sign and select A2, then Ctrl + Enter to fill in the range.
  6. Use keyboard shortcut Ctrl+Shift+tilde (~) to get the General format.

Three digit fractions didn’t work for me until I changed the Fraction on the Number tab of the Format Cells dialog box to Up to three digits.

Fraction Format Dialog Box

I was also able to re-format my list using the As sixteenths Fraction format, which changed all the fractions to sixteenths.

However, when I created a list of thirty seconds (32’s) there was no standard format to choose from, so I looked at the format for sixteenths, by clicking on the  Custom category, then changed it to # ??/32, which changed all fractions to thirty-seconds.

Fraction Dialog Custom Box

Related Posts Plugin for WordPress, Blogger...