 SOLVED

New Contributor

# Formula help

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

8 Replies

# Re: Formula help

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

# Re: Formula help

Is the issue solved

# Re: Formula help

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.

# Re: Formula help

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

# Re: Formula help

As variant

``=MIN(5,8-A1)``

# Re: Formula help

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

# Re: Formula help

Yes, this worked. Thank you.

# Re: Formula help

@Jammor , you are welcome