Help Required

Matt Jarvis · Mar 19, 2018

Hi all,

Hope all is well?

After some much needed help.

I've attached a spreadsheet for which I need the help.

In the attached I have a sampling of results and the winner only has a "prh" rating. What I need is for all horses in the race to have a "prh" rating. You will see for the first race I have populated cells H3:H14 with the required formula and cells I3:I14.

What I need help with is doing this across all races without having to manually alter. I need the formula or a Macro to take into account the spaces between each race. Take the "prh", ignore the winner and apply my formulas for the rest of the horses. Not sure if this can be done or not.

Thanks

ArkRoyal · Mar 19, 2018

What is the relevance of the information on Sheet1 as you are using the value in cell B2 ie 2.5?

Matt Jarvis · Mar 19, 2018

Hi ArkRoyal, its a pound for length value being used for the distance.

ARAZI91 · Mar 20, 2018

M Matt Jarvis - i dont know anything about macros etc , but this formula (12.5/D2)*G2 will get the same figures for your beaten lengths as you are getting now and might do away with the need for sheet 2.
In your sheet 1 Column D is Distance in Furlongs and Column G is cumulative lengths beaten - using the constant of 12.5 and dividing by the distance gives you your lbs per length figures that you are using now.
I have screenshotted another column on your sheet 2 showing you the formula.

ARAZI91 · Mar 20, 2018

M Matt Jarvis - having another look at your sheet , would this be better

This does away with sheet 2
The lbs per lengths figures are calculated automatically and your "new" PRH figures are calculated automatically in a new column at the end.
The only requirement is to populate (using fill) each race with the prh figure as the winner.(column H) and enter 0 in the btn column next to the winner.
Personally i would take the spacing out after each race as well , but that's just a personal choice.

I have returned your sheet as new - ( deleted sheet 2)
Maybe be of some use to you.

Matt Jarvis · Mar 20, 2018

ARAZI91 said:
M Matt Jarvis - having another look at your sheet , would this be better

View attachment 58920
This does away with sheet 2
The lbs per lengths figures are calculated automatically and your "new" PRH figures are calculated automatically in a new column at the end.
The only requirement is to populate (using fill) each race with the prh figure as the winner.(column H) and enter 0 in the btn column next to the winner.
Personally i would take the spacing out after each race as well , but that's just a personal choice.

I have returned your sheet as new - ( deleted sheet 2)
Maybe be of some use to you.

Morning Ark,

Many thanks for the attached and looking at this for me.

It's nearly what I need, however the prh ratings do not match in all cases.

For the first race for instance, in my initial attachment "Buying Trouble has a prh rating of 68 but on the attached Test 2 now has 73.

Thanks

Matt

ARAZI91 · Mar 20, 2018

Matt Jarvis said:
Morning Ark,

Many thanks for the attached and looking at this for me.

It's nearly what I need, however the prh ratings do not match in all cases.

For the first race for instance, in my initial attachment "Buying Trouble has a prh rating of 68 but on the attached Test 2 now has 73.

Thanks

Matt

M Matt Jarvis Hi Matt - not Ark but Arazi

- right so you are taking into account the weight carried as well?
Was not aware of that - ill have another look and come back too - im sure using the constant of 140 (lbs) in another column will suffice with a +/- function.

On second thoughts - maybe not - ill get back to you Matt.

ARAZI91 · Mar 20, 2018

M Matt Jarvis - if you were adjusting all ratings back to a base weight , like for example Timeform do 140lbs (10 stone) then i could easily set up another column Matt

M Matt Jarvis - This any use Matt?

This is using a base weight of 140 (lbs) like Timeform do - This puts the figures on a scale and takes into account weight carried
The figures are different but the differences between ratings are the same as your figures
For use in future races - Deduct the weight carried today from 140 and add to the figure in the L column.

ARAZI91 · Mar 20, 2018

ARAZI91 said:
M Matt Jarvis - if you were adjusting all ratings back to a base weight , like for example Timeform do 140lbs (10 stone) then i could easily set up another column Matt

M Matt Jarvis - This any use Matt?

This is using a base weight of 140 (lbs) like Timeform do - This puts the figures on a scale and takes into account weight carried
The figures are different but the differences between ratings are the same as your figures
For use in future races - Deduct the weight carried today from 140 and add to the figure in the L column.
View attachment 58922

In your example you are deducting or adding the difference in the weight carried from the winning horse - using a universal 140 lbs (10 stone) for example is the only way that my limited abilities can come up with - the figures might look different - but it is only a matter of scale - the realative values between the ratings remain the same if you check the two columns.
You can adjust the ratings back to the weight carried in future races by deducting the lbs carried from 140 and adding to the rating.

Maybe Ark will come up with a macro

ArkRoyal · Mar 20, 2018

I will put up a macro this evening for you to try.

ArkRoyal · Mar 20, 2018

Here is a macro to try

Code:

Sub PRH()

Dim RaceDistance As Double
Dim WinnerWgt As Integer
Dim HorseWgt As Integer
Dim WinnerPRH As Byte
Dim PoundsPerLengthConstant As Double
Dim DistanceBeat As Double

PoundsPerLengthConstant = 12.5

Range("H3").Select

Do Until ActiveCell.Offset(0, -3).Value = ""
    WinnerWgt = ActiveCell.Offset(-1, -2).Value
    WinnerPRH = ActiveCell.Offset(-1, 0).Value
    RaceDistance = ActiveCell.Offset(-1, -4).Value
    'If you do not want to use the correct race distance then use this line instead of the one above
    'RaceDistance = Round(ActiveCell.Offset(-1, -4).Value, 0)
    Do Until ActiveCell.Offset(0, -3) = ""
        DistanceBeat = ActiveCell.Offset(0, -1).Value
        HorseWgt = ActiveCell.Offset(0, -2).Value
        ActiveCell.Value = Round(WinnerPRH - ((PoundsPerLengthConstant / RaceDistance * DistanceBeat) + (WinnerWgt - HorseWgt)), 0)
        ActiveCell.Offset(1, 0).Select
    Loop
        
    ActiveCell.Offset(2, 0).Select
Loop

End Sub

It should be self explanatory but give me a shout if you don't understand anything.

Basically copy your data into the sheet, as you provided earlier, and run the macro to automatically create the PRH values for all other runners.

Matt Jarvis · Mar 20, 2018

ArkRoyal said:

Here is a macro to try

Code:

Sub PRH()

Dim RaceDistance As Double
Dim WinnerWgt As Integer
Dim HorseWgt As Integer
Dim WinnerPRH As Byte
Dim PoundsPerLengthConstant As Double
Dim DistanceBeat As Double

PoundsPerLengthConstant = 12.5

Range("H3").Select

Do Until ActiveCell.Offset(0, -3).Value = ""
    WinnerWgt = ActiveCell.Offset(-1, -2).Value
    WinnerPRH = ActiveCell.Offset(-1, 0).Value
    RaceDistance = ActiveCell.Offset(-1, -4).Value
    'If you do not want to use the correct race distance then use this line instead of the one above
    'RaceDistance = Round(ActiveCell.Offset(-1, -4).Value, 0)
    Do Until ActiveCell.Offset(0, -3) = ""
        DistanceBeat = ActiveCell.Offset(0, -1).Value
        HorseWgt = ActiveCell.Offset(0, -2).Value
        ActiveCell.Value = Round(WinnerPRH - ((PoundsPerLengthConstant / RaceDistance * DistanceBeat) + (WinnerWgt - HorseWgt)), 0)
        ActiveCell.Offset(1, 0).Select
    Loop
       
    ActiveCell.Offset(2, 0).Select
Loop

End Sub

It should be self explanatory but give me a shout if you don't understand anything.

Basically copy your data into the sheet, as you provided earlier, and run the macro to automatically create the PRH values for all other runners.

Absolute legend, first class. Can't thank you enough

FHRTIPS · Jul 27, 2018

ArkRoyal said:

Here is a macro to try

Code:

Sub PRH()

Dim RaceDistance As Double
Dim WinnerWgt As Integer
Dim HorseWgt As Integer
Dim WinnerPRH As Byte
Dim PoundsPerLengthConstant As Double
Dim DistanceBeat As Double

PoundsPerLengthConstant = 12.5

Range("H3").Select

Do Until ActiveCell.Offset(0, -3).Value = ""
    WinnerWgt = ActiveCell.Offset(-1, -2).Value
    WinnerPRH = ActiveCell.Offset(-1, 0).Value
    RaceDistance = ActiveCell.Offset(-1, -4).Value
    'If you do not want to use the correct race distance then use this line instead of the one above
    'RaceDistance = Round(ActiveCell.Offset(-1, -4).Value, 0)
    Do Until ActiveCell.Offset(0, -3) = ""
        DistanceBeat = ActiveCell.Offset(0, -1).Value
        HorseWgt = ActiveCell.Offset(0, -2).Value
        ActiveCell.Value = Round(WinnerPRH - ((PoundsPerLengthConstant / RaceDistance * DistanceBeat) + (WinnerWgt - HorseWgt)), 0)
        ActiveCell.Offset(1, 0).Select
    Loop
       
    ActiveCell.Offset(2, 0).Select
Loop

End Sub

It should be self explanatory but give me a shout if you don't understand anything.

Basically copy your data into the sheet, as you provided earlier, and run the macro to automatically create the PRH values for all other runners.

Hi be playing with this spreadsheet that you made a macro
Thing is the value in H2 does not change at all I wonder if you take a look please

ArkRoyal · Jul 27, 2018

It obviiously worked for M Matt Jarvis .

FHRTIPS can you post the sheet you you are using and i will have a look.

FHRTIPS · Jul 27, 2018

here it is

ArkRoyal · Jul 28, 2018

FHRTIPS The sheet is working fine. The winners rating (H2 in the first race) does not change it is used to calculate the rating for the other runners in the race. If you change the value in H2 and run the macro you will see the ratings for the other horses in the race are updated.

What are you trying to do? This is what M Matt Jarvis reqested.

Matt Jarvis said:
In the attached I have a sampling of results and the winner only has a "prh" rating. What I need is for all horses in the race to have a "prh" rating

FHRTIPS · Jul 28, 2018

Just using it see if its any good

FHRTIPS · Jul 29, 2018

ArkRoyal Hi how can I view the The Inner Sanctum please

ArkRoyal · Jul 29, 2018

FHRTIPS said:
ArkRoyal Hi how can I view the The Inner Sanctum please

BY simply taking part in discussions and generally contributing content to the forum.

For example you started this thread Gain That Winning Edge but only made one post. Running a similar thread would do you no harm particulary if it encourages other members to interact.

giuseppe_esq · Sep 12, 2018

Matt Jarvis said:
Hi all,

Hope all is well?

After some much needed help.

I've attached a spreadsheet for which I need the help.

In the attached I have a sampling of results and the winner only has a "prh" rating. What I need is for all horses in the race to have a "prh" rating. You will see for the first race I have populated cells H3:H14 with the required formula and cells I3:I14.

What I need help with is doing this across all races without having to manually alter. I need the formula or a Macro to take into account the spaces between each race. Take the "prh", ignore the winner and apply my formulas for the rest of the horses. Not sure if this can be done or not.

Thanks

Sorry to sound silly, but what exactly is the 'PRH' please?

Thanks
Giuseppe

Help Required

Yearling

Attachments

Administrator

Yearling

Mare

Mare

Attachments

Yearling

Mare

Mare

Mare

Attachments

Administrator

Administrator

Attachments

Yearling

Colt

Administrator

Colt

Attachments

Administrator

Colt

Colt

Administrator

Colt