WHERE Clause In Filter

[JThompsonMBA03]

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.

[ChrisTX]

What value do you want if the Percentile is <= 95%?

Can you add a column for your expected output?

[JustinBabbitt]

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

[JThompsonMBA03]

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.48693517676656 1.51203400119517 1.52171575507257 1.52171575507257 1.52171575507257 1.52171575507257 1.52171575507257 1.52171575507257 1.52171575507257 1.52171575507257

[VianneyM]

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

[VianneyM · Accepted solution]

@JThompsonMBA03,

You can achieve this with the multi-row formula tool

Let me know if that works for you

Best,

Vianney

[VianneyM]

Attached also the workflow 

Best,

Vianney

[JThompsonMBA03]

This community is the best!

Thanks so much!