Feet & Inches formatting -- is there a better way?

Barry wrote:

Here's a slightly different approach.

ok, nice.. i can't really get into it right now but will have a more thorough look at your formulas later tonight.. i did notice QUOTIENT in the formula list and thought it would be useful in this case but didn't see a way to tie it in with the approach i took.

i think one of the reasons my formulas are looking so scraggly is because i was trying to cram all of the stuff into one cell -- which in all honesty, would be the best approach for me because i'll be using this in most of my spreadsheets and it'd be nice to just copy/paste one cell containing the formula instead of setting up multiple columns each time.. but then again, i don't know the 'best' approach for making clean calculations and my cram it all into one idea could very well be flawed.

that sort of brings up another question i have about cell formats which i couldn't find a solution for (but i also couldn't find anything that says it's not possible).. say i have 4 - 8 0.375 in a cell (that's three separate numbers -- the 4, the 8, and the 0.375).. as i understand, numbers is seeing this as text (or a string?) instead of actual numbers so if i format the cell as fractions then nothing happens.. if i put the 0.375 in that formatted cell by itself then it shows as 3/8..

so, is there a function (or something)that i can add to the last number (0.375) which tells numbers to apply the cell format to?

another (farfetched) example might be with the currency format.. say i want one cell that says either "i owe them 5" or "they owe me 5"... in this case, i'd want numbers to put the 5 in dollars so it reads "i owe them $5.00"

is this (easily) possible to do in one cell?

--------------------

re: the notation system..

i've tried doing some things like that with my own crew and for the most part, it doesn't really work out.. i tried implementing a standard for calling out sixteenths which was to only say the first # of a sixteenth.. so 24 13/16 is said "twenty-four thirteen" or "twenty-four five" for 24 5/16..

i guess it was ok but it ultimately failed due to linguistic issues.. there's no flavor (or whatever you want to call it).. i've had the same core crew for around 10 years now and the calling of numbers has developed to something most people can't understand..

24 1/6 = 24 heavy

24 1/8 = 24 eightball -or- eight

24 1/4 = 24 quart

24 3/8 = 24 tree eight

24 7/16 = 24 half-light

24 9/16 = 24 half-heavy

24 3/4 = 24 tree quart

24 7/8 = well that's just 24 seven eight

the weird ones - or the ones prone to bad cuts - are when the amt of inches ends in zero.. you might call out 30 7/8 and and get a board that's 37 1/8 so we try to add an 'and' in there in these cases.. 30 and 7/8

but anyway, back this particular discussion.. ultimately, these numbers that i'm calculating are mainly for cnc operators spread throughout the country.. (i cut a ton of different radii on-site and drawing a 75' radius inside a building that's 60' long isn't too cool 😀).. i'm now having cnc people take 4x8 sheets of masonite and just cut a bunch of few_inch_wide computer controlled radii to my specs so when i show up on site, i have all the radii and we just trace and cut..

sending them a format such as the recommendation by the writer is just going to lead to some confusion and probably a few errors that i'd rather just avoid.

realistically, i wish the u.s. would just implement the metric system once and for all (well, i wish they did it 20 years ago before i started building and ingraining the imperial system).. it makes too much sense compared to what we're using today but for whatever reason, imperial remains standard.

i'm wondering what i should lobby for between u.s adopting metric or apple incorporating a dirtbag carpenter mode into numbers 😀 (ie - feet/inches/fractions of inch along with degree instead of radians for angles)

i guess i got a little long winded there so sorry about that.. i'll respond back once i try out some of the recommended forumlas.

thanks

hmm,

just found an error in there with 395.99 and the like.. lengths between ~31/32" & 1" are being rounded to 1"... when this occurs on an 11" (say 4' 11 31/32) then the result is showing up as 4' - 12 instead of 5' - 0.

looks like i'm going to have to add even more stuff to those formulas 😀

(unless of course, someone knows some simpler manner to do this)

KOENIG Yvan wrote:

(1) it's time for carpenters to adopt the metric system

(2) radian is the official mathematical unit for angles so it will remain the one used in spreadsheets.

1. well, it's not the carpenters that need to pave the way.. we're more/less the end users of building materials.. it's the manufacturers that need to adopt (and it probably goes higher up than them as well.. into the govt.)..

if i adopt the metric system then all the materials need to be able to be purchased accordingly or else i'm left with buying something 1219.2mm long instead of 1220 or a sheet 6.4mm thick instead of 6.. etc... i'd also be left with resizing/recutting every single dimension in order to fit with other trade's standard imperial dimensions..

point being, there's nothing but a giant headache for the tradesmen to adopt metric when all of their supplies and/or building codes etc are produced/written in imperial.

2. the radians thing i'm not so convinced about.. sure, it makes a lot of formulas simpler so i see it's benefit for mathematicians but communicating in radians is less than ideal.. degrees are much simpler to talk about and in many ways, make more sense when it comes to practical applications.. in spreadsheets, i highly doubt it would take much to give the user a choice.

KOENIG Yvan wrote:

(1) in France, everybody use the metric system, even those restoring the castle of Versailles !

(2) I use computers since more than 20 years and I never saw a spreadsheet offering the ability to work with degrees for trigonometric functions.

You may use degrees to describe your angles but when you need to use trigonometric function, you just need to convert the degrees into radians thru this kind of formula :

=COS(RADIANS(degree-angle)).

1] yeah, don't get me wrong.. i want the metric system in the u.s..

we're slowly (very slowly) adopting it over here but it seems more prominent in some industries than others.. if i had to guess, i'd say the construction industry is at the back of the pack as we rarely have anything offered in metric sizes..

for a while, a label on a 2x4 also had it's metric equivalent on it but that has gone away.. really, i think it's up to the government to mandate a change instead of their half-arsed setup that have now.. really, if enacted today, i don't see any reason why the u.s. couldn't be all metric by 2022.. most of the land surveys have already be redone in metric.. the military uses metric.. even our feet and inches are officially calibrated using the metric system..

it's simply a matter of pushing public education and placing a few requirements on manufacturers but for whatever reason, it's just not happening.. i think we're on the 60 year plan instead of the 10 year plan but unfortunately, i doubt i'll still be designing then..

2] i know about the conversion for degrees.. maybe i'm still just a little salty because i spent two hours scratching my head when i first tried trig functions in numbers 😀..

my only other spreadsheet experience besides simple quantity/price per/total type stuff has been with sketchup(pro)'s dynamic components.. it's basically a spreadsheet for drawing and it's all in degrees as well as selectable units of measure.. but that's a specialized app and i can understand the majority of people not wanting or caring about this stuff in a more general spreadsheet app.

[edit] that said, i saw some things for excel that allow you to customize your user experience.. theres a plugin(?) (.xla format i think) that allows the user to enter and receive data in feet/inches while it does all the conversions in the background.. i'm hoping iwork (11?) will offer something similar.

Barry wrote:

During the working out stage, I generally break a long formula into separate parts and put each part into a separate cell, then use concatenation to tie the results together, mostly because it makes the separate sections easier to read.

ok, i can see that as an advantage for keeping things simpler when working out the kinks.. performance wise, is there any disadvantages of having a bunch of functions in one cell as opposed to separating the duties to multiple cells? (not that i've noticed any slow downs as my tables are generally small.. just wondering in theory)

fwiw, i still haven't been able to try your approach yet but i do think it's a better route than what i was doing.

I think you've already answered this question,

ha.. yeah, i was just typing my observations so far.. but i was just wondering if i could do something to the decimal portion of the string to signify that it's to be formatted accordingly.. (for instance, i tried putting VALUE around the decimal part but obviously it didn't work.. no biggie, i'll just keep a separate column for the fractions)

But that discussion is beyond the scope of this forum.

right, it is.. i still wish the u.s. would just go full on into adopting the metric system though 😉

thanks again for all your help

Barry wrote:

... by moving all the 'working columns' onto an auxiliary table,...

lol.. that little snippet switched up my whole game plan for the better..

i'm in the process of making four different tables (one of which is the other one you(barry) helped me figure out) which need feet/inches/fractions of an inch

for whatever reason, i was making four separate files... one for each calculator.

it simply never occurred to me before to just make one .numbers file with multiple sheets 😊 which is going to make some things much easier (only one file on the cloud or laptop, easier to keep track of, etc)

so i think i can put the feet/inches converter on the fifth sheet and feed all the other tables to and fro.. in which case, i won't mind the simpler multi column approach instead of concatenating everything into one cell.

i think i'm good to go from this point on as i have all the test calculators working as i hoped and now my organization of them is going to be much better to use.. so thanks fellas for holding my hand through this 😀..

and i have two weeks to spare in getting these things prepared!

jeff