×

Calculating Picture Frame Spacing in Excel (Inches)

By Chris Newman •  Updated: 04/28/21 •  5 min read

The Chore of Hanging Pictures

Often times when I find myself needing to hang multiple picture frames next to each other, I turn to Excel to help me figure out the spacing needed. After doing this from scratch enough times, I decided to make a little template to where I could plug in a few inputs and quickly get the measurements I needed to start hammering nails into the wall.

My template (pictured below) requires 3 inputs:

1. How many frames I am going to hang up in the space
2. The length in inches of the wall space I want to spread the frames out in
3. How wide (in inches) a single picture frame is (this model assumes all frames are the same size)

The final output (last 3 rows above) provides you with the schematics to pinpoint where the center of each one of your frames needs to be. As you can see by the diagram below, there are three different measurements provided to get you from start to finish.

A Few Caveats

In order to make the output line up with the tape measure, I had to incorporate rounding to the nearest 1/16th inch. In some cases, the rounding will cause slight disconnects, but hopefully will be so small that it doesn’t ruin anything visually.

With me being a stubborn American, this template is also set up to output in inches. Hopefully, by reading through this article and downloading the sample file, you’ll be able to make conversions to centimeters or whatever unit of measure you require.

Formula to Convert Inches to Ruler Measurement Format

Like all good gurus, I was able to find the basis of the formula needed for this exercise via a Google search. Many thanks goes out to the MVPS.org site which hosted this article and gave me a great starting point so I didn’t have to think too much to get my desired result.

If your value is in cell A1:

``=INT(A1) & IF(INT(A1)<>A1," " & TEXT(ROUND((A1-INT(A1))*16,0)/16,"##/##"),"")& CHAR(34)``

This formula would convert a value of 108.5 to 108 1/2”. There are a number of parts to this formula, so let’s spend some time breaking it down a bit.

[Segment 1] =INT(A1)

We’ll start off this function by obtaining the whole inches portion of our numerical value. We can utilize the INT() function to isolate the whole number portion of the value in cell A1.

[Segment 2] IF(INT(A1)<>A1

We need to include an IF statement to determine if the formula needs to handle a partial inch value (any decimals).

[Segment 3] ROUND((A1-INT(A1))*16,0)/16

In this segment, we are essentially converting the decimal portion of the number into a rounded decimal value that falls with a denominator of 16. Since the lowest split on a tape measure is in 16ths, that is what we want to round to.

[Segment 4] TEXT(……..,”##/##”)

At the end of the TEXT() function, we are providing a custom number format rule to turn the result into a fractional format.

[Segment 5] CHAR(34)

This function adds the double quotation symbol to the end of the text string we are creating. The number 34 is the Char code for double-quotes.

A Formula to Display Feet and Inches

I wanted to go a step further and include an alternative formula that displays the end result with Feet and inches summarized. I won’t rehash all the prior pieces of the formula we walked through in the prior section, just know that all you really have to account for is dividing parts of the formula by 12 to remove the effects of there being complete feet (a full 12 inches) in the input value.

You’ll see that the MOD() Function is utilized a couple of times in the below formula. If you are not familiar with MOD, it essentially provides you with the remainder value of the two numbers you feed it. So if you were to write =MOD(17,7), it would result in 3 since that would be the remainder after 17 was divided by 7.

If your value is in cell A1:

``=INT(A1/12)&"' | " & TEXT(INT(MOD(A1,12)),"#") & IF(INT(A1)<>A1, TEXT(ROUND((A1-INT(A1/12)*12-INT(MOD(A1,12)))*16,0)/16," ##/##"),"")& CHAR(34)``

This formula would convert a value of 108.5 to 9’ | 1/2”.

I Hope This Helped!

Hopefully, I was able to explain how you can use Excel to calculate where you need to hammer in your nails. I know it’s been handy for me! If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Keep Learning

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.

[FREE Training] 10 Amazing Excel Efficiency Tricks. Why Don't People Know These?!

X