Forum Discussion
Automatically enter a numeric value based on previous cell.
=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.
- 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.
- alternatively: =ROUND((A3-1)/1.7,0)
- That is fab that's does it exactly! I didn't think of the simpler way of dividing them!
Thank you both though
