Formulas

Extracting Integers and Fractions in Microsoft Excel

February 14, 2013 Formulas

Sometimes you need to extract the integer portion of a number. Sometimes the fractional part. Sometimes both. Excel makes it easy to get the integer and somewhat harder to get the fraction. If you just want the answer, skip to the technical details. The Integer PartWhat could be easier than the INT function? I mean [...]

Get the full story …

Extract Time with the MOD Function in Excel

November 1, 2012 Formulas

I had a reader comment on my last post about how to extract time from a date-time number using the MOD function. Simple really. The syntax is MOD(number,divisor). The MOD function returns the remainder after number is divided by divisor. A simple example is MOD(5,2), which equals one (1). It works like this: five (5) [...]

Get the full story …

Break Even Calculation with an Unlocked iPhone and International Rates

April 23, 2012 Formulas

I just upgraded my wife to a new iPhone 4S and since she’s finished with her contract, AT&T will now unlock her old iPhone 4. Having an unlocked phone is advantageous when traveling overseas because you can pick up a Sim card with a phone plan and save some money. The question I want to [...]

Get the full story …

A Dynamic Dependent Drop Down List with a Horizontal Table Reference

April 9, 2012 Formulas

I received a comment asking if a dynamic dependent drop-down list in Excel could have a list where the “table headers were actually rows and not columns?” Since I’ve already detailed how this is done in the article mentioned above, I’ll keep this short. The screen shot below is what I’ll be referencing. At the [...]

Get the full story …

The VLOOKUP Function – Inside Out

March 25, 2012 Formulas

As part of Shark Week I’ve committed to write something for VLOOKUP week. (It’s what I get for using twitter.) So without further ado. I love the VLOOKUP Function in Excel. As the name implies, it’s a vertical lookup. Meaning the function will lookup data in columns. The VLOOKUP Function Arguments The VLOOKUP function has [...]

Get the full story …

How to Update a List or Range without OFFSET

March 22, 2012 Formulas

I avoid the use of Volatile Functions, especially OFFSET, which is commonly used to update a list or range. They can slow down the operation of your workbook. For very large workbooks with lots of data, it can be significant and irksome. Worksheet cells that use Data Validation for a drop-down list can simplify the [...]

Get the full story …

Fill Down a Formula with VBA

September 6, 2011 Formulas

I commented on a post that brought to light, the fact that, using the cell fill-handle to “shoot” a formula down a column doesn’t always work when the adjacent column(s) have blank cells. So I decided to share some Excel VBA code that’s used to copy a formula down to the bottom of a column [...]

Get the full story …

International Short Date Formatting for the TEXT Function

July 7, 2011 Advanced

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

Get the full story …

A Dynamic Dependent Drop Down List in Excel

May 25, 2011 Formulas

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

Get the full story …

Convert Coordinates for Your GPS with Excel

March 1, 2011 Beginner

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

Related Posts Plugin for WordPress, Blogger...
Get the full story …

← Previous Entries