Sizing cosplay props and armour in Excel

On October 22, 2014, in Uncategorized, by Toast

As I know not everyone is BFFs with excel like I am, I’m going to write a full tutorial (we’ll see how long this gets anyhow) on how I build these sizing spreadsheets using something I’m in the process of starting to build.

Quick Excel Note:

Throughout this tutorial I will refer to cells and what to place in them. I don’t want to assume anything is basic knowledge. So please see the image below:

In this image the blue cell is C3 and the green cell is A6.

Also yes, you can use google docs to do all this.

Using excel to size props/armour:

One of my current projects is Toan from Dark Cloud, with the Chronicle sword. So as I write this I’m doing the actual work to size out this blade. So here is Toan:

1

As shown in both my armour and weapon tutorial I like to add lines in photoshop to aid in getting more exact measurements. Any program that enables you to have layers and draw straight lines with some kind of tool will work for this. So now Toan looks like this:

2

Note that the blue line is just to make is easier to show where the end of the blade is when a line is drawn from the handle’s tip. I find this a handy trick when the highest/lowest point don’t line up.

At this point I note down the current zoom on my picture, this is so if we were have to revisit this image for more measurements we can still use the same scaling. My current zoom is 66.67%

Now I’ve written this out before, but we’ll do it again!

So I now pick up my ruler and hold it to my screen and measure out the two lines, green and red, in cm. I suggest using cm over inches just due to the ability to be more precise, also, do yourself a favour and buy a clear ruler for this, just do it.

So the green line (Toan’s height) is 22cm and the red line (Sword’s total length) is 18.8cm.

Now we set up an excel sheet with the following columns:

3

Area is the title/description of what I have measured, screen is the measurement that I took on the screen (aka what we just did above), IRL is the cm that the item will be in real life and Ft/in RL is just a conversion over to feet and inches because although I’m Canadian, my brain does still run on ft/in for larger items.

Now enter two items into the “area” like so:

4

So we know Toan’s height on screen (22cm) and my height (168 cm). So enter 22 in to cell B2, and 168 into cell C2.

Now I put a little box somewhere else, generally F2, to hold my conversion ratio. This is created by entering the following into F2:

=C2/B2

Now F2 contains the number that is takes to convert from your screen measurements to your real life measurements.

So now we enter 18.8 into cell B3 (Total Weapon Length). To figure out the IRL length of this we need to multiply it by the conversion ratio.

To make this easier on items going forward we are going to enter this into cell C3:

=B3*$F$2

The $F$2 means that excel will always reference this cell when we drag this formula. So now grab the bottom of cell C3 and drag down a ways. While dragging it down the B3 part will change to B4, B5, ect, however the F2 will continue to be referenced.

So we now have a sheet like so:

5

To fill in the D column (Ft/in RL) we are going to enter the following into cell D2:

=CONCATENATE(ROUNDDOWN(CONVERT(C2,”cm”,”in”)/12,0),”‘”,ROUND(MOD(CONVERT(C2,”cm”,”in”),12),0),””””)

Why is this so long? Because it’s the only way to get it to display a format that is x’x”.

If you care.  This is what this formula is doing in words.  Concatenate is something that allows you to string sentences together, there are other ways to do this in excel but I enjoy the concatenate method.  The CONVERT formula is changing the measurement from cm to in.  The round down is… well rounding down.  MOD is then something that calculates the remainder on a division.  So you are telling MOD that you have divided the inch result of the cm measurement in C2 by 12, and it is giving you the remainder.  I then round this… because it looks nicer.

Now like we did to cell C3, drag this down to fill the D column.

And this is our end result:

6

From here you can play around with the scale (I’m honestly not sure I want a 143 cm blade). So reduce this just overwrite the scale until it produces a total number that you are happy with. Making this blade around 120cm or 4 feet is likely sufficient enough for scale.  There is nothing wrong with doing an 80 or 90% scale when props feel stupidly large. Then I continue along measuring things to keep everything to scale. How big is the handle? The sides? The width? What is the measurement between X area and Y area. You can understand how/why these sheets get very long.

From here I sketch a 2D version of the item, generally on a firmer paper type (like Bristol) then use this to template out my weapon.

As always – Thanks for reading and come visit me/send me any questions at:

https://www.facebook.com/ToastCosplay

Tagged with:
 

One Response to Sizing cosplay props and armour in Excel

  1. Jeannine says:

    This is be very helpful for me, I too will be making a Toan cosplay thanks a lot :)

Leave a Reply

Your email address will not be published. Required fields are marked *