 • 410K Members
• 9,815 Online
• 466K Conversations

How to return multiple results based on multiple criteria

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

Re: How to return multiple results based on multiple criteria

Do you mean to calculate group # like

=MOD(A1-1,4)+1

?

Re: How to return multiple results based on multiple criteria

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

Re: How to return multiple results based on multiple criteria

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

Re: How to return multiple results based on multiple criteria

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)

Re: How to return multiple results based on multiple criteria

That could be

=ABS(ROUNDUP(G10/4,0))+4*(G10<0)

Re: How to return multiple results based on multiple criteria

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