Forum Discussion

dukeof3arl's avatar
dukeof3arl
New Member
2 years ago
Solved

Help with converting IFS into a Custom Column

Hey everyone,

 

I'm having an issue translating an excel formula into a custom column.  I'll illustrate below.

 

Here is the table for reference.  

 

Here is the excel formula:  =IFS(Target<0,(-(Actuals-Target)/Target)+1,Target>0,((Actuals-Target)/Target)+1,Target=0,(100%))

 

Basically if the actuals are zero then that's the highest desired attainment, capped at 200%.  However, if you hit your target you are "100%" of benchmark.  Anything exceeding the target will descend backwards from 99%.

 

Any help would be greatly appreciated!

 

-Luke

 

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    dukeof3arl ?? You can use SWITCH in a custom column.

    Column = 
SWITCH( TRUE(), 
  <logical condition 1>, <result if true>,
  <logical condition 2>, <result if true>,
  <logical condition 3>, <result if true>,
  ... (as many conditions/result pairs as you want)
  <result if no conditions are true>
)

6 Replies

    • dukeof3arl's avatar
      dukeof3arl
      New Member
      2 years ago

      Greg_Deckler - can you give me a pointer on the first part?  I'm unfamiliar with SWITCH or even how it would be used in this context.

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        2 years ago

        dukeof3arl Sure:

         

        SWITCH( TRUE(), 

          <logical condition 1>, <result if true>,

          <logical condition 2>, <result if true>,

          <logical condition 3>, <result if true>,

          ... (as many conditions/result pairs as you want)

          <result if no conditions are true>

        )