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