Home

How to return multiple results based on multiple criteria

keatsy3
New Contributor

I have 8 groups that i need to place individual entries into.

The individual entries return as numbers between 1 and 16. 

1-4 need to go into group 1

4-8 need to go into group 2

and so on

 

I.E... 

1    1

2    1

3    1

4    1

5    2

6    2

7    2

8    2

and so on 

 

The same also needs to be done for the inverse at the same time

6 Replies

Do you mean to calculate group # like

=MOD(A1-1,4)+1

?

No, I mean....

If the number in cell A is between 1 & 4, I want cell B to return 1
If the number in cell A is between 5& 8, cell B returns 2
and so on

That's exactly what above formula do, assuming in column A are integers. Please check attached file

Basically, is there an easier and quicker way to do this:

=IFS(G10 =1, 1, G10= 2,1, G10 =3,1, G10 =4,1, G10 =5,2, G10 =6,2, G10 =7,2, G10 =8,2, G10 =9,3, G10 =10,3, G10 =11,3, G10 =12,3, G10 =13,4, G10 =14,4, G10 =15,4, G10 =16,4, G10 =-1, 5, G10= -2,5, G10 =-3,5, G10 =-4,5, G10 =-5,6, G10 =-6,6, G10 =-7,6, G10 =-8,6, G10 =-9,7, G10 =-10,7, G10 =-11,7, G10 =-12,7, G10 =-13,8, G10 =-14,8, G10 =-15,8, G10 =-16,8)

That could be

=ABS(ROUNDUP(G10/4,0))+4*(G10<0)
Yeah... I've just gone ahead and used an absurdly long IFS command

Seems to work... good luck to any slower computer that attempts to run it though!

Thanks for your help!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies