# Numerical value to appear in D31 automatically

Closed
bigalogigalo Posts 1 Registration date Thursday June 21, 2018 Status Member Last seen June 25, 2018 - Updated on Jun 25, 2018 at 04:50 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Jun 26, 2018 at 11:34 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!!!

## 1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 549
Jun 26, 2018 at 11:34 AM
Hi bigalogigalo,

What you are looking for are nested IF's and the AND function

Nested IF'S:
https://www.excel-easy.com/examples/if.html

AND function: