Jun 25 2018 01:49 AM
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 automatically
I'd like to allow for the below values:
(C31 = D31)
FAIL = 0
L1 = 1
L2P = 2
L2M = 3
L2D = 4
Additionally, whatever value appears in BOTH C31 and D31, I'd like them BOTH to have certain colours.
C31/D31 value = colour of both cells
FAIL/0 = Red
L1/1 = Dark Orange
L2P/2 = Orange
L2M/3 = Yellow
L2D/4 = Green
2) Next, the above formula will be repeated in 2 other places. (E31/F31 and G31/H31). I'll be able to copy & paste the formula just changing the letters.
However, I'd like to do a total. So I'll autosum D31, F31 and H31.
This total value will be in J31. Whatever the numerical value is in J31, I'd like it to show a text value in I31.
If its one of these numbers, I'd like it to show
FAIL = 0
L1 = 4,5,6,7
L2P = 8,9
L2M = 10,11
L2D = 12,13,14
L2D* = 15,16
For the values in I31 & J31, I'd like them both to be coloured.
FAIL = 0 = Red
L1 = 4,5,6,7 = Dark Orange
L2P = 8,9 = Orange
L2M = 10,11 = Yellow
L2D = 12,13,14 = Green
L2D* = 15,16 = Purple
3) Finally, For the value in I31, I'd like it to show the below exact values in K31. (i.e. 1-3 NOT 1,2,3)
I31 = K31
FAIL = 0
L1 = 1-3
L2P = 4
L2M = 5-6
L2D = 7-8
L2D* = 9
No colours for this cell.
Thank you soooo much in advance!!!
Jun 25 2018 07:27 AM
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
Jun 25 2018 04:11 PM
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)
Jun 26 2018 05:46 AM
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().