Formulas

All about formulas and functions

A Dynamic Dependent Drop Down List

A Dynamic Dependent Drop Down List in Excel

The 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 …

Read more

How to Convert GPS Coordinates in Excel

Navigating the world of GPS coordinates can be a complex task, especially when dealing with different formats. Excel, with its powerful data manipulation capabilities, can be an excellent tool for converting latitude and longitude coordinates from decimal to degrees, minutes, and seconds (DMS). In this article, we’ll explore how to make conversions of GPS coordinates in Excel, ensuring you have the precise data needed for your GPS adventures.

Read more

A Woopra Chat About Excel

I 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 …

Read more

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 …

Read more

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 …

Read more

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 …

Read more

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 …

Read more

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 …

Read more

Use the DATEDIF Function to Quickly Calculate Date Difference in Excel

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. How Many Years, Months and Days Has it Been? The following spreadsheet works well for birthdays or anniversaries. The DATEDIF …

Read more