Convert measurements to scale of 1:6

PoohsMate

Board Regular
Joined
Aug 9, 2005
Messages
123
I've done several searches on this and can't find an answer.
I am looking to use Excel to help me scale down measurements. Some on original drawing I am using are in inches and some in centimetres which makes it more interesting.
What I am hoping to be able to do is to enter the original dimension in either inches or centimeters e.g. Inches in Cell A2 cms in B2 and then apply a scale of 1:6 in D2 with the conversion appearing in E2. There are applications on the web but I was hoping someone would help me understand and design my own as a learning excercise as I tend to use logical functions alot but not the mathematical ones.
Any help would be appreciated.Kind regards, Bernard
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows
Bernard

1. What are the final dimension units required?
2. Are the original dimensions all whole numbers, or parts thereof?
3. If it's the latter above, how are the parts thereof expressed, in fractions, or decimals
4. To what degree of accuracy are the scaled results required?
 

PoohsMate

Board Regular
Joined
Aug 9, 2005
Messages
123
Sykes,
I am rounding (to hopefully make things simple even if less accurate).
e.g.
Cell A2 = Input 132.75 inches actual (rounded to 133 inches).
Then I have used a series of calculations.
D2 (To convert to metres as I can't find centimeters) =CONVERT(A2,"in","m")
E2 (To get the centimeters) =SUM(D2*100)
F2 (To get the 1:6 conversion rate) =SUM(E2/6)
Then finally G2 (To convert to centimeters)=SUM(F2/2.8), as I think there are 2.8 centimeters to an Inch?
I am trying to get to the stage where I enter the actual length. Select the convertion rate then out pos the result in centimetres (or in extreme cases metres and centimeters if possible).
Hope this makes sense.
Bernard (PS Nice Pic - Buff Orpington?, we've got Peking Buffs due to small garden).
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows
Bernard
Don't understand the G2 stage - you're already in cm, and say you want the result in cm (and/or cm/m)!

A2 is the input cell
in B2 type the scaler eg "6" for 1:6 scale, "5" for 1:5 scale etc etc
in C2 put the formula:
=IF(CONVERT(A2/B2,"in","cm")<100,CONVERT(A2/B2,"in","cm"),CONVERT(A2/B2,"in","m"))

I'm only using whole numbers in A2, and I've formatted C2 for "number" to 2 decimal places.

Now, if you type up to 236 in A2 you'll get the answer in cm in C2, anything over 236 in A2 will automatically show in metres and decimals thereof, in C2.
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

...........Oh, PS she was a Warren....... my favourite, called "May". She died of old age a couple of years ago now....surprising how you get attached to them though isn't it?
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows
Bernard
Forgot to mention, doing it this way, you can type in A2 to whatever accuracy you wish, and be fairly certain of an accurate result, depending upon the number of DPs you set the cells to.
If I use the 132.75 inches in your example, I get a result of 56.20 cm.
 

PoohsMate

Board Regular
Joined
Aug 9, 2005
Messages
123

ADVERTISEMENT

Sykes,
Superb. Thank you.
Our two have gone broody so there is a lot of Fowl language (sorry!) in the coop at the moment - especially when I turf them out. I recommend them, lovely little eggs, great personalities and sound fantastic when contentedly ripping my lawn to shreds.
Kind regards, Bernard
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows
Bernard
Pleasure!
Yes, same here, mine love dandelion and dock leaves & fresh grass - unfortunately next door's particularly, and their cat's food!
Through the winter I end up buying greens from the supermarket for them. How sad is that!

All the best....
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows
Bernard
We never got to complete your original question, which was to allow for the original input to be in cm as well as ins.
I've come up with a nifty solution (even if I say so myself) to accomodate this if you're interested.
Problem is, I'm away for a week from tomorrow, so we'd have to implement it today..... are you available?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
How about:

=(CONVERT(A2,"in","cm")+B2)/6

then you could input in inches, centimeters or both:
small.xls
ABCD
1InCm
2132.7556.1975
3142.333333
47185.963333
Sheet1
 

Forum statistics

Threads
1,141,625
Messages
5,707,470
Members
421,510
Latest member
haroonstr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top