Formula to identify outliers between the values in multiple cells

[mafallaize]

Hi guys,

Cell B1 contains the number of assessments, which can change from 1 to 10.
The table counts the number of assessments with each result, either "VH","H","M" or "L". For the example below, there are 4 assessments.
I'm looking for a formula for column G which can be copied down:

• Returns "No" if all assessments are of one type (VH, H, M, L) e.g. 0004 or 0400
• Returns "Yes" if the assessments occupy 2 adjacent types e.g. 0022 or 0220
• Returns "Big" if the assessments occupy 3 or 4 types or 2 non-adjacent types e.g 0111,1110, 3001 or 2002

In other words, if the rating between assessments is wildly different, we identify it with Big. If it is slightly different, "Yes" or if the same for each then "No"

Here's an example table:

Number of Assessments:	4
VH	H	M	L	N/A	Outlier
0	0	4	0	0	No
0	0	2	2	0	Yes
4	0	0	0	0	No
0	0	4	0	0	No
0	0	3	1	0	Yes
0	4	0	0	0	No
1	0	0	0	3	No
0	0	1	1	2	Yes
0	0	3	0	1	No
0	1	2	0	1	Yes
0	3	0	1	0	Big

I appreciate your assistance, thanks!

 

[JamesCanale]

MrExcelPlayground13.xlsx
	A	B	C	D	E	F	G
1	VH	H	M	L	N/A	Outlier
2	0	0	4	0	0	No	No
3	0	0	2	2	0	Yes	Yes
4	4	0	0	0	0	No	No
5	0	0	4	0	0	No	No
6	0	0	3	1	0	Yes	Yes
7	0	4	0	0	0	No	No
8	1	0	0	0	3	No	No
9	0	0	1	1	2	Yes	Yes
10	0	0	3	0	1	No	No
11	0	1	2	0	1	Yes	Yes
12	0	3	0	1	0	Big	Big
Sheet18

Cell Formulas
Range	Formula
G2:G12	G2	=CHOOSE(INT(LOG10(VALUE(RIGHT(VALUE(A2&B2&C2&D2)/10000,LEN(VALUE(A2&B2&C2&D2)/10000)-2)))+1),"No","Yes","Big")

Here it is with all the helper columns expanded to understand it better:

MrExcelPlayground13.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	VH	H	M	L	N/A	Outlier
2	0	0	4	0	0	No	No	40	0.004	4	1	No
3	0	0	2	2	0	Yes	Yes	22	0.0022	22	2	Yes
4	4	0	0	0	0	No	No	4000	0.4	4	1	No
5	0	0	4	0	0	No	No	40	0.004	4	1	No
6	0	0	3	1	0	Yes	Yes	31	0.0031	31	2	Yes
7	0	4	0	0	0	No	No	400	0.04	4	1	No
8	1	0	0	0	3	No	No	1000	0.1	1	1	No
9	0	0	1	1	2	Yes	Yes	11	0.0011	11	2	Yes
10	0	0	3	0	1	No	No	30	0.003	3	1	No
11	0	1	2	0	1	Yes	Yes	120	0.012	12	2	Yes
12	0	3	0	1	0	Big	Big	301	0.0301	301	3	Big
Sheet18

Cell Formulas
Range	Formula
G2:G12	G2	=CHOOSE(INT(LOG10(VALUE(RIGHT(VALUE(A2&B2&C2&D2)/10000,LEN(VALUE(A2&B2&C2&D2)/10000)-2)))+1),"No","Yes","Big")
H2:H12	H2	=VALUE(A2&B2&C2&D2)
I2:I12	I2	=H2/10000
J2:J12	J2	=VALUE(RIGHT(I2,LEN(I2)-2))
K2:K12	K2	=INT(LOG10(J2)+1)
L2:L12	L2	=CHOOSE(K2,"No","Yes","Big")

 

[JamesCanale]

This is better. That last one didn't take the widest split.

MrExcelPlayground13.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	VH	H	M	L	N/A	Outlier
2	0	0	4	0	0	No	No	40	0.004	4	1	No
3	0	0	2	2	0	Yes	Yes	22	0.0022	22	2	Yes
4	4	0	0	0	0	No	No	4000	0.4	4	1	No
5	0	0	4	0	0	No	No	40	0.004	4	1	No
6	0	0	3	1	0	Yes	Yes	31	0.0031	31	2	Yes
7	0	4	0	0	0	No	No	400	0.04	4	1	No
8	1	0	0	0	3	No	No	1000	0.1	1	1	No
9	0	0	1	1	2	Yes	Yes	11	0.0011	11	2	Yes
10	0	0	3	0	1	No	No	30	0.003	3	1	No
11	0	1	2	0	1	Yes	Yes	120	0.012	12	2	Yes
12	1	3	0	1	0	Big	Big	1301	0.1301	1301	4	Big
Sheet18

Cell Formulas
Range	Formula
G2:G12	G2	=CHOOSE(INT(LOG10(VALUE(RIGHT(VALUE(A2&B2&C2&D2)/10000,LEN(VALUE(A2&B2&C2&D2)/10000)-2)))+1),"No","Yes","Big","Big")
H2:H12	H2	=VALUE(A2&B2&C2&D2)
I2:I12	I2	=H2/10000
J2:J12	J2	=VALUE(RIGHT(I2,LEN(I2)-2))
K2:K12	K2	=INT(LOG10(J2)+1)
L2:L12	L2	=CHOOSE(K2,"No","Yes","Big","Big")

 

[mafallaize]

This is better. That last one didn't take the widest split.

MrExcelPlayground13.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	VH	H	M	L	N/A	Outlier
2	0	0	4	0	0	No	No	40	0.004	4	1	No
3	0	0	2	2	0	Yes	Yes	22	0.0022	22	2	Yes
4	4	0	0	0	0	No	No	4000	0.4	4	1	No
5	0	0	4	0	0	No	No	40	0.004	4	1	No
6	0	0	3	1	0	Yes	Yes	31	0.0031	31	2	Yes
7	0	4	0	0	0	No	No	400	0.04	4	1	No
8	1	0	0	0	3	No	No	1000	0.1	1	1	No
9	0	0	1	1	2	Yes	Yes	11	0.0011	11	2	Yes
10	0	0	3	0	1	No	No	30	0.003	3	1	No
11	0	1	2	0	1	Yes	Yes	120	0.012	12	2	Yes
12	1	3	0	1	0	Big	Big	1301	0.1301	1301	4	Big
Sheet18

Cell Formulas
Range	Formula
G2:G12	G2	=CHOOSE(INT(LOG10(VALUE(RIGHT(VALUE(A2&B2&C2&D2)/10000,LEN(VALUE(A2&B2&C2&D2)/10000)-2)))+1),"No","Yes","Big","Big")
H2:H12	H2	=VALUE(A2&B2&C2&D2)
I2:I12	I2	=H2/10000
J2:J12	J2	=VALUE(RIGHT(I2,LEN(I2)-2))
K2:K12	K2	=INT(LOG10(J2)+1)
L2:L12	L2	=CHOOSE(K2,"No","Yes","Big","Big")

Wow that was quick! I wrapped it in an iferror to capture quad zeros, works like a charm! Thank you very much James, clever stuff XD.

 

[JamesCanale]

0000! There is always another case. A valuable lesson.