I am working on a spreadsheet where we are going to register the amount of chemicals and the end result should be in metric. The information we get can be in diffrent units like pund, ounces or kg. I have figured out the following formula for the conversion which work fine when converting from Lbs: =IF(I12="Lbs",CONVERT(N12,"lbm","kg"))
However, how can i choose Lbs or Kg in cell I12 in a dropdown, and get a result in a chosen cell in metric value?
Looking forward to your replay
Assuming the only options in I12 are lbs, ounces (or oz), and Kg, then this formula should work for you
=IFERROR(IF(I12="kg", N12, CONVERT(N12, IF(I12="lbs", "lbm", "ozm"), IF(I12="lbs", "kg", "g"))*IF(I12="lbs",1,0.001)),"")
It might be easier if you restrict the values in I12 to lbm, ozm or kg (using data validation list functionality). Then the formula would be
=IFERROR(IF(I12="kg", N12, CONVERT(N12, I12, IF(I12="lbm","kg","g"))*IF(I12="lbm", 1,0.001)),"")
Hope that helps
If you are looking to tell Excel what format the information is in (i.e. by using drop-down in I12 then presumably you would need to be able to choose Lbs, Ounces, Grams or KG then multiply by the appropriate conversion factor depending on the unit chosen. If however the data is consistently formatted (i.e. it is clearly indicated whether it is Lb, Oz, Kg or G) then you could have a formula to do this for you rather than having to choose what unit the data has been supplied in.
Can you give some examples of the data you will be given in terms of its formatting?
