Excel help

Droolinggimp

Droolinggimp

Droolinggimp

Soldato
Joined
16 May 2004
Posts
7,312
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.

thanks in advance
 
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.
 
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))
 
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 said:
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
Click to expand...
This only depends on the value of D11. D10 has no impact on the result and doesn't need to be included.

What is this spreadsheet for?
 
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.
 
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, "")
 
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
 
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.:)
 
wesimmo said:
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.
Click to expand...

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.
 
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.
 
bremen1874 said:
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.
Click to expand...

This is VLOOKUP:
Shared-Screenshot.jpg


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.
 
You must log in or register to reply here.
Back
Top Bottom