Formula to score based on conditions

[Raspotin]

Hello everyone !
I've been trying to solve this dilemma for a long time, but haven't been able to for weeks now after so many attempts.

I would like the formula to make a scoring, based precisely on the criteria you see, and which are> or <50, and Growing or slowing, and based on how the last data is classified (in cell F12), and in based on its conditions (in cells F and G23, which are dynamic and therefore change based on where we classify the last data).
I would therefore like that on the basis of these data, the foruma would go and attribute the relative score.
I have put examples below, a few examples to show how scoring works, and its criteria are written above.
And as for the extreme scores, therefore -8 to -10, and +8 and +10, require an extra condition, namely that the last PMI for <50 and Growing, is Last Value (Cell F12) <= Little ( B: B; 3).
instead for> 50 and Slowing, the PMI respects the condition Last value (Cell F12)> = Big (B: B; 3).
If they do not meet one of these conditions, they will simply be treated with the normal criteria of the other scores.


Thanks a lot to everyone for your cooperation and help !!

File Uploaded on drive :formula problem.xlsx

 

[KRice]

Would you please review a few things below? This problem lends itself well to using helper cells, as certain values need to be more readily accessible. Even with helper cells, the formulas become rather messy. I've manually assigned a row index (idx) to the original baseline scoring table, beginning with the most negative score zone and ending with the most positive one. Then using formulas, the scoring table is recreated in a more useful format, so that the starting score (strt) and the limit (lim) for each scoring zone are readily accessible. Another column ("Cond Met?) in the helper table performs logic checks to determine whether the "Status" shown in F23:G23 (e.g., >50 and Slowing) are found in the original scoring table descriptions, and if so, where. Since the last two rows of the scoring table have one additional logic check, the formulas include a feature to determine whether the 3rd or 4th condition should be applied, based on the row number.

I noticed several inconsistencies that lead me to question whether the offering below will really do what you want it to do.
1. The scores for the last two rows of the original scoring table have been swapped...could you confirm that doing so is correct? It seemed to me that >50 is associated with positive scoring zones elsewhere, and <50 is associated with negative scoring zones elsewhere, so I swapped the two scores.
2. Doing so (swapping rows 5 and 6) resolved some inconsistencies in the example table...please review the "Score Bounds" in that table and reconcile those with the text in the "Note" column. There are places where -8 and +8 are mentioned that appear to be incorrect. And the "Change" column shows 3 and 1 in 5th and 6th rows, but those are not consistent with the corresponding notes.
3. Please confirm what the additional criteria are for the 5th and 6th rows in the scoring table. I believe there are conflicting definitions shown when comparing cells E6:E7 to P15:P16: "for 1st time" vs. ">=big(k=3)"? Which is it? On this last item, does "big" mean ">=LARGE(B2:B1500,3)"?...you want to compare the Last Value to the 3rd largest value in column B? Or do you want to know whether the "Peak >50 and Slowing" has occurred for the first time in Column B?

I copied parts of the example table immediately below the original one and swapped elements in the 5th and 6th rows (score bounds and change). Please confirm that these swaps are what was intended based on your notes in the original table. The pink cells in O18:O23 use a formula to calculate a score for the conditions shown in the revamped example table. If these all check out okay, then let me know and I'll clean up the sheet so that the F10 scoring formula performs the same calculation, but pointing to the correct inputs elsewhere on the sheet.

Other observations:
1. None of the formula that you've used in the sheet need to be array formulas. I've eliminated the array formulas in this example.
2. The use of merged cells is highly discouraged. They create several unintended problems (e.g., when attempting to click on ranges for formula development, the desired range cannot be selected). I've eliminated several merged cells...and in nearly all cases, a simple multi-cell selection and then reformat to "center across selection" creates a heading that effectively spans the same cells.

formula problem_rev.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	DATE	PMI	Change		PMI			Score	idx	Cond Met?		idx	>=	Start and <
2	Sep-92	50.44			>50 and growing			+5 to +8	5	FALSE		1	-10	-8
3	Dec-92	47.59	-2.85		>50 and Slowing			+5 to 0	4	TRUE		2	-8	-5		>50 and growing	>50 and slowing	<50 and slowing	<50 and growing	Peak >50,slowing for 1st time	Trough <50,growing for 1st time
4	Mar-93	52.15	4.56		<50 and slowing			-5 to -8	2	FALSE		3	0	-5
5	Jun-93	55.36	3.21		<50 and growing			-5 to 0	3	FALSE		4	0	5
6	Sep-93	57.69	2.33		Peak >50,slowing for 1st time			+8 to +10	6	FALSE	S	5	8	5		+5 to +8	+5 to 0	-5 to -8	-5 to 0	-8 to -10	+8 to +10
7	Dec-93	59.47	1.78		Trough <50,growing for 1st time			-8 to -10	1	FALSE	S	6	10	8
8	Mar-94	61.77	2.30						4	<-- use idx
9	Jun-94	61.99	0.22					-8 to -10								Examples
10	Sep-94	61.49	-0.50		FINAL SCORE	1										Condition	Status		Score Bounds	PMI	Change	Score	Note
11	Dec-94	59.81	-1.68					Percentile									>50	Slowing	+5 to 0	56	-2.6	2	Because it's +5 (-2,6 that is the change),so the score is +2
12	Mar-95	54.3	-5.51		Last PMI	52.1		58%									>50	Growing	+5 to +8	54	5.1	8	Because it's +5 (+5,1 that is the change) but as the maximum score allowed is +8,the score is +8
13	Jun-95	50.72	-3.58														<50	Slowing	-5 to -8	43	-4.2	-8	Because it's -5(-4,2 that is the change),but as the maximum score allowed is -8,the score is -8(and not -9 )
14	Sep-95	48.44	-2.28														<50	Growing	-5 to 0	45	2.4	-3	Because it's -5(+2,4),so the score is -3
15	Dec-95	43.88	-4.56		ManufacturingPMI											Last Value>= big(k=3) of Row B	>50	Slowing	-8 to -10	63	3	9	Because it's +8(+1 that is the change),he score is +9
16	Mar-96	45.19	1.31		Last - 1	56.3	MIN	30.86								Last Value <= little(K=3) of row B	<50	Growing	+8 to +10	32	1	-10	Because it's -8(-3 that is the change),but as the max score allowed is -10 ,the score is -10(not -11)
17	Jun-96	46.34	1.15		Last Value	52.1	Average	51									start	equiv I8			equiv f18
18	Sep-96	47.27	0.93		Change	-4.2	MAX	63.1						0	2	2	5	4	+5 to 0		-2.6
19	Dec-96	49.36	2.09		Large(B:B,3)	61.99	FALSE	delete later						8	8	10	5	5	+5 to +8		5.1
20	Mar-97	51.11	1.75		Small(B:B,3)	32.02	FALSE	delete later						-8	-8	-10	-5	2	-5 to -8		-4.2
21	Jun-97	51.49	0.38											0	-3	-3	-5	3	-5 to 0		2.4
22	Sep-97	50.63	-0.86			Status								10	9	9	8	6	+8 to +10		1
23	Dec-97	51.06	0.43			>50	Slowing							-10	-10	-11	-8	1	-8 to -10		-3
24	Mar-98	50.38	-0.68
X

Cell Formulas
Range	Formula
M2:M7	M2	=INDEX(NUMBERVALUE(RIGHT($H$2:$H$7,LEN($H$2:$H$7)-SEARCH(" to ",$H$2:$H$7)-3)),MATCH($L2,$I$2:$I$7,0))
N2:N7	N2	=INDEX(NUMBERVALUE(LEFT($H$2:$H$7,SEARCH(" to ",$H$2:$H$7))),MATCH($L2,$I$2:$I$7,0))
J2:J7	J2	=AND(ISNUMBER(SEARCH($F$23,E2)),ISNUMBER(SEARCH($G$23,E2)),OR(ROWS(E$2:E2)<=4,AND(ROWS(E$2:E2)=5,$F$17>=LARGE($B$2:$B$1500,3)),AND(ROWS(E$2:E2)=6,$F$17<=SMALL($B$2:$B$1500,3))))
I8	I8	=INDEX($I$2:$I$7,AGGREGATE(14,6,(ROW($J$2:$J$7)-ROW($J$1))/($J$2:$J$7),1))
F12	F12	=LOOKUP(2,1/($B$2:$B$1500<>""),$B$2:$B$1500)
H12	H12	=PERCENTRANK.EXC($B$2:$B$1500,$F$12,2)
F16	F16	=INDEX(B:B,AGGREGATE(14,6,ROW($B$2:$B$1500)/($B$2:$B$1500<>""),2))
F17	F17	=LOOKUP(2,1/($B$2:$B$1589<>""),$B$2:$B$1589)
F18	F18	=F17-F16
H16	H16	=MIN($B$2:$B$1500)
H17	H17	=AVERAGE($B$2:$B$1500)
H18	H18	=MAX($B$2:$B$1500)
N18:N23	N18	=VLOOKUP($R18,$L$2:$N$7,2)
O18:O23	O18	=IF(OR(AND(INT(VLOOKUP($R18,$L$2:$N$7,3)+U18)>=VLOOKUP($R18,$L$2:$N$7,2),INT(VLOOKUP($R18,$L$2:$N$7,3)+U18)<=VLOOKUP($R18,$L$2:$N$7,3)),AND(INT(VLOOKUP($R18,$L$2:$N$7,3)+U18)<=VLOOKUP($R18,$L$2:$N$7,2),INT(VLOOKUP($R18,$L$2:$N$7,3)+U18)>=VLOOKUP($R18,$L$2:$N$7,3))),INT(VLOOKUP($R18,$L$2:$N$7,3)+U18),VLOOKUP($R18,$L$2:$N$7,2))
P18:P23	P18	=INT(Q18+U18)
Q18:Q23	Q18	=VLOOKUP($R18,$L$2:$N$7,3)
R18:R23	R18	=INDEX($I$2:$I$7,MATCH(S18,$H$2:$H$7,0))
F19	F19	=LARGE($B$2:$B$1500,3)
G19	G19	=$F$17>=F19
F20	F20	=SMALL($B$2:$B$1500,3)
G20	G20	=$F$17<=F20
F23	F23	=IF(F17>=50,">50","<50")
G23	G23	=IF(F17>F16,"Growing","Slowing")
C3:C24	C3	=IF(B3<>"",B3-B2,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R15	Cell Value	contains "Growing"	text	NO
R15	Cell Value	contains "Slowing"	text	NO
R13	Cell Value	contains "Growing"	text	NO
R13	Cell Value	contains "Slowing"	text	NO
R14,R16	Cell Value	contains "Growing"	text	NO
R14,R16	Cell Value	contains "Slowing"	text	NO
R12	Cell Value	contains "Growing"	text	NO
R12	Cell Value	contains "Slowing"	text	NO
R11	Cell Value	contains "Growing"	text	NO
R11	Cell Value	contains "Slowing"	text	NO
F17	Cell Value	<$I$15	text	NO
F17	Cell Value	>$I$15	text	NO
B2:B816	Cell Value	top 10% bottom values	text	NO
B2:B816	Cell Value	top 10% values	text	NO
G23	Cell Value	contains "Growing"	text	NO
G23	Cell Value	contains "Slowing"	text	NO

 

[Raspotin]

Thank you soo muchh !!!???? I appreacieated so much your help and the time you have spent to help me ! i've used your solution to resolve the problem,was working very well ,you're literally a genius KRice ?
Wish you all the best ??????



and sorry for the late reply ,totally forgot about my thread

 

[KRice]

I'm happy to help. I am hopeful that you reviewed my comments in post #2 (where I discuss some inconsistencies in the original post) and confirmed that the changes I made give the results you expect. If not, please let me know.