WHERE Clause In Filter

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.

Custom Tools

Best Practices

Preparation

Accepted answers

vmartinez

@JThompsonMBA03,

You can achieve this with the multi-row formula tool

Let me know if that works for you

Best,

Vianney

All comments

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