-6

enter image description here

Value1   Value2    Color
2        16        =IF(A2>5,"Red", IF(AND(5>A2>1,40>B2>1,"Yellow")),IF(AND(5>A2>1,40>B2>60,"Red")),IF(AND(5>A2>1,60>B2>100,"Yellow")),"Green)

The logic for which, I believe is:

If "Value1" > 5 ---> "Red"

If "Value1" is between 1-5 and "Value2 " is between 1-40 ---> "Yellow"

If "Value1" is between 1-5 and "Value2 " is between 40-60 ---> "Red"

If "Value1" is between 1-5 and "Value2 " is between 60-100 ---> "Yellow"

Else ---> "Green"

I want the color «red», «yellow» og «green» to be shown in a column based on two values from two different colums. I’ve tried to look it up but I can’t find anything to make my formula work. Hope the picture explains

It only says that the formula is wrong but I can’t figure out what to do…

Thomas Kimber
  • 10,601
  • 3
  • 25
  • 42
Martine
  • 1
  • 1
  • Why should I not upload images of code/data/errors : https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors – nick Jul 10 '23 at 08:19
  • It would be easier to help if you put the criteria in your post rather than in a picture of your computer screen. – Rory Jul 10 '23 at 08:19
  • Hi Martine, you've got `python` in your list of question tags, but I suspect you want an answer to cover a formula in excel that returns a text value based on the position of some input value in a range. – Thomas Kimber Jul 10 '23 at 08:21

3 Answers3

1
=IF(A2>5, "Red", IF(AND(A2>1, B2>1, B2<40), "Yellow", IF(AND(A2>1, B2>40, B2<60), "Red", IF(AND(A2>1, B2>60, B2<100), "Yellow", "Green"))))

or use this

=IF(OR(B2>=100, A2<=1), "Green", IF(OR(A2>5, 60>B2>40), "Red","Yellow"))
YellowFlash
  • 41
  • 6
0

Try this:

=IF(AND(A2<=5,B2<100,B2>60),"Yellow",IF(OR(A2>5,AND(B2>=40,B2<60)),"Red","Green"))

(Edited as I hadn't realised the mid-range for Value2 in 40-60 was to go red)

Rather than use (to me anyway) the confusing 1>x>100 way of expressing a range, I've instead put range boundaries as separate conditions within an AND clause. It's no better or worse, but I find it easier to parse that way.

Thomas Kimber
  • 10,601
  • 3
  • 25
  • 42
  • Thank you, the logic you wrote is correct! But it didn’t work. And excel only says «there is a problem with this formula» so I can’t tell what doesn’t work – Martine Jul 10 '23 at 08:42
  • When i copy paste that formula into cell C2, with values 1 and 2 in cells A2, and B2, it gives me an answer. – Thomas Kimber Jul 10 '23 at 08:46
  • 1
    You've got B2>39 and B2>60 in the same AND function. It would be easier to have the Yellow part be the Else result, I think. – Rory Jul 10 '23 at 09:33
  • Tried what you wrote @ThomasKimber and tried to change it with what you suggested Rory but none work… – Martine Jul 10 '23 at 09:53
0

thanks for the feedback! And sorry for posting the question in a wrong «format», will do better next time. The formula that worked for me in excel is: (i am writing this from my phone so sorry for any code block errors and such)

    =IF(A2>5; «Red»; IF(AND(1<A2;A2<5;1<B2;B2<40); «Yellow»; IF(AND(1<A2;A2<5;40<B2;B2<60); «Red»; IF(AND(1<A2;A2<5;60<B2;B2<100); «Yellow»; «Green»))))
Martine
  • 1
  • 1
  • 1
    Horrible! Why? Observe [this image](https://i.stack.imgur.com/RD4i7.png) which illustrates how your formula works. The purple color designates the rows containing suspicious results (in the resulting `C` column). For the most of it, you might say that it is the correct behavior but there is no way that `40` and `60` in column `B` should return `Green`. That being said, you also want to add some equal signs that will cover for some of the edge cases (`>=` or `<=`). Have a think and then accurately explain what should be returned in the suspicious (purple) cases or correct the formula yourself. – VBasic2008 Jul 10 '23 at 16:32
  • It has acutally worked fine so far for green and red. But does not work at all for yellow. I’ve tried and tried (and even chanhed to >=/<=) but doesn’t work… so if you have any idea on how I can get yellow to work I would like to hear:) – Martine Jul 11 '23 at 15:37
  • 1
    Many will exactly know what to do if you explain accurately what needs to be returned. You can start by telling us which values are wrong in the image I posted in my previous comment. – VBasic2008 Jul 11 '23 at 16:12