SOLVED

Formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-1479051%22%20slang%3D%22en-US%22%3EFormula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479051%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20of%20numbers.%26nbsp%3B%20There%20are%20%26gt%3B200%20rows%2C%20with%20each%20cell%20in%20the%20column%20having%20one%20number%20between%201%20and%2010.%26nbsp%3B%20In%20the%20column%20next%20to%20it%2C%20I%20want%20to%20copy%20down%20a%20formula%20that%20outputs%20a%20different%20number.%26nbsp%3B%20If%20the%20number%20in%20the%20first%20column%20includes%201%2C%202%2C%20or%203%20then%20output%20the%20number%205.%26nbsp%3B%20If%20the%20number%20in%20the%20first%20column%20is%20a%204%20then%20output%20the%20number%204.%26nbsp%3B%20If%20the%20number%20in%20the%20first%20column%20is%20a%205%20then%20output%20the%20number%203%20and%20so%20on%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1%2C2%2C3%20-%26gt%3B%205%3C%2FP%3E%3CP%3E4%20-%26gt%3B%204%3C%2FP%3E%3CP%3E5%20-%26gt%3B%203%3C%2FP%3E%3CP%3E6%20-%26gt%3B%202%3C%2FP%3E%3CP%3E7%20-%26gt%3B%201%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHoping%20there's%20an%20easy%20way%20to%20do%20this%20and%20not%20have%20to%20use%20multi%20level%20nesting%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethx%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1479051%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479057%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479057%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F705690%22%20target%3D%22_blank%22%3E%40Jammor%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20IF(number%23%26lt%3B4%2C%205%2C%208-number%23)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479481%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479481%22%20slang%3D%22en-US%22%3EIs%20the%20issue%20solved%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479492%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479492%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F705690%22%20target%3D%22_blank%22%3E%40Jammor%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EThis%20should%20do%20the%20trick...%3C%2FP%3E%3CP%3E%3DIF(OR(A1%3D1%2CA1%3D2%2CA1%3D3)%2C5%2CIF(A1%3D4%2C4%2CIF(A1%3D5%2C3%2CIF(A1%3D6%2C2%2CIF(A1%3D7%2C1%2C0)))))%3C%2FP%3E%3CP%3EYou%20can%20replace%20the%20last%200%20with%20whatever%20you%20want%20to%20be%20displayed%20should%20the%20value%20in%20column%20A%20be%20outside%20the%20range%20you%20defined.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479525%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479525%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F699938%22%20target%3D%22_blank%22%3E%40DevendraJain%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20worked%20on%20my%20machine%20but%20I%20forgot%20to%20explain%20that%20%22%3CSTRONG%3Enumber%23%22%3C%2FSTRONG%3E%20was%20a%20dynamic%20range%20generated%20by%20the%20%3CSTRONG%3ERANDARRAY%3C%2FSTRONG%3E%20function.%26nbsp%3B%20That%20is%20the%20way%20I%20reference%20data%2C%20but%20any%20other%20form%20of%20reference%20to%20a%20whole%20number%20can%20be%20used%20(even%20the%20practice%20of%20direct%20cell%20references%20such%20as%20B27%20if%20you%20must!)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479636%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F705690%22%20target%3D%22_blank%22%3E%40Jammor%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMIN(5%2C8-A1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479730%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479730%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%20Yes%2C%20this%20worked%20great.%26nbsp%3B%20Thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479733%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479733%22%20slang%3D%22en-US%22%3EYes%2C%20this%20worked.%20Thank%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1480684%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1480684%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F705690%22%20target%3D%22_blank%22%3E%40Jammor%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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

 

thx

8 Replies
Highlighted

@Jammor 

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

Highlighted
Is the issue solved
Highlighted

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. 

Highlighted

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

Highlighted
Best Response confirmed by Jammor (New Contributor)
Solution

@Jammor 

As variant

=MIN(5,8-A1)
Highlighted

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

Highlighted
Yes, this worked. Thank you.
Highlighted

@Jammor , you are welcome