Calculating Picture Frame Spacing in Excel (Inches)
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:
- How many frames I am going to hang up in the space
- The length in inches of the wall space I want to spread the frames out in
- 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”.
Download The Excel Example File
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
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.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
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.