Formula to score based on conditions

Raspotin

New Member
Joined
Nov 21, 2020
Messages
8
Office Version
  1. 2019
Platform
  1. MacOS
Schermata 2021-03-18 alle 18.36.31.png




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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVW
1DATEPMIChange PMIScoreidxCond Met?idx>=Start and <
2Sep-9250.44>50 and growing+5 to +85FALSE1-10-8
3Dec-9247.59-2.85>50 and Slowing+5 to 04TRUE2-8-5>50 and growing>50 and slowing<50 and slowing<50 and growingPeak >50,slowing for 1st timeTrough <50,growing for 1st time
4Mar-9352.154.56<50 and slowing-5 to -82FALSE30-5
5Jun-9355.363.21<50 and growing-5 to 03FALSE405
6Sep-9357.692.33Peak >50,slowing for 1st time+8 to +106FALSES585+5 to +8+5 to 0-5 to -8-5 to 0-8 to -10+8 to +10
7Dec-9359.471.78Trough <50,growing for 1st time-8 to -101FALSES6108
8Mar-9461.772.304<-- use idx
9Jun-9461.990.22-8 to -10Examples
10Sep-9461.49-0.50FINAL SCORE1ConditionStatusScore BoundsPMIChangeScoreNote
11Dec-9459.81-1.68Percentile >50Slowing+5 to 056-2.62Because it's +5 (-2,6 that is the change),so the score is +2
12Mar-9554.3-5.51Last PMI52.158%>50Growing+5 to +8545.18Because it's +5 (+5,1 that is the change) but as the maximum score allowed is +8,the score is +8
13Jun-9550.72-3.58<50Slowing-5 to -843-4.2-8Because it's -5(-4,2 that is the change),but as the maximum score allowed is -8,the score is -8(and not -9 )
14Sep-9548.44-2.28<50Growing-5 to 0452.4-3Because it's -5(+2,4),so the score is -3
15Dec-9543.88-4.56ManufacturingPMILast Value>= big(k=3) of Row B >50Slowing-8 to -106339Because it's +8(+1 that is the change),he score is +9
16Mar-9645.191.31Last - 156.3MIN30.86Last Value <= little(K=3) of row B<50Growing+8 to +10321-10Because it's -8(-3 that is the change),but as the max score allowed is -10 ,the score is -10(not -11)
17Jun-9646.341.15Last Value52.1Average51startequiv I8equiv f18
18Sep-9647.270.93Change-4.2MAX63.102254+5 to 0-2.6
19Dec-9649.362.09Large(B:B,3)61.99FALSEdelete later881055+5 to +85.1
20Mar-9751.111.75Small(B:B,3)32.02FALSEdelete later-8-8-10-52-5 to -8-4.2
21Jun-9751.490.380-3-3-53-5 to 02.4
22Sep-9750.63-0.86Status109986+8 to +101
23Dec-9751.060.43>50Slowing-10-10-11-81-8 to -10-3
24Mar-9850.38-0.68
X
Cell Formulas
RangeFormula
M2:M7M2=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:N7N2=INDEX(NUMBERVALUE(LEFT($H$2:$H$7,SEARCH(" to ",$H$2:$H$7))),MATCH($L2,$I$2:$I$7,0))
J2:J7J2=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))))
I8I8=INDEX($I$2:$I$7,AGGREGATE(14,6,(ROW($J$2:$J$7)-ROW($J$1))/($J$2:$J$7),1))
F12F12=LOOKUP(2,1/($B$2:$B$1500<>""),$B$2:$B$1500)
H12H12=PERCENTRANK.EXC($B$2:$B$1500,$F$12,2)
F16F16=INDEX(B:B,AGGREGATE(14,6,ROW($B$2:$B$1500)/($B$2:$B$1500<>""),2))
F17F17=LOOKUP(2,1/($B$2:$B$1589<>""),$B$2:$B$1589)
F18F18=F17-F16
H16H16=MIN($B$2:$B$1500)
H17H17=AVERAGE($B$2:$B$1500)
H18H18=MAX($B$2:$B$1500)
N18:N23N18=VLOOKUP($R18,$L$2:$N$7,2)
O18:O23O18=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:P23P18=INT(Q18+U18)
Q18:Q23Q18=VLOOKUP($R18,$L$2:$N$7,3)
R18:R23R18=INDEX($I$2:$I$7,MATCH(S18,$H$2:$H$7,0))
F19F19=LARGE($B$2:$B$1500,3)
G19G19=$F$17>=F19
F20F20=SMALL($B$2:$B$1500,3)
G20G20=$F$17<=F20
F23F23=IF(F17>=50,">50","<50")
G23G23=IF(F17>F16,"Growing","Slowing")
C3:C24C3=IF(B3<>"",B3-B2,"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R15Cell Valuecontains "Growing"textNO
R15Cell Valuecontains "Slowing"textNO
R13Cell Valuecontains "Growing"textNO
R13Cell Valuecontains "Slowing"textNO
R14,R16Cell Valuecontains "Growing"textNO
R14,R16Cell Valuecontains "Slowing"textNO
R12Cell Valuecontains "Growing"textNO
R12Cell Valuecontains "Slowing"textNO
R11Cell Valuecontains "Growing"textNO
R11Cell Valuecontains "Slowing"textNO
F17Cell Value<$I$15textNO
F17Cell Value>$I$15textNO
B2:B816Cell Valuetop 10% bottom valuestextNO
B2:B816Cell Valuetop 10% valuestextNO
G23Cell Valuecontains "Growing"textNO
G23Cell Valuecontains "Slowing"textNO
 
Solution

Raspotin

New Member
Joined
Nov 21, 2020
Messages
8
Office Version
  1. 2019
Platform
  1. MacOS
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 :eek:
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,284
Messages
5,745,846
Members
423,981
Latest member
ph1l

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top