Excel offset percentage calculation

[doris_day]

I'm trying another couple of algorithms I've been researching but find the offset calculation in Excel frustrating and wondered if anyone had a workround.

At the moment the calculation is a straightforward percentage including the stake. So if we have a price of 5.0 and want the offset to be at 50% the bet is placed at 2.50. My way of calculating the proper price is ((5.0-1)*50%)+1
which comes to 3.0.

I need a workround to get the offset set at my calculation. Any ideas ?

[osknows]

Hi doris,

I hope you're well.

I'm not quite sure what you're asking as OFFSET is a function in Excel which does something very different.

If you mean add a tick offset modifier then -Pn applies a % tick offset from the original bet. There's not much explanation in the help file but I'd assume it should be the same calc as you've outlined?

The alternative is -Tn, you could calculate the % requirement manually and then use the tickdiff function n = TickDiff(3,5) to give the number of ticks (see here http://www.gruss-software.co.uk/forum/v ... 6&start=15)

If I've misunderstood.. apologies

[doris_day]

Hi Os, yes fine here and almost finished the house. At long last. Did I settle up with you btw ? I have this awful feeling I owe you some money but I may be wrong. I've been so busy finishing the house.

The problem with Tn is, I wont know what the price is and how many ticks I'll need. That's why I need Pn. Im not certain why the boys decided to use a percentage that included the stake. It may be useful for others but its a pain for me.

[osknows]

All is good, you don't owe me anything.

If you know the back price, say X, you want the offset to be 50% which gives Y

In excel you can use the function =TickDiff(X,Y) to give the tick difference for any values you want. That way Tn isn't a fixed value but are between any odds you wish

Eg, using the example for odds of 5.0

Assuming cells
[AA1] = 5.0
[AA2] = (AA1-1)*0.5+1

Feed that into a trigger such as ="BACK-T" & TickDiff(AA1,AA2)

[doris_day]

Yes, I understand the logic now.

Should work but for some reason I get VALUE! when using the function TickDiff. MinusTicks and PlusTicks works OK so I dont see why TickDiff shouldn't. No doubt there's a simple explanation

[doris_day]

Now using the function with Gruss refreshing the sheet and sometimes I get the error VALUE! and sometimes a number which is odd

[doris_day]

I think it must be due to the new value I have calculated not being a valid tick value. How can I round to the nearest valid tick value ?

[doris_day]

No, I don't think that's the problem

[doris_day]

It says in the error box 'A value in the formula is of the wrong data type'

[osknows]

Yes you need to round values to the nearest tick before passing into the function TickDiff

This is what I use

Code: Select all

Public Function ticksRound(odds As Currency) As Currency
Dim roundedMutliple As Currency
Dim valueDiv As Currency
Dim valueNew As Currency
Dim IncrementOdds As Currency

    Select Case odds
        Case Is < 1
            ticksRound = 1.01
            Exit Function
        Case Is < 2
            IncrementOdds = 0.01
        Case Is < 3
            IncrementOdds = 0.02
        Case Is < 4
            IncrementOdds = 0.05
        Case Is < 6
            IncrementOdds = 0.1
        Case Is < 10
            IncrementOdds = 0.2
        Case Is < 20
            IncrementOdds = 0.5
        Case Is < 30
            IncrementOdds = 1
        Case Is < 50
            IncrementOdds = 2
        Case Is < 100
            IncrementOdds = 5
        Case Is < 1000
            IncrementOdds = 10
        Case Else
            ticksRound = 1000
            Exit Function
    End Select
     

    valueDiv = odds / IncrementOdds
    roundedMutliple = Int(valueDiv + 0.5)
    valueNew = roundedMutliple * IncrementOdds
    ticksRound = valueNew
     
End Function

[doris_day]

Thanks. That's done it.