Jun 20 2020 03:44 PM - edited Jun 20 2020 03:45 PM
I have a column of numbers. There are >200 rows, with each cell in the column having one number between 1 and 10. In the column next to it, I want to copy down a formula that outputs a different number. If the number in the first column includes 1, 2, or 3 then output the number 5. If the number in the first column is a 4 then output the number 4. If the number in the first column is a 5 then output the number 3 and so on
1,2,3 -> 5
4 -> 4
5 -> 3
6 -> 2
7 -> 1
Hoping there's an easy way to do this
thx
Jun 21 2020 07:56 AM
Hi @Jammor,
This should do the trick...
=IF(OR(A1=1,A1=2,A1=3),5,IF(A1=4,4,IF(A1=5,3,IF(A1=6,2,IF(A1=7,1,0)))))
You can replace the last 0 with whatever you want to be displayed should the value in column A be outside the range you defined.
Jun 21 2020 08:56 AM
It worked on my machine but I forgot to explain that "number#" was a dynamic range generated by the RANDARRAY function. That is the way I reference data, but any other form of reference to a whole number can be used (even the practice of direct cell references such as B27 if you must!)
Jun 21 2020 11:21 AM
Solution