# Excel help

Discussion in 'Windows & Other Software' started by Droolinggimp, 17 Feb 2019.

1. Droolinggimp

# Location: Derby

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.

2. dirtychinchilla

# Location: Woking, Surrey

Use conditional formatting and maybe make the text the same colour as the cell perhaps.

3. wesimmo

# Posts: 5,587

I'd probably use a VLOOKUP for that.

Some people might do a couple of nested IF statements but for me the VLOOKUP is more elegant and is more flexible if you want to introduce more colours and numbers.

4. Foyman

# Location: nr Stoke

If you want to go the way of nested IF statements -

If cell may be blank:

=IF(A1="Green",2,IF(A1="Yellow",3,IF(A1="Red",5,"")))

If cell always contains one of the three colours:

=IF(A1="Green",2,IF(A1="Yellow",3,5))

5. Droolinggimp

# Location: Derby

@Foyman thanks you. that first formula worked as intended

6. Droolinggimp

# Location: Derby

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.

7. bremen1874

# Posts: 12,041

This only depends on the value of D11. D10 has no impact on the result and doesn't need to be included.

8. Droolinggimp

# Location: Derby

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.

9. bremen1874

# Posts: 12,041

In your list of options Green always equals 10, Yellow always equals 20, and Red always equals 30. This doesn't change if the value of D10 is different so there's no point including it.

You can combine IF and AND like this if you need to:

=IF(AND(D10="Mental", D11="Green"), 10, "")

10. Droolinggimp

# Location: Derby

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

11. Droolinggimp

# Location: Derby

ill try that formula tomorrow , its bed time for me now. Thanks for replying late at night though. Very helpful

12. Droolinggimp

# Location: Derby

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.

13. h4rm0ny

# Location: Yorkshire and proud of it!

What I was going to say. Some IF statements will do the job with just three, but it will be an increasing pain to keep up to date if you add more. Have two columns with the text ("Green", "Red", etc.) on the left and the number it equates to on the right. Stick that somewhere out of the way on your spreadsheet or in a different sheet if you want it to be really hidden. Then in the cells where you want this to happen add a VLOOKUP formula using those two columns you've just created as the source data. You should be able to work it out from this.

14. bremen1874

# Posts: 12,041

Does VLOOKUP handle two input parameters (easily)?

You can have a series of IF(AND statements in different cells that either return a value or zero and then sum the results for the actual value.

15. Pipe & Slippers

# Location: Finally, Swindon

SUMPRODUCT is hugely under-rated and under-used

16. james.miller

# Location: Woburn Sand Dunes

id be using tables and vlookups for this, soo much easier to manage.

17. h4rm0ny

# Location: Yorkshire and proud of it!

This is VLOOKUP:

The blue area is your source data. The yellow is the square with the VLOOKUP function in it and you can see it has looked up the cell to the left of it (C7) in the blue table and found that Green is 2.

Now what you're asking is how to combine extra categories of data (mental, physical, etc.) so you should add a second table (like the Red, Green, Blue) and have a second VLOOKUP formula that works on that.

If it's a simple SUM of the two numbers, then just have a new formula again that sums the results of the two look ups. Naturally you'll then drag all these formulas down for as many rows as you want. You'll need \$ symbols in your VLOOKUP formula to make sure the range stays the same when you drag the formula to other cells.