Worksheet Calculations

[A.D.K.]

Hi everyone,

 

I'm rather new to worksheets.

 

Trying to make a cut list for materials, based on a custom record attached to lighting device symbols (I'm in spotlight, but I assume it doesn't matter that they're lighting devices, only that they're symbols with a custom record).

 

So I have the worksheet nicely populating with the lengths in inches, per construction/art, who work in inches.  I want this worksheet to then have a column beside the length (inches) column that is populated from the record, which would calculate the length in mm, which is what I need to give the guys building the fixtures.

 

So if I've got my vwx spreadsheet terminology correct, the database header cell for column A is linked to the length field in my custom record.  I want the database header cell in column B to be the value from column A, but converted to mm.  So in excel I'd make cell b1 "A1*25.4"

 

How do I do that here?  Is there a more sensible way of going about it than that formula? Does it complicate matters if my imperial measurements are fractions, but I want the metric measurements to be decimal?

 

Thanks!

 

EDIT: I should add, the lengths can't be calculated from the symbol itself, it's LED tape represented by a generic pixel.  The custom record in question is where I manually give it info based on what we're cramming it into.  So the stock "find length" functions won't work:(

 

Attached a screenshot.  14.25 inches is 362mm, so I've obviously got something wrong...

 

 

Edited October 4, 2020 by A.D.K.

[michaelk]

You're on the right track.

 

Because the Document Units are set to Feet & Inches, it's really reporting the value in decimal feet and then - because you have the cell set to report the value as a dimension - it's displaying it in a more useful way.  If you were to change the formatting of cell D2.1, D2.2, or D2.3 to be a general number (not a dimension) you would see that the value 1'2 1/4" is reported as 1.188.

 

In your case the formula =D2*12*25.4 would give you the measurement in millimeters.

 

Beware:  If you go to File>Document Settings>Units and change the document units to something else - say inches - then you would not have to multiply by 12.  But your entire document - dimensions included - would be set to report inches instead of feet/inches.

 

I know.   In the US we think it's dumb, too.

 

 

 

 

[A.D.K.]

@michaelk brilliant, thank you!  My tired brain couldn't see the multiply by 12.  You're awesome.

 

I'm in Canada, working for a US network with an English production designer.  Everyone works in feet and inches, but our LED tape stock comes in mm.  I'll get the hang of it;)  Thinking of it as exercise for my brain, so it's a good thing.

[michaelk]

I'm in the US, but I've done shows in Canada and Europe.  I'm always a little embarrassed to explain why we still used a measuring system where the main unit is a factor of 12 of the sub unit, but that unit is divided by powers of 2…. Any time you try to divide a measurement by 3 it turns into an 8th grade fractions problem whose answer is never any of the lines on your tape measure.

 

If you want a real challenge, try going the other way.  Start with a measurement in mm and convert it to feet and inches. 

 

[A.D.K.]

My google nest thingy exists mostly to do exactly that.

[Art V]

1 hour ago, A.D.K. said:

My google nest thingy exists mostly to do exactly that.

Maybe I should consider a Google Nest as well for this stuff. 🙂

 

It can get a bit messy with these kinds of conversions.

Some clients have hard and soft conversion tables and formulas for exactly this kind of stuff depending on use case for converting from metric to imperial and back to solve this issue without leaving room for misinterpretations of how to convert the units when.

Edited October 5, 2020 by Art V

[A.D.K.]

Could be worse.  At least I don't have to rely on this math to get anything to function once it's been sent to space.

 

Nest mini beside the computer to do maths for me.  Cheap.  Highly recommend.

Edited October 5, 2020 by A.D.K.