Math - Formula + Expression ?

[Brain]

Hi?
Here is a small excerpt of my dataset. I could do it using Excel, but it would take too much time and could potentially lead to errors.
This dataset is the result of three different races (RNO). Each time, a rating was established for the first-place finisher. This rating will not change.
Now I want to calculate the rating for the 2nd and 3rd place finishers of each race. This rating will take into account the distance of the race and the winner’s times (TIME), (TPS EP), and (400F).
I want to creat new columns : EP1 ; LF1; SR1
This the excel formula for each column :

SR1 = (1-(WIN_TIME/HORSE_TIME)^(RACE_DIST/1000))*1000
Example for RNO1=> (1- (95,08/95,15)^(1600/1000))*1000
same formula for the 3rd but HORSE_TIME change
EP1 = (1-(71,41/71,93)^(1200/1000))*1000
LF1 = (1-(23,67/23,22)^(400/1000))*1000

Is it doable? Considering there are many races and often more than 10 runners.
Thanks
Br
essai.xlsx (10.2 KB)

[MartinDDDD]

Hey there :-).

In 5.4 Expression Node is the perfect choice for this as you can do all three calculations in just one node.

I’ve wrapped this inside a group loop (grouping by RNO), then split off first place and turn the column values into variables.

Then in the Expression Node there are three expression that replace the corresponding columns for 2nd place an onwards.

I copied your example calculations into excel to compare against the results from Expression node:

Excel:

Expression:

WF overview:

Expressions:

My workflow:

horseracing.knwf (93.5 KB)

[Brain]

Thanks for your quick answer.
My problem is when the distance change.

In the formula for example :
SR1 = (1-(WIN_TIME/HORSE_TIME)^(RACE_DIST/1000))*1000
Example for RNO1=> (1- (95,08/95,15)^(1600/1000))*1000
same formula for the 3rd but HORSE_TIME change
EP1 = (1-(71,41/71,93)^(1200/1000))*1000
LF1 = (1-(23,67/23,22)^(400/1000))*1000
FULL DISTANCE is 1600

If the distance is 1200 = SR1 race distanc echange in the formula
EP1 …^(800/1000))*1000
Thanks
Br

[MartinDDDD]

ok just to confirm I understand it right:

SR is calculated for full distance = 1600 => therefore ^1600/1000 in formula
EP is calculated for distance = 1200 => therefore ^ 1200/1000 in formula
LF is calculated for distance = 400 => therefore ^400/1000 in formula.

Means: if Distance is 1600 then column SR to be calculated, EP and LF not required
if Distance is 1200 then column to be EP to be calculated, SR and LF not required
if Distance is 400 then column LF to be calculated, SR and EP not required.

Is that correct? If so I think I can easily amend.

[Brain]

It means if Full distance is 1600:
SR over 1600
EP over 1200
400F over 400

if full distance is 1200
SR over 1200
EP over 800
400F over 400

400F will always be = 400

[MartinDDDD]

Ok rather than hardcoding the values I pass in the race distance as a variable and use it in SR calc. In EP calc it is then race distance - 400.

This should work for scenarios where race distance is either 1600 or 1200.

Are there any other distances?

Modifications to the Expressions:

Updated WF:

horseracing.knwf (93.9 KB)

[Brain]

There are other distances but i will try this formula over this distance in a first time.
Thanks a lot for your help as usual
Br

[MartinDDDD]

Cool. Let me know how you go.

If it is always the same logic - i.e. race distance is x:
SR calculated with ^x/1000, EP with ^((x-400)/1000) and LF always with ^400/1000 then this should work universally.

If the logic deviates for other distances (for whatever reason - not a horse racing expert here ), then it might be worthwhile to put together a mapping table that maps distance to: SR factor, EP factor, LF factor… then the WF can be easily modified to accommodate a somewhat finite range of different calculation options.

[Brain]

Hi Martin,
I am moving forward with the same idea. I have created new columns to make it easier to analyze the evolution of the SR, EP, and LF calculations.
Column SR2 EP2 and LF2 are what i would like to have.
As you see column values never change for position 1. What I am trying to do is subtract the values we obtained from those corresponding to position 1. (attached file).
Classeur1.xlsx (9.8 KB)
horseracing.knwf (129.8 KB)

Thanks for your help
Br

[MartinDDDD]

I see.

You can do this directly inside the loop - no need for the Expression after Loop end:

• use Table Row to Variable to also turn SR/EP/LF from first place into variables
• Use these in an additional Expressions node for first row to populate values for SR1/EP1/LR1
• Also use these in initial Expressions node - e.g. change to “SR from 1st place variable” - “previous calculation”

Overview:

Workflow:
horseracing1.knwf (96.6 KB)

[Brain]

Hi Martin,

There is a calculation issue with the LF1 column. The other two are perfect. You can check the difference with this excel file.
Classeur1.xlsx (9.8 KB)

Thanks
Br

[MartinDDDD]

Mind telling me what needs to change?

[Brain]

The result column LF1 after calculation is incorrect.

[MartinDDDD]

Got it. Misclicked when choosing the flow variable in Expressions - used 400F column from first place instead of LF column - changed it to this so should now work:

WF:
horseracing2.knwf (96.9 KB)

Wishing you a happy new year

[Brain]

Perfect
HNY 2025 !!!