Forum Discussion

Hui Zhen Tan's avatar
Hui Zhen Tan
Copper Contributor
Apr 23, 2018

Linking Various Formulas

Hi,

I would like to link 3 different formulas together, that allows me to edit the number without altering the formulas. For example, I want to link a formula to calculate cm, to feet, to inch and vice versa but a) I can't loop all 3 together, there would be an error and b) when I link cm-> feet -> inch, and when I edit the middle portion (feet), the link between cm and feet would disappear.

I hope I am not confusing anyone and any help is greatly appreciated!
Thank you!

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor

    Hi

     

    You didn't show your formulas.

    It better be something with CONVERT().

     

  • I'm confused! I "think" I know what you want but with a bit more info like a file attachment or a fuller description of what you've tried would be helpful.
    • Hui Zhen Tan's avatar
      Hui Zhen Tan
      Copper Contributor

      Hi,

       

      Here is a photo of what I am trying to do.

      So I want to be able to type in the box cm, feet or inch so that they will change accordingly like an auto calculator. So eg, if I type 1 feet, the inch would change to 12inch, cm to 30.4cm. If I were to type in the inch box, it should change the feet and cm accordingly.(:

      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor

        Hi

         

        A formula-wise solution you would need some helper cells. Otherwise you have to use a macro.

        I offer you the formula solution.

         

        cmftin  cmftin
         4 4ft121,92448

         

        D2: =LOOKUP(9^9,A2:C2) or SUM(A2:C2) or MEDIAN() or AVERAGE() ...

        E2: =IF(COUNT(A2:C2)=1,LOOKUP(9^9,A2:C2,A$1:C$1),"Type only ONE number")

        F2: =CONVERT($D2,$E2,F$1)

         

Resources