Excel help

Droolinggimp · 17 Feb 2019 at 15:32

In an empty cell, I want it to show a number if the cell above it is a certain text. eg

I have 3 colours (in text not actual colours), Green, yellow and red. One of these colours is in a cell. Each colour has to have a value, Green is 2 yellow is 3 and red is 5

the cell with the colour in it is randomly selected out of the 3 each time F9 is pressed. I want a cell to change the number depending on what colour is in the cell above,

In my head its like this:
if cell x is green put a 2 in this one.

I hope this makes sense, I am not that clued up on excel tbh.

thanks in advance

Droolinggimp · 17 Feb 2019 at 15:50

@Foyman thanks you. that first formula worked as intended

Droolinggimp · 18 Feb 2019 at 01:02

Ok another one. The formula above worked BUT I need to have another cell show one of 3 numbers depending on what two other cells show.

I have a cell that displays randomly, one of these three text - Mental, sickness or physical
the cell below randomly displays one of the 3 colours, - Green, yellow or Red

I can envision this formula will be long for some reason but I need a cell to display 10 or 20 or 30 like this:

in my head (again) it looks like this.
if cell D10 is Mental and cell D11 is green, show 10
if cell D10 is Mental and cell D11 is yellow, show 20
if cell D10 is Mental and cell D11 is red, show 30
if cell D10 is physical and cell D11 is green, show 10
if cell D10 is physical and cell D11 is yellow, show 20
if cell D10 is physical and cell D11 is red, show 30
if cell D10 is sickness and cell D11 is green, show 10
if cell D10 is sickness and cell D11 is yellow, show 20
if cell D10 is sickness and cell D11 is red, show 30

D10 and D11 both show one of three options everytime f9 is pressed.

Thanks in advance again.

Droolinggimp · 18 Feb 2019 at 01:45

D11 is the text, green, yellow or red.

The spread sheet is for making random playing cards for a game I am currently designing. On the cards is a name of a patient, their condition (phys, sick, ment) severity (green, yellow or red) the cell that I am needing filled with one of the 3 numbers (10,20, or 30)is how many points player will need to treat the condition.

Droolinggimp · 18 Feb 2019 at 02:00

to be fair i should have put the actual numbers required but for simplicity's sake I juts used 3 numbers the same. Mental will have higher numbers than physical for example

Droolinggimp · 18 Feb 2019 at 02:01

ill try that formula tomorrow , its bed time for me now.

Thanks for replying late at night though. Very helpful

Droolinggimp · 18 Feb 2019 at 08:21

That formula worked, I copied and pasted IF(AND(D10="Mental", D11="Green"), 10, at the end and replaced green and number with yellow/red and other numbers and they show up when the cell have the combinations. How would I add the other conditions/colours and numbers?

Edit:NM I juts copied pasted the formula twice and change the text and colours. It works.