1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel help

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

  1. Droolinggimp

    Mobster

    Joined: 16 May 2004

    Posts: 3,805

    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
     
  2. dirtychinchilla

    Capodecina

    Joined: 2 May 2011

    Posts: 10,631

    Location: Woking, Surrey

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

    Soldato

    Joined: 19 Mar 2012

    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

    Gangster

    Joined: 9 Nov 2003

    Posts: 427

    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

    Mobster

    Joined: 16 May 2004

    Posts: 3,805

    Location: Derby

    @Foyman thanks you. that first formula worked as intended :)
     
  6. Droolinggimp

    Mobster

    Joined: 16 May 2004

    Posts: 3,805

    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.

    Thanks in advance again.:)
     
  7. bremen1874

    Capodecina

    Joined: 20 Oct 2008

    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.

    What is this spreadsheet for?
     
  8. Droolinggimp

    Mobster

    Joined: 16 May 2004

    Posts: 3,805

    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

    Capodecina

    Joined: 20 Oct 2008

    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

    Mobster

    Joined: 16 May 2004

    Posts: 3,805

    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

    Mobster

    Joined: 16 May 2004

    Posts: 3,805

    Location: Derby

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

    Mobster

    Joined: 16 May 2004

    Posts: 3,805

    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

    Soldato

    Joined: 25 Jun 2011

    Posts: 5,468

    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

    Capodecina

    Joined: 20 Oct 2008

    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

    Wise Guy

    Joined: 4 Jan 2004

    Posts: 1,159

    Location: Finally, Swindon

    SUMPRODUCT is hugely under-rated and under-used
     
  16. james.miller

    Capodecina

    Joined: 17 Aug 2003

    Posts: 19,537

    Location: Woburn Sand Dunes

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

    Soldato

    Joined: 25 Jun 2011

    Posts: 5,468

    Location: Yorkshire and proud of it!

    This is VLOOKUP:
    [​IMG]

    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.