Looked over the possibilities for help and coming up empty.

I have written a spreadsheet which does some routine calcs for me. The problem comes in the formatting of certain cells.

What I have:

A column which is formatted in # ?/?... that is, ordinary integers and fractions. These are measurements and cannot be listed on the display as decimals. So I have a width of ... say 40, just for example. Height is (again, for example) 72. We will call these A column and B column for this illustration. Display of these whole integers is fine. DISPLAY OF THE NUMBERS AS FRACTIONS IS FINE. The problems come when I try to do the operations.

Adjacent columns are the cutback width and height. For example, I have a cell where the "Material" is supplied in a data validation cell with a pulldown menu, and you choose metal, wood, vinyl, or other. I have nested some "IF" statements in the cutback column (call them C and D for this example) to reference the data validation cell, so that IF the material is wood, the cutback width is A1 minus a fraction (varies with the material, but all fractional) and cutback height is B1 minus a different fraction.

That is if the widthin A1 is 40, and the cutback on wood is 1/4, the sheet does the math, and the display cell (C1) for the cutback reads 39 3/4. Pretty slick. HOWEVER, if the original measurement is a fraction, (the value entered in A1 is 40 3/4, the operation fails, as google sheets "sees" the original measurement as a text entry. For example, if the width is 40 1/2, THE MEASURED WIDTH IS FORMATTED AND RECORDED PROPERLY, but the operation fails and returns "Value!" error because google "sees" it as text for the purpose of the quant. This is so weird, because the operation succeeds if the original is a whole integer, and returns a fractional number as a result, but it will not work if the original number is a fraction. I thought about making two columns, one whole integer and one fraction, entering as decimals for the quants and seeing if I could only format at the end for display purposes, but this is beyond my scripting abilities.

Can anyone offer me help here? Be gentle, I am a codeaphobic and know next to nothing about this stuff.

  • pls share a copy/dummy of your sheet – user0 Feb 19 at 15:59
  • Sheets is just weak on this front. You can sidestep by entering all the mixed numbers as formulas. Related Question. – Joel Reid Mar 13 at 18:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.