Excel for Artists: A Size Calculator - by Ingeborg Hawighorst, Excel MVP
Published Aug 19 2019 01:31 PM 11.9K Views

Quick, without thinking too much: is A3 paper larger or smaller than 18 x 24 inch? And how big is 30 x 40 cm compared to 14 x 20 inch?


"Who cares," you may think. Well, lots of artists do. Depending on where you live in the world, the size of a painting is expressed in different units of measure – in the US, in inches; whereas in Europe, in centimeters (cm) using the metric system. I live in a metric world, and whenever I see a size given in inches, I must think hard about what that means in cm.


That's why I like Excel so much. It helps me do all these pesky math things, and I can simply look at the results.


Although I work with Excel a lot and love the left-side-of-the-brain problem solving and technical details, I also do things that are dominated by the right-side-of-the brain: drawing and painting. I paint portraits, landscapes, and still life, mainly working with pastels. I've even painted a few Excel MVPs in the last few years.

 

Ingeborg_PaintingBear.JPG

 

 

When buying art supplies online, there are so many different paper sizes that things can quickly get confusing. Therefore, I often turn to Excel to do the conversion of the paper sizes from inch to cm, so I know what I'm buying. Also, when I look at art work on the internet, and a painting size is given as 45 x 30 in, my metric-trained brain needs a while to realize how big that painting really is, so I turn to Excel again to help me work it out faster. I've even created a little conversion calculator that helps compare two different paper sizes using an Excel chart, which I'd like to share with you today.

 

Download the workbook Paper Size Comparison below.

 

How it works – for artists

 

When you open the Paper Size Comparison workbook, you can see two input areas, one for each size. The white cells for the paper sizes have drop-downs where you can select from many popular paper sizes in imperial, and even a few sizes commonly used in China.  Simply click the white cell and use the drop-down to select a size in each cell.

 

The chart will then immediately draw the outline of the selected sizes and provide a visual indicator of how the two compare.

 

Ingeborg_SizeCalculator_01.jpg

 

If the size you want is not listed in the drop-down, go to the top of the drop-down and select "custom inch" or "custom cm". This will enable two cells below the drop-down where you can manually enter the dimensions for width and height.

 

Ingeborg_SizeCalculator_02.jpg

 

The axes of the chart are not showing a particular scale but will dynamically change based on the selected numbers. That means that the sizes are compared relative to one another. You can set the measurement of the chart axes to either cm or inch with the drop-down below the paper sizes.

 

Bonus feature 1: There is a switch for each paper size that you can use to turn the paper sideways in the chart. That way you don't need to care about the order of width and height in the input cells.

 

Bonus feature 2: Beneath each paper size you can find the measure of the outline of the size in both cm and inches. This is useful if you do your own framing and want to know the total length required.

 

And finally, if you find that the colors of the workbook don't agree with you, you can switch to a different theme.  The sheet is protected against accidental changes, so first click the "Review" ribbon tab and there click "Unprotect Sheet." Now you can click the "Page Layout" ribbon tab and select a different theme with "Themes" drop-down.  When you have found your preferred theme, protect the sheet again. Here is a quick video that shows the steps. Don't forget to save your changes!!

 

ChangeTheme.gif

 

 

Why it works – for geeks

 

The inputs from the sizes sheet are processed on the hidden Calc sheet. You can download a version of the workbook with all sheets visible below, or you can simply right click the "Sizes" sheet tab, then "Unhide" and select the "Calc" sheet.

 

The Calc sheet contains the "Size Table" that informs the data validation list for the paper sizes drop-downs. The list of paper sizes in the drop-downs is calculated with the dynamic range name "PaperSizes" that uses this formula:

 

=Calc!$A$4:INDEX(Calc!$A:$A,MATCH("zzzzz",Calc!$A:$A,1))

 

When more paper sizes are added to the table, the name formula will include the new rows automatically.

 

The unit of measure for the paper sizes is listed in the last column, and the paper size will be converted dynamically with formulas that calculate the chart data.

 

Calculating the chart data

The data entered into the Sizes sheet are pulled into the Calc sheet with direct references in the section labelled "Data entry cells from the Sizes sheet". If custom sizes have been entered, these will be pulled into the section "Measurements for custom sizes".

 

The chart data series need five data points for each series. These will be plotted in an XY Scatter chart. The first data point is the lower left-hand corner of the paper, then the line goes clockwise around the edge of the paper. The zeros in the coordinates have been entered manually, but the other numbers get calculated with a formula.

 

calculations.jpg

 

Here is the formula for the X coordinate of the top right corner, i.e. cell F23:

 

 

 

 

=IF(G3="custom inch",
   G12,
   IF(G3="custom cm",
      G15,
      VLOOKUP($G$3,SizeTable,2,0)))
*INDEX(N22:O23,
    MATCH(VLOOKUP(G3,SizeTable,4,0),M22:M23,0),
    MATCH(G7,N21:O21,0))

 

 

 

 

Let's pick this apart. If cell G3 has the words "custom inch," then the number from cell G12 is returned. If G3 has the words "custom cm," then the number from G15 is returned. If G3 does not contain a custom value, it will be looked up in the Size table, and the value from the second column (cm wide) will be returned.

 

If the paper size comes from the Size table, it may be necessary to convert the values from cm to inch or vice versa. That's what the second part of the formula does. It looks in the "Conversion table," finds the row where the unit of measure equals the one looked up from the fourth column of the SizeTable, and finds the column that is equal to the chart axis unit of measure provided in cell G7.

 

Building the chart

The chart source data is using named ranges instead of direct cell references. If "turn sideways" option for the blue paper is set to "yes," the formula returns G21:G25 for the X data, otherwise it returns F21:F25.  The blue Y series is then simply offset by one column.  So, when "turn sideways" is "no," the range names refer to column F for the X values and to column G for the Y values. When "turn sideways" is set to "yes," the range name references shift one column to the right and refer to G and H. Column H is simply a copy of the data in column F, so that the paper appears to be turned.

Here is the list of named ranges for the chart:

 

BlueTurn

=Sizes!$E$14

BlueX

=IF(BlueTurn="yes",Calc!$G$21:$G$25,Calc!$F$21:$F$25)

BlueY

=OFFSET(BlueX,0,1)

OrangeTurn

=Sizes!$E$24

OrangeX

=IF(OrangeTurn="yes",Calc!$J$21:$J$25,Calc!$I$21:$I$25)

OrangeY

=OFFSET(OrangeX,0,1)

 

The chart uses the range names for the blue and the orange series X and Y values. Each size series plots the outline of the paper starting from the crossing of the axes, going clockwise and returning to the starting point.

 

A third series plots just one data point: the highest number in all the values on both X and Y axis. Although this data point is formatted to be invisible, it ensures that the automatic axis scale is always the same for both the horizontal and vertical axis.

 

ChartDataSource.jpg

The chart title uses a formula in the Calc sheet, so it dynamically changes to the selected settings and the axis title comes from the selection in cell E30 next to the chart.

 

Since all the calculations are done with formulas, this calculator also works in Excel online and phone versions of Excel.

 

PS: You can see more of my art on my Facebook page https://www.facebook.com/iBorgNZArt/

 

4 Comments
Version history
Last update:
‎Apr 24 2020 02:06 PM
Updated by: