Hi.
I have a dataset which follows the following format:
Score | Percentile |
0 | 0% |
20 | 20% |
40 | 40% |
60 | 60% |
80 | 80% |
95 | 95% |
96 | 96% |
97 | 97% |
98 | 98% |
99 | 99% |
100 | 100% |
I need to add a column with an Adjusted Score.
I'm looking to set all actual scores to the score of the 95% Percentile IF the Percentile is > 95%.
My idea was
"IF [PERCENTILE] > 95.0 THEN
[SCORE] WHERE [PERCENTILE] == 95.0"
however this does not appear to be possible within the filter tool.
How could I best accomplish this? Multi-Row Formula tool?
I appreciate any ideas.
Thanks.
Solved! Go to Solution.
What value do you want if the Percentile is <= 95%?
Can you add a column for your expected output?
Hello JThompsonMBA03,
A formula tool can be used to add a column to your data or update a column based on existing data.
Using a formula tool, use your logic to identify the cells you wish to update.
IF Score >= 95 then 95
Else Score
Endif
My apologies as I may have oversimplified my example.
Here is some actual data. I need the Adjusted Score Column as the following
If Percentile > 95% then Adjusted Score == Actual Score Value just under 95%
The Actual Score value will be dynamic and constantly changing, therefore I cannot "hard code" a number into the formula - I need to use the value name in my coding. I can hard code the 95 percentile, but I cannot hard code an actual score value.
Thank you very much.
Actual Score | Percentile | Adjusted Score |
1.48611295698335 1.48693517676656 1.51203400119517 1.52171575507257 1.53622326052855 1.57318986904188 1.62201322469877 1.66351055705622 1.69075730496194 1.69683368831804 1.69746258587846 | 94.62 94.74 94.87 94.99 95.11 95.23 95.35 95.48 95.6 95.72 95.84 | 1.48611295698335 1.52171575507257 1.52171575507257 1.52171575507257 1.52171575507257 1.52171575507257 1.52171575507257 1.52171575507257 |
Hi @JThompsonMBA03,
attached you can find an example applying your logic. As mentioned before, you can use a formula tool to have your new Adjusted Score column
IF [Percentil_Num] > 95 THEN
95 ELSE [Score] ENDIF
Let me know if that helps
Best,
Vianney
You can achieve this with the multi-row formula tool
Let me know if that works for you
Best,
Vianney
This community is the best!
Thanks so much!