Very VERY simple.... yet oh so hard.

[Bengal Daddy]

I wonder if this is going to be one
of those head slapping moments, or
whether theres a decent VBA type
solution.

what I am trying to do is very very
simple.
All I want to do is place a BACK bet
1 tick below the CURRENT LAY price.
This is in-running, on any
selection, and I might change my
mind, so no, I can't just click on
the relavant box.

I intend to make a load of buttons
for each selection. Now, if all
ticks were the same increments,
there would be no problem. However,
they differ with odds size (which creates its own problems), and to
handle this, I have attempted the
following argument in the excel cell:-

=if (h9<=2,h9-0.01,if(h9<=3,h9-0.02, if(h9<=4,h9-0.05, if(h9<=6,h9-0.1, if(h9<=10,h9-0.2, if(h9<=20,h9-0.5, if(h9<=30,h9-1, if(h9<=50,h9-2, if(h9<=100,h9-5, if(h9<=1000,h9-10))))))))))

or in VBA :-

If h9 <= 2 Then
m9 = h9 - 0.01
ElseIf h9 <= 3 Then
m9 = h9 - 0.02
ElseIf h9 <= 4 Then
m9 = h9 - 0.05
ElseIf h9 <= 6 Then
m9 = h9 - 0.1
ElseIf h9 <= 10 Then
m9 = h9 - 0.2
ElseIf h9 <= 20 Then
m9 = h9 - 0.5
ElseIf h9 <= 30 Then
m9 = h9 - 1
ElseIf h9 <= 50 Then
m9 = h9 - 2
ElseIf h9 <= 100 Then
m9 = h9 - 5
Else
m9 = h9 - 10
End If

However, it's having none of it.
Is there an easier way of going about this?

You see the OFFSET command has a way of working it out obviously, but I want to BACK only, at the place it would LAY............and without the original BACK bet....if you catch my drift.

Any input would be greatly appreciated,


Thanks,
BD

[Nero Tulip]

I use vlookups for this. Start by creating a two column table of every odds increment and put numbers next to them. eg:

1, 1000
2, 990
3, 980
4, 970

And so on. Then use match/vlookup (can't remember which) to find the current odds' number. Your 'play' price is then that number +1 on the table. So, market is 990 - number for this is 2, +1 = 3, using vlookup 3 = 980.

[Bengal Daddy]

Thanks cheif,

I'll look into vlookups now!

[nigelk]

You could also use a combination of match and offset to get the desired price.

On the attached on sheet 5, you'll see prices from 1.01 to 200 ( don't think I missed any?).

On the BetAngel sheet in I4 the formula:

=OFFSET(Sheet5!A1,MATCH(H9,Sheet5!A2:A271,0)+1,0)

gets the current lay price (2.8) and returns the value of the cell below (2.78). If you change the +1 to a +2, it'll get the value 2 cells below, and so on.

Similarly, of you need any values above the current price, it would be a -1, -2 etc.

[Bengal Daddy]

Nero / Nigel,

Thanks for your input guys!!

You've put me on the road to excel mastery!!

Getting there, slowly but surely.
My current spreadsheet is beginning to look like a 17 yr old chav's modded nova gone wrong!