SOLVED

ACTION TRACKING SHEET

Copper Contributor

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 entries in column C (P, A, S, or D).

 

Screen Shot 2023-02-10 at 12.02.30 PM.png

Anyone up to the challenge?

5 Replies
I've tried to do this by nesting if functions but can't get past 2 nested functions.
best response confirmed by jensor4 (Copper Contributor)
Solution

@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,""),"")

 

@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.

Screen Shot 2023-02-11 at 9.58.06 AM.png

@jensor4 

Like this?

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

 

@dscheikey That didn't work. But this did since there were only 2 input variables in column B (A orB), but 4 in column C (P, C, S, D). Using If and Switch worked perfectly without having to nest:

Screen Shot 2023-02-11 at 7.17.17 PM.png

1 best response

Accepted Solutions
best response confirmed by jensor4 (Copper Contributor)
Solution

@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,""),"")

 

View solution in original post