Forum Discussion

jensor4's avatar
jensor4
Copper Contributor
Feb 10, 2023
Solved

ACTION TRACKING SHEET

I am trying to come up with formula that will appear in column G that depends on entries in columns  B and C. There are only 2 permissable entries in column B (A or B). There are 4 permissisable entr...
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Feb 10, 2023

    jensor4 

    I would solve this with SWITCH(). There are certainly countless variations.

     

    =SWITCH(B4,"A",E4*(100*D4-100*F4-B$1),"B",SWITCH(C4,"P",E4*(100*F4-100*D4-B$1),"C",E4*(100*F4-100*D4-B$1),"S",E4*(F4-D4),"D",D4,""),"")

     

jensor4's avatar
jensor4
Copper Contributor
Feb 10, 2023
I've tried to do this by nesting if functions but can't get past 2 nested functions.
  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Feb 10, 2023

    jensor4 

    I would solve this with SWITCH(). There are certainly countless variations.

     

    =SWITCH(B4,"A",E4*(100*D4-100*F4-B$1),"B",SWITCH(C4,"P",E4*(100*F4-100*D4-B$1),"C",E4*(100*F4-100*D4-B$1),"S",E4*(F4-D4),"D",D4,""),"")

     

    • jensor4's avatar
      jensor4
      Copper Contributor
      Feb 11, 2023

      dscheikey 

      I liked your response, however it doesn't give the correct result in cell D5. I edited and came up with a potential solution that seems to work, but I did get a circular reference warning. Below is a screen shot showing what your suggestion yielded and beneath is what I came up. Note I simplified the setup for editing efficiency.

       

      • dscheikey's avatar
        dscheikey
        Bronze Contributor
        Feb 11, 2023

        jensor4 

        Like this?

        =SWITCH(B5,"A",1,"B",SWITCH(C5,"P",2,"C",2,"S",3,"D",4,""),"")

         

Resources