Automatic Order Sorting in Excel

[mickyxx1]

Hi there, I was wondering if anyone know or can help me in Order sorting the market data when you transfer it to EXCELL. I know there is a SortExample BA file provided in the BA guide but it doesn't Automatically sort in order of current BACK or LAY price as it changes before or after the OFF. Basically, in the same way the BA or betfair sort the order of the market. Thanks for your help everyone. Pls ask me if the above is not clear.

[Ian]

Why would you want to do that ? It would be very difficult to control your formulas etc. if the order in the worksheet kept changing on every refresh.

[mickyxx1]

I am only focusing on the top 3 favs. of the market. And my formula is to trigger a bet on condition of their price range qualifies. Much like DUTCHING OR CONDITION bet but dependent on each others price.

[alrodopial]

In a column (e.g AA) rank them based on their odds and then in another column place your formulas based on these values ( the rank )

=IF(OR("AA5=1,AA5=2,AA5=3),.....,.....)
=IF(OR("AA6=1,AA6=2,AA6=3),.....,.....)

etc

[MarkRussell]

Hi,

I prefer to use the following formula for ranking the odds as it ignores any non-runners i.e. when the odds are 0

So to rank column F for example use:-

Code: Select all

=IF(F5=0, "", SUMPRODUCT((F5>$F$5:$F$55) * ($F$5:$F$55<>0)) +1+COUNTIF($F$5:F5,F5)-1)

Regards,
Mark

[mickyxx1]

Thanks everyone i will try your formula soon,

[excelbet]

Hi everyone,
I'm using Gary's example for ranking fav.
I would like the trigger col "Q" to mirror the sort order of col "AA" as I don't want to disturb the trigger criteria for each of the 3 favs. In other words if the 3rd fav has moved in order of ranking in AA9 then Q9 should reflect the 3rd fav criteria. At present if the 3rd fav changes from AA9 to AA5 the trigger criteria stays in Q9.

Any help would be much appreciated.

eb

[osknows]

For example, say your Q5:Q9 formula will trigger a BACK bet when condition 1, 2 & 3 is met. The current formula in Q5 would be something like

[Q5] = IF(AND(condition1 = TRUE, condition2 = TRUE, condition3 = TRUE),"BACK","")

If you want to introduce the ranking into this formula so that only the top 3 favs are included, the formula would be

[Q5] = IF(AND(condition1 = TRUE, condition2 = TRUE, condition3 = TRUE,AA5<=3),"BACK","")

[osknows]

Incase I've missed the point of your question... if you're trying to lookup information on another table based on positions then you may need VLOOKUP(position, range to look up, column, match)

You may need to elaborate what your attempting to achieve

[excelbet]

Tried your suggestion but still can't achieve results. Let me explain.
Betfair changes the order of runners in-play in col 'Q' and this plays havoc with criteria for each runner and in particular the top 3 favs. Before the race I would set the conditions for each runner in col 'Q' and I'm happy with the unique rank col as it identifies the order of the runners according to their odds. However, as the odds change the rank order also changes but the conditions in col 'Q' remain static and are totally misaligned with rank order in col 'AA'. Is there a formula in excel (or VBA) to fix this so that the cells in col 'Q' relfect the movement of rank order in col 'AA' ?

Any help would be appreciated.

[excelbet]

Any solution from anyone who has encountered this situation ?

eb

[osknows]

Hi Eb,

Usually you wouldn't want to hard code any values if they rely on odds being in a certain order. One way is

Current rank order is in column AA - (contains the RANK formula and changes with current horse odds)
Fixed rank order is in Column AB - (Eg AB5:AB55 are numbered 1 - 51 fixed)
Move the criteria in column Q to AC so that the order lines up with the fixed rank in column AB

Then in column Q use the formula =vlookup(AA5,AB5:AC55,2,false)

which will lookup the value in AC where both the current rank and fixed rank match.

If you're still stuck PM me the sheet and I'll have a look for you

Os

[flogger]

I would be interested for a fix on this one hopefully.

[excelbet]

Thanks Osknows for your suggeestion. I will try it later today.

eb

[excelbet]

Tried your solution Osknows. It is perfect for my purpose. I can now 'back' or 'lay' any of the top 3 favs and my criteria will track each runner in any rank order they appear.
However, a new challenge has now emerged.
If a bet on Fav 1 is triggered, a Bet Ref No. appears but the 'Q' cell is open for multiple further bets to be triggered. Let's say that Fav 1 is in cell Q7 then the one of the rules for that Fav should be T16 =0 for it to work properly. But then if the rank order changes then a different option applies. It seems that for each permutation the Fav1 rule might be T14=0;T15=0;T16=0 but this can't be as there would be a conflict. Despite numerousl attempts to play around with multiple 'IF' statements I cannot find a solution.
Any further help would be much appreciated.
eb