Results 1 to 9 of 9

Thread: google sheets fractions

  1. #1
    Join Date
    Dec 2013
    Location
    I live in NH
    Posts
    104

    google sheets fractions

    Hello all, I am trying to use google sheets to cut all of my doors and drawer faces in my shop.

    For anyone that does not know its a free version of excel that can take excel files and convert/open them in google sheets. After that has been done you can use sheets similar to excel.

    My question is this ,

    Can you sort columns by the size of the number? (seems a dumb question but i cant seem to find a command that actually does the sorting)


    If you can sort by the size of the number can you still do this when the numbers are in fractional format?


    What I am able to get is close but not right, the only command so far that i can get to do any sorting is the A-Z command. but it only uses the first number on the left. in other words it lists 2 right after 19 .

    Does anyone use the export to excel command and then build doors from a spreadsheet? Please let me know how you deal with this issue if anyone knows .

    Thanks again my fellow woodworkers!

  2. #2
    Join Date
    Feb 2009
    Location
    Bucks County, PA
    Posts
    971
    The problem is probably that you entered the values as straight-out mixed fractions (e.g. 5 5/8), but when you do that Google Sheets thinks that's a string and not a number.

    Instead, you have to enter that as a decimal number (in this example, 5.625). At the same time, be sure to set the column's formatting to Format -> Number -> More Formats -> Custom Number Format -> # ??/?? The sheet will then display the values as mixed fractions, but if you click on any number you'll see the decimal equivalent. Sorting then works properly.
    And there was trouble, taking place...

  3. #3
    Join Date
    Dec 2013
    Location
    I live in NH
    Posts
    104
    I am trying to do this without entering any numbers . I find every time i do this many i mess a few of them up . I am building large kitchens so i have over 300 parts on each of these lists. (and thats just door/drawer parts)
    Can that format command you mention work if the 5 5/8 number is there and then you do the formatting you explained?
    I am really new to spreadsheets in general so i am learning as i go, and there seems a lot to learn on google sheets!
    Thanks a ton for trying to help me out with this !

  4. #4
    Join Date
    Jul 2017
    Location
    Prairie Village, KS
    Posts
    397
    Quote Originally Posted by andy photenas View Post
    I am trying to do this without entering any numbers . I find every time i do this many i mess a few of them up . I am building large kitchens so i have over 300 parts on each of these lists. (and thats just door/drawer parts)
    Can that format command you mention work if the 5 5/8 number is there and then you do the formatting you explained?
    I am really new to spreadsheets in general so i am learning as i go, and there seems a lot to learn on google sheets!
    Thanks a ton for trying to help me out with this !
    You would have to write a custom formula to convert. Or you could break the column into two columns (whole number and fraction) and then combine to make it a decimal number. My recommendation is the latter but going forward, always enter numbers as decimal numbers then you can change the way they display.

  5. #5
    Join Date
    Feb 2009
    Location
    Bucks County, PA
    Posts
    971
    Quote Originally Posted by andy photenas View Post
    I am trying to do this without entering any numbers . I find every time i do this many i mess a few of them up . I am building large kitchens so i have over 300 parts on each of these lists. (and thats just door/drawer parts)
    Can that format command you mention work if the 5 5/8 number is there and then you do the formatting you explained?
    I am really new to spreadsheets in general so i am learning as i go, and there seems a lot to learn on google sheets!
    Thanks a ton for trying to help me out with this !
    As Tim also pointed out, it's best to enter the numbers as decimals (5.625 for that example) and then just use the formatting menu to change how it's displayed.
    And there was trouble, taking place...

  6. #6
    Join Date
    May 2015
    Location
    Ingleside, IL
    Posts
    1,417
    Quote Originally Posted by andy photenas View Post
    I am trying to do this without entering any numbers . I find every time i do this many i mess a few of them up . I am building large kitchens so i have over 300 parts on each of these lists. (and thats just door/drawer parts)
    Can that format command you mention work if the 5 5/8 number is there and then you do the formatting you explained?
    I am really new to spreadsheets in general so i am learning as i go, and there seems a lot to learn on google sheets!
    Thanks a ton for trying to help me out with this !

    Just curious what the end game is here. Are you trying to make a cut list for someone in the shop? Are you using a drawing / design program to layout the kitchens and cabinets? If so, you should be able to export to excel or sheets or pdf , etc. You said you are trying to do it without entering numbers which to me means you have the info somewhere already and are transcribing to sheets. Like I said, just curious.
    Stand for something, or you'll fall for anything.

  7. #7
    Join Date
    Nov 2006
    Location
    Vancouver, BC
    Posts
    858
    Quote Originally Posted by andy photenas View Post
    I am trying to do this without entering any numbers . I find every time i do this many i mess a few of them up . I am building large kitchens so i have over 300 parts on each of these lists. (and thats just door/drawer parts)
    Can that format command you mention work if the 5 5/8 number is there and then you do the formatting you explained?
    I am really new to spreadsheets in general so i am learning as i go, and there seems a lot to learn on google sheets!
    Thanks a ton for trying to help me out with this !
    Where are you getting the data for your spreadsheet if you're not entering it? Are you using a CAD program that is generating a cutlist?

    If you want to enter dimensions in fractional form you probably need to use excel. With Google sheets you need to enter the numbers in decimal form and then you can format them as fractions but that is more of a hassle if you want to work in fractions. With excel you can enter a number as a fraction and it will recognize the fraction as a number. It can then display it in either decimal or fractional form.

  8. #8
    Join Date
    Feb 2013
    Location
    Tippecanoe County, IN
    Posts
    836
    As Tim suggested, dedicate an empty column next to your dimension list for a sorting key. Enter this formula in that column:

    =left(A1,find(" ",A1)-1)+mid(A1,find(" ",A1)+1,find("/",A1)-find(" ",A1)-1)/right(A1,len(A1)-find("/",A1))

    Where A1 is the cell you want to sort numerically.

    If you have a good understanding of how cell addresses change when you copy a cell you can simply copy the above and paste it into a strategically chosen cell and then just copy and paste from there to wherever you want. Saves trying to type it in manually.
    Beranek's Law:

    It has been remarked that if one selects his own components, builds his own enclosure, and is convinced he has made a wise choice of design, then his own loudspeaker sounds better to him than does anyone else's loudspeaker. In this case, the frequency response of the loudspeaker seems to play only a minor part in forming a person's opinion.
    L.L. Beranek, Acoustics (McGraw-Hill, New York, 1954), p.208.

  9. #9
    Join Date
    Dec 2013
    Location
    I live in NH
    Posts
    104
    Sorry if i asked that in a confusing way, i thought it was common knowledge that if you design in eCAb and make your doors "build in house" that you can then bring up the cutlist when in floor plan view. From here at the top you can choose export excel file. I then upload this to google sheets and in doing so sheets changes the file to a google sheets file. after this you can use sheets almost exactly the same as excel and its free.
    This is how i am not entering any numbers. the file comes up as fractional not decimal.

    I was able to use all the ideas you guys had to figure it out !!!

    The best way to do this is by making a second column as suggested but no formula is needed. all you need to do is use the split command under data tab and choose to split by " space" this breaks the fraction off of the whole numbers but keeps it in the column to the right . This can be done in one move if you highlight the column.

    And to answer the question about how i use this list. I use it to build the doors in my shop but i send the file to an eCab friendly CnC shop near me. While i build the doors he makes the bodies.

    Thanks again good woodworks !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •