21

I want to be able to input plain numbers like 200, or 3000 and have the cell display 200g, 3kg, etc... But still be able to add the values in the cells. So if I add the two above I'd get 3.2kg on the result cell.

I suspect this is not possible, but is it?

4
  • I tried but was only able to change the color of the text or the shading of the cell via conditional formatting May 3, 2012 at 12:47
  • I think the answer of pnuts could work out. Have you tried it? Mar 14, 2014 at 8:26
  • 1
    It depends what you mean by work out. It doesn't address the core of the problem which is automatic unit-consciousness and division.
    – yarian
    Mar 14, 2014 at 20:54
  • 1
    What would be really nice is a spreadsheet which also calculated units. So you would get an error if you tried to add meter and liter. And m * m giving m^2
    – Lenne
    Sep 13, 2020 at 12:39

5 Answers 5

22

Does not manage the automatic division by 1000 for kilogrammes but perhaps of some use (better than nothing?)

In New Google Sheets, Format > Number > More Formats > Custom Number Format and Apply:

0,0 g
3
  • I can't seem to add a unit of 2 or more characters, i.e. km is not valid.
    – Morgoth
    Oct 24, 2017 at 10:57
  • 11
    @Morgoth It works if you double-quote the unit: 0,0 "km"
    – vieron
    Jan 3, 2018 at 15:16
  • Cool, thanks all. e.g. formats working for me 0.0"km" or 0.0"m"
    – gaoithe
    Oct 14, 2022 at 12:07
3

I found it.

Format > Number > More Formats > Custom Number Format
then enter: . "lbs"

If you want two decimal points then it would be: .00 "lbs"

Putting whatever you want in quotes allows you to create custom units of measurement.

1

There is an add-on that does this called LegendSheet. However, you don't have the number and the unit in the same cell rather you have to put them side by side. But it handles all the unit conversions such that you could do something like this:

  | A   | B    | C     | D | E                     |
--+-----+------+-------+---+-----------------------+
1 | 3.0 | [k]g | 200.0 | g | =LS_QADD(A1:B1,C1:D1) |

And the result in E1 will be 3.2 [k]g. Note that you have to use the brackets for unit prefixes. There is a help video on on YouTube that shows you can also do subtraction, multiplication and division and raise to a power numbers with units. It is a paid add-on, but you get a good amount of free uses per day that I find to be sufficient for my needs.

0

you can add to any cell: & "unit"

3
  • 3
    A description would be more helpful.
    – serenesat
    Jun 1, 2017 at 17:41
  • 4
    The OP still wants to be able to add the cells up, though.
    – jonsca
    Jun 1, 2017 at 22:55
  • Off topic but very helpful (we can use this to add the unit directly in the formula eg: (17+36)/3 & "€"
    – JinSnow
    Sep 5, 2021 at 15:12
0

Sorry to resurect this very old question but I was looking to do the same thing and could not find the answer anywhere. My missus is useless when it comes to multiplying a recipe from her books to cover the number of people she is cooking for so i created a spreadsheet for her to enter the number of people the recipe is aimed at and the number of people se is cooking for and calculate the weights. of course the recipies all use different measurements and I wanted to account for them all.

Here is what i ended up doing...

first format the column you want to enter the values into as plain text

format > number > plain text

This will allow a number without units to work with the following regex functions

add a few columns beside the column you want to enter your value into... in these columns add the formulas -

column C. =VALUE(REGEXREPLACE(B7,"[^[:digit:].]", ""))

column D. =REGEXREPLACE(B7,"[^[:alpha:]]", "")

this will split the entered measurement into its value and unit components.

now create a conversion table on a separate sheet that contains the following -

unit conversion rate base unit base increment multiplier
g 1 g kg 1000
kg 1000 g kg 1000
lb 454 g kg 1000
lbs 454 g kg 1000
oz 28 g kg 1000
ml 1 ml L 1000
L 1000 ml L 1000
pt 568 ml L 1000
qt 1137 ml L 1000
floz 29 ml L 1000
tsp 1 tsp tbsp 3
tbsp 1 tbsp tbsp 1
...

add whatever conversions you want on here

back on the main sheet continue adding columns to do the lookups

column E. =INDEX(Sheet2!$A$2:$C$99,MATCH(D7,Sheet2!$A$2:$A$99,0),3) 

this gets the base unit

column F. =if(E7="",1,INDEX(Sheet2!$A$2:$C$99,MATCH(D7,Sheet2!$A$2:$A$99,0),2))

gets the conversion rate (defaults to 1 if the unit entered is unknown)

column G. =F7*C7

Gets the total base qty for the recipe

column H. =round((G7/$K$3)*$K$4)

adjust for the number of people being served (k3 holds the number servings on the recipe, k4 holds the number of servings required)

column I. =if(E7="","",INDEX(Sheet2!$A$2:$E$99,MATCH(D8,Sheet2!$A$2:$A$99,0),4))

column J. =if(E7="",1,INDEX(Sheet2!$A$2:$E$99,MATCH(D7,Sheet2!$A$2:$A$99,0),5))

Gets the values needed to convert to the highest denomination of the measurement

column K. =if(H12/J12 >= 1,H12/J12 & " " & if(I12="",D12,I12), H12 & " " & E12)

this is the actual value we have been looking for. It checks if the conversion to the higher denomination is greater or equal to 1. If so it uses the higher denomination - otherwise it uses the base denomination.

Hide columns C - J and this is what you get -

Recipe Multiplier
recipe servings 4
req servings 6
ingredient recipe qty total qty
eggs 2 3
sugar 6 oz 252 g
milk 10 fl oz 435 ml
cinnamon 1 tsp 2 tsp
flour 750 g 1.125 kg
1
  • I like it. I would put the units as a select list to the right of the number values which would serve to allow the user to manipulate the ending unit, and also avoid typos in the beginning units. Nicely done! Sep 1, 2022 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.