How to word an Expression

Adam-Adam

Hello All,

I was hoping you could help me with a query.

If I have a set of games and corresponding names and amount of money own.

I would like to formulate an expression whereby I can add the persons rank.

This is based on the assumption that the person who has won the highest money is in 1st position and so on and so on.

Here is what I have so far, this seems to be incorrect:

IF [Person_id] = [Row-1:Person_id] && [winner_flag] = "Y" THEN "FIRST"

ELSEIF [Row-2:Person_id] THEN "SECOND"

ELSEIF [Row-3:Person_id] THEN "THIRD"

ELSEIF [Row-4:Person_id] THEN "FOURTH"

ELSEIF [Row-5:Person_id] THEN "FIFTH"

ELSEIF [Row-6:Person_id] THEN "SIXTH"

ELSEIF [Row-7:Person_id] THEN "SEVENTH"

ELSEIF [Row-8:Person_id] THEN "EIGHTH"

ELSEIF [Row-9:Person_id] THEN "NINTH"

ELSEIF [Row-10:Person_id] THEN "TENTH"

ELSE "Other" ENDIF

Does anyone know which edits I need to make to the above expression so that it functions?

Thank you all in advance

Workflow

Datasets

Help

Accepted answers

Luke_C

Hi @Adam-Adam

Here is an example of what I described above:

New Workflow4.yxmd

phottovy

Here is a slightly modified version of @Luke_C example. The only real difference is I used the Multi-Row tool to rank by game instead of the Tile tool. The key with both examples is to use the "group by" option when calculating the rank. This allows the rankings to be recalculated for each game.

New Workflow4.yxmd

All comments

Luke_C

Hi @Adam-Adam

Can you provide some sample data? I would probably do something like:

Sort by Game + Money Won
Tile tool to assign ranks for each game
Create a static text input mapping 1 to First, 2 to Second, etc
Find and replace or Join to add the values (anything after 10 can be hardcoded to other)

Christina_H

Assuming the formula would do what you want it to if formatted correctly, you need to edit your ELSEIF statements. It should be

ELSEIF [Person_id]=[Row-2:Person_id] THEN "SECOND"

However there might be more efficient ways to achieve what you're trying to do if you can provide sample data.

Adam-Adam

Thank you both for your response@christina_Hurell

Sample Data

Game: Winner Flag Person ID Money Won

Darts Y Adam £10

N John £5

N Harry £1

Pool Y John £8

N Adam £1

N Harry £0

Snooker Y Harry £5

N John £3

N Adam £1

Now obviously the data set is much bigger than this so I want to be able to have a column in Excel where I can say that per game, this person came 1st, 2nd, 3rd, 4th, 5th etc. up to 10th position.