Formula help

New Contributor

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



8 Replies


= IF(number#<4, 5, 8-number#)

Is the issue solved

Hi @Jammor,

This should do the trick...


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. 


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!)

Best Response confirmed by Jammor (New Contributor)


As variant


@Peter Bartholomew  Yes, this worked great.  Thank you.

Yes, this worked. Thank you.

@Jammor , you are welcome