Forum Discussion

Al Robinson's avatar
Al Robinson
Copper Contributor
Jun 25, 2018

Excel Forumla help please!

1) Hi there, I'm trying to do a formula in a cell and need some help please. For the sake of this example, I'll be typing certain text into C31 and I'd like a numerical value to appear in D31 automat...
excel
Matt Mickle's avatar
Matt Mickle
Bronze Contributor
Jun 25, 2018

You can use a formula like this:

 

=IF(C31="FAIL",0,
IF(C31="L1",1,
IF(C31="L2P",2,
IF(C31="L2M",3,
IF(C31="L2D",4,"")))))

 

 

For Conditional formatting you can simply go to:

 

1. Conditional Formatting >Highlight Cells > Equal To

 

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 25, 2018

    As variant

    =LOOKUP(G31,{"FAIL","L1","L2P","L2M","L2D"},{0,1,2,3,4})

    or for Office365 subscribers

    =SWITCH(C31,"FAIL",0,"L1",1,"L2P",2,"L2M",3,"L2D",4)

     

    • Matt Mickle's avatar
      Matt Mickle
      Bronze Contributor
      Jun 26, 2018

      Hmmm...

       

      Didn't realize we had the Switch() Function.  I use it a fair amount in MS Access.  Pretty handy.  Another hidden gem that can be useful for dashboard production is Choose().

       

      https://support.office.com/en-us/article/switch-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e

       

      https://support.office.com/en-us/article/choose-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc

       

       

       

       

       

Resources