Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Numerical value to appear in D31 automatically [Closed]

Report
Posts
1
Registration date
Thursday June 21, 2018
Status
Member
Last seen
June 25, 2018
 -
Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
 -
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!!!
See more

1 reply

Answer 1 / 1
Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
 440
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:
https://support.office.com/en-us/article/AND-function-5F19B2E8-E1DF-4408-897A-CE285A19E9D9

If you get stuck let us know what you tried.


Best regards,
Trowa
Ask a question