Automatically enter a numeric value based on previous cell.

Copper Contributor

Hi. I have created a table I wish to use for some reviews but am unsure if it is possible to get it to do what I want.

 

It is set up so lets say A1 as a score entered as 4 then A2 has a score 5. A3 then totals them to obviously give us 9. However, what I would like to do is have the next cell A4 automatically show a 5.

 

Its to change the value in A4 to tally up with a matrix scoring system, so if the total in A3 is 10-9 it would convert to 5, if it was 8-7 it would convert to 4, if it was 6 it would convert to 3, 5-4 would convert to 2 and finally 3-2 would convert to 1. 

 

Is this possible?

 

6 Replies

@Davew1978 

=IF(AND(A3>=2,A3<=3),1,IF(AND(A3>=4,A3<=5),2,IF(A3=6,3,IF(AND(A3>=7,A3<=8),4,IF(AND(A3>=9,A3<=10),5,"")))))
=IFERROR(CHOOSE(A3,"",1,1,2,2,3,4,4,5,5),"")

You can try a nested IF formula in cell A4. An alternative could be CHOOSE.

nested if.JPG

 

 

@OliverScheurich 

 

Thank you, that's does the first part and allows it too automatically enter a 5 if the value of A3 is 9 or 10.

 

Is there a way to expand it for the rest so if the total in A3 was 8 or 7 it would convert to 4, if it was 6 it would convert to 3, or 5 or 4 would convert to 2 and finally 3 or 2 would convert to 1?

 

Thanks

@Davew1978 

=IF(AND(A3>=2,A3<=3),1,IF(AND(A3>=4,A3<=5),2,IF(A3=6,3,IF(AND(A3>=7,A3<=8),4,IF(AND(A3>=9,A3<=10),5,"")))))

 

The formula already does what you are looking for. In the first condition it checks if the value in A3 is 2 or 3 and if this is true it returns 1.  In the second condition it checks if the value in A3 is 4 or 5 and if this is the case it returns 2. This is done correspondingly for all IF conditions.

alternatively: =ROUND((A3-1)/1.7,0)
Thank you, it might be the way I am typing it in to Excel, I can get it to show a 5 if the combined scores before are 9 or 10 but can't get it to show anything if the combined scores are lower.
That is fab that's does it exactly! I didn't think of the simpler way of dividing them!

Thank you both though