Category Archives: Formulas

All about formulas and functions

International Short Date Formatting for the TEXT Function

I have an Excel workbook that is used in two different regions where the date format is entirely different, the US and the UK. At the top of a report worksheet I use a TEXT function to inform the user of the date range.

Here’s what a US user sees:

    From: 6/6/2011 to 6/10/2011

Here is the formula:

    =”From: ” & TEXT(MIN(ExtractData!A:A),”m/d/yyyy”) & ” to ” & TEXT(MAX(ExtractData!A:A),”m/d/yyyy”)

The Min and Max dates are in column A on a worksheet named ExtractData.

The problem is trying to automatically change the date format in the second … Read the rest

A Dynamic Dependent Drop Down List

A Dynamic Dependent Drop Down List in Excel

A Dynamic Dependent Drop Down ListThe other day I was reading a post over at the Contextures blog about Dynamic Dependent Excel Drop Downs and realized that using an Excel Table would provide an alternative method that is both simple and flexible. Tables are available in Excel versions 2007, 2010, and 2011.

In this post I’ll create a Table to hold the Category’s and Items, create three defined names using dynamic formulas, then use Data Validation to create two drop-down lists, the second being dependent upon the first.

Create at Reference Table

Here’s a Table with Category names in … Read the rest

Convert Coordinates for Your GPS with Excel

A few years ago I found myself just south of Paris, France one Sunday with a car and the inclination to do some sight-seeing. Since I was alone and don’t speak French my saving grace was Google Earth and a Tom Tom GPS navigation device.

Tom Tom allows GPS coordinates to be entered as a destination, but Google Earth lists those coordinates in a different format. In researching this article I found out that GPS coordinates can be presented in at least four different formats, making it difficult to understand the coordinates.

Google

Read the rest

A Woopra Chat About Excel

Woopra Desktop AppI use the Woopra desktop app to view live visitors to this WordPress blog. Visitors will see a Woopra window lurking about when I’m logged into my (free) Woopra account. One option is to initiate a chat session with me.

My first chat happened the other day. The chat notice startled me, but I quickly recovered and was able to interact with a visitor.

(Bold and color are my additions for clarity)

Visitor #13357:
08:53:19. HI
You:
08:53:28. Howdy

Visitor #13357:
08:54:22. I WANT TO CONVERT 01:05 = 65 MIN WHAT I DO
08:55:42. IN Read the rest

INDEX and MATCH Functions Together Again in Excel

The INDEX and MATCH functions are used together to perform lookups. The INDEX functions grabs a cell from an array, and the MATCH function contributes the row and/or column position.

INDEX and MATCH Example

Familiarity with the VLOOKUP function can cause you to restrict INDEX and MATCH to less than they can be. The example spreadsheet below bears this out.

The formula for Fiscal QTR takes the Month in cell E2 as input, finds that value in the Month column, then returns the Fiscal QTR in the same row. The first formula is:

=INDEX(A2:B13,MATCH(E2,A2:A13,0),2)

This … Read the rest

The INDEX Function in Excel

The INDEX and MATCH functions working together are more flexible than using the VLOOKUP function. However, if you’ve never used INDEX and MATCH to perform a lookup it can get confusing.

I’m going to cover the INDEX function here so you can learn it well and don’t have to think about it too much when we put it together with the MATCH function.

The INDEX Function Details

The INDEX function returns a cell value from a range, given a row and/or column position number. The syntax is:

INDEX(array, row_num, [column_num])

  • Array is required and is
Read the rest

The MATCH Function in Excel

The MATCH and INDEX functions working together are more flexible than using the VLOOKUP function. However, if you’ve never used MATCH and INDEX to perform a lookup it can be confusing at first.

I’m going to cover the MATCH function (Excel) here so you can learn it well and don’t have to think about it too much when we put it together with the INDEX function. First comes the dirty details and boring syntax, followed by a couple of examples.

The MATCH Function (Excel) Details

The MATCH function searches for an item in a range … Read the rest

Goal Seek in Excel

Formulas and Functions give you answers based upon input. If you know what a formula’s answer should be, but can’t derive the input value, Goal Seek is the tool for you.

I have a simple formula from my last post that takes one input, a Date in “m/d/yyyy” format, and returns the Day of the Year. This will tell me that Valentine’s Day — 14 Feb — is the 45th day of the year, or my birthday is day 311 this year.

Goal Seek Formula

The quickest way to find out what Date the 250th day … Read the rest

What Day of the Year is It?

Given the Date in this spreadsheet, a simple formula using the DATE Function will return the Day of Year. Go ahead and type a Date value in cell A2 to see how the formula works, the spreadsheet is embedded from my SkyDrive. (Date format is US, “m/d/yyyy”)

Click here if you can’t see the embedded worksheet.

The Day of Year Formula

The formula in cell B2 is:

= A2-DATE(YEAR(A2),1,0)

The DATE Function takes the Year from the Date you type in cell A2, and uses the zero (0) day of month 1 (January), … Read the rest

How Many Years, Months and Days Has it Been?

The DATEDIF Function comes in handy when you want to know how many years, months, and days there are between two dates. And even though it may be slightly flawed, it still beats the alternatives.

The following spreadsheet works well for birthdays or anniversaries. The DATEDIF Function is being used to generate the Years, Months, and Days.

DATEDIF Spreadsheet

The End Date column has the formula =TODAY().

The Years heading in cell D2 has the custom format ;;;”Years” which shows the text “Years” in the spreadsheet cell while hiding the underlying value “y” that you can … Read the rest

The TRIM Function in Excel and VBA

Excel’s TRIM Function is a great way to remove extra spaces before, after, and within text in a spreadsheet. However, if you think the identically named VBA function does the same thing you’d be sadly mistaken, as I was once upon a time.

Here’s a normal sentence.

VBA TRIM Normal Sentence

Here’s a sentence with extra spaces – two before, one after, and several within the sentence.

TRIM Function Test Sentence 1

Here’s a comparison of the two TRIM Functions.

TRIM Function Comparison 2

The Excel TRIM function reemoves all spaces from text except for single spaces between words.

The VBA TRIM Function … Read the rest

The VLOOKUP Function in Excel

VLOOKUP is an Excel Function that’s used to look up something. It takes four arguments. The first argument is the Something. The second and third arguments are the Look Up part. And the last argument is optional, but the most important, and is either TRUE or FALSE.

Technically the first argument is the lookup_value, but it’s just the Something we’re going to use in the Look Up part. The second and third arguments are the table that holds the look up data and column where the return value resides.

The VLOOKUP Function … Read the rest

Logical Operations in Excel

Every once in a while you get fixated on something and the only way to let it go is to share it with others. So …. while reviewing the logical IF function and nested IF statements, I came up with a chart or table, that sort of evolved out of curiosity.

It started by looking at two conditions, both having true or false possibilities, as shown in the table below, where A and B have four possible outcomes.

Logical Operations Four Outcomes

If you type the lowercase true or false into a cell they will change to uppercaseRead the rest

Excel ISNUMBER or IsNumeric Function

I don’t like it when Excel worksheet functions are different from their VBA counterparts. Makes for some aggravation.

For example, I have a user generated data range that’s supposed to be made up of either numbers or empty cells. However, Excel users sometimes bump their keyboards inadvertently (when you hit the space bar) and things like a space character can get entered into an otherwise empty cell, and go undetected to the naked eye.

VBA Programming

As part of a VBA macro I looped through each cell in this data range and performed an … Read the rest

Calculate Hours Between Two Dates and Times in Excel

Recently I was asked how to subtract time in Excel (time difference) or how to calculate the number of hours between two points in time on different days. Since this was in a reader comment, I gave a brief answer that requires a fuller account here.

Dates and Times are all part of the master plan in Excel. Once you “get” the fundamentals, the rest is just icing on the cake.

A Date value in Excel looks like this: 40519
A Time value in Excel looks like this: 0.58333

Cell formatting changes how you see … Read the rest