How to Change the Value of a Cell based on the values of two other cells

[KH219]

Hi,

I've been trying to work this for ages and I just can't seem to fathom it. I'm very new to this so I'm a bit clueless.

I'm trying to quickly insert a value in a column based on 2 other columns

E.g.



Condition Score
Number of Problem s
Overall score


1
1



2
5



3
4



4
10




My rules are as follows:

If Condition Score is 1 and Number of problems is >= 0 or <=2 Then Overall Score is 1, if number of problems is outside of this range then the overall score is 5
If Condition Score is 2 and Number of problems is >=3 or <=7 Then Overall Score is 1, if number of problems is outside of this range then the overall score is 5
If Condition Score is 3 and Number of problems is >=8 or <=10 Then Overall Score is 1, if number of problems is outside of this range then the overall score is 5
If Condition Score is 4 and Number of problems is >=11 or <=15 Then Overall Score is 1, if number of problems is 10 or 16 then the overall score is 3, and if the number of problems are outside of these ranges then the overall score is 5

Can someone please help? I have about 100000 cells to process

[georgiboy]

Could try the below in cell C2 and drag down:

=IF(A2=1,IF(AND(B2>=0,B2<=2),1,5),IF(A2=2,IF(AND(B2>=3,B2<=7),1,5),IF(A2=3,IF(AND(B2>=8,B2<=10),1,5),IF(A2=4,IF(AND(B2>=11,B2<=15),1,IF(OR(B2=10,B2=16),3,5))))))

Tried my best to follow the logic

[KH219]

That worked, thank you so much!

[p45cal]

or in C2:
=IF(AND(A2=4,OR(B2=10,B2=16)),3,IF(MATCH(B2,{0,3,8,11,16})=A2,1,5))and copy down.
If you have Office365 then in a single cell on row 2:
=IF(AND(A2:A20=4,OR(B2:B20=10,B2:B20=16)),3,IF(MATCH(B2:B20,{0,3,8,11,16})= A2:A20,1,5))which will spill down. This particular formula looks at rows 2 to 20 so you may need to amend the referenced ranges to suit.