Forum Discussion
Automatically enter a numeric value based on previous cell.
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
=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.
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
=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.
