Formulas | Excel Semi-Pro | Page 2

Formulas

February 23, 2011 Formulas

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 […]

Get the full story …

INDEX and MATCH Functions Together Again in Excel

January 24, 2011 Formulas

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 […]

Get the full story …

The INDEX Function in Excel

January 21, 2011 Formulas

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 […]

Get the full story …

The MATCH Function in Excel

January 19, 2011 Formulas

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 here so you can learn it well and don’t have to think about it too […]

Get the full story …

Goal Seek in Excel

January 17, 2011 Features

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 […]

Get the full story …

What Day of the Year is It?

January 14, 2011 Formulas

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 […]

Get the full story …

How Many Years, Months and Days Has it Been?

January 12, 2011 Formulas

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, […]

Get the full story …

The TRIM Function in Excel and VBA

December 29, 2010 Formulas

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. Here’s a sentence with extra spaces – two […]

Get the full story …

The VLOOKUP Function in Excel

December 20, 2010 Formulas

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 […]

Get the full story …

Logical Operations in Excel

December 15, 2010 Beginner

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 […]

Get the full story …