Easiest way to establish which is the favorite?

[1clutch1]

Hi,

I'm trying to create a Dashboard spreadsheet where I can edit the bet commands from.

I want all the bet commands to be the same to the multiple worksheets in the book.
When I load in 20 markets sometimes the favourites are not in selection one. Top row in the spreadsheet.

I'm not sure how to make my bet command go into the right cell if the price is less than 2.0
If I write an If statement it could fire in bets in the wrong command cell when the match goes in play.
I'm only doing tennis so there's only two runners.
What's the best way to do this?

Thanks

[ShaunWhite]

Create a cell for the market that identifies the favourite and include that in your logic.
Complex problems are always solved one small step at a time. Think about how you would do it manually, break it down into the smallest possible actions and replicate each step. Don't try and do it all in one mega statement.

[1clutch1]

The problem is if I use a the price on either runner is an anchor to establish a fave. My fear is that when the match goes in play the odds flip I'll be posting to the wrong command cell.
With tennis times being inconsistent its hard to use a cell to reference

[ShaunWhite]

The problem is if I use a the price on either runner is an anchor to establish a fave. My fear is that when the match goes in play the odds flip I'll be posting to the wrong command cell.
With tennis times being inconsistent its hard to use a cell to reference

The 'Fav Y/N' cell will obviously need to be set against the selection with the smallest odds. It would be pointless if it was just a hard-coded marker.

[nigelk]

I posted something along these lines to deal with a similar problem another forum member had. Just changed slightly.

Sheet 2 B4 returns the minimum price of all the runners, C11 (and this is the important bit), shows what row number the fav is currently on, and D4, the name.

Looking at the BA sheet, there is a formula in each cell that looks at the runner name, and it it matches the one on sheet 2, puts a back command in.

change any of the odds in col H and the back command will change to suit the current fav

Obviously you'll need to add other criteria to suit, such as min odds.

Cheers,Nigel.

Sample.xlsx

[1clutch1]

I got this to work on multiple sheets using a macro and a bit of VBA. Cheers for the sheet sample, I used some of the formulas which helped a lot

[Russell]

Can I offer an alternative way to solve this?

It is possible to identify the favourite in just one cell using the following formula...

=INDEX(B9:B17,MATCH(SMALL((G9,G11,G13,G15,G17),1),G9:G17,0))

The range can be adjusted to suit. In this example I am assuming that the favourite is likely to be in the first 5 runners.

The original question was looking at posting a bet if the odds were less than 2.0, in which case an "IF AND" formula would work. For instance, let's say the Favourite calculation above was entered into cell J3 and you were interested in the runner in cell B11. The formula would be...

=IF(AND($J$3=B11,G11<2),"Back","")

This could then be copied into the other cells.

Russell

[ShaunWhite]

Can I offer an alternative way to solve this?
=INDEX(B9:B17,MATCH(SMALL((G9,G11,G13,G15,G17),1),G9:G17,0))

ooooh that's slick Russell, I do love seeing code resolved down to a one-liner. But that comes from learning to code in 1k of memory.
hehe it reminds me that I once wrote a whole game of Breakout in under 900bytes ....but that was in 1981 so nothing fancy.

[mjmorris335]

Can I offer an alternative way to solve this?
=INDEX(B9:B17,MATCH(SMALL((G9,G11,G13,G15,G17),1),G9:G17,0))

ooooh that's slick Russell

My old computing lecturer would've described that as elegant. Very nice.

Mike

[Russell]

Thanks guys