SOLVED

Formula help

Copper 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

 

thx

8 Replies

@Jammor 

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

Is the issue solved

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. 

@DevendraJain 

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 (Copper Contributor)
Solution

@Jammor 

As variant

=MIN(5,8-A1)

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

Yes, this worked. Thank you.

@Jammor , you are welcome

1 best response

Accepted Solutions
best response confirmed by Jammor (Copper Contributor)
Solution

@Jammor 

As variant

=MIN(5,8-A1)

View solution in original post