Any Excel Nerds? (1 Viewer)

Generally good on MS Excel but encountered an issue and can’t seem to find a solution.

I’m setting up a spreadsheet to work as a race night bookmaker. Managed to formulate the win pot fairly easily but we’re running an each way pot as well. The win pot can just be calculated as the stake made by the individual (£1) divided by the total amount staked on that horse to win (£5) then multiplied by the total win pot (£10).

So £1/£5 gives a 20% return of the pot, and the person would get £2 back from a pot of £10.

However, the each way element is proving to be a pain in the arse. We’re paying out on 2nd and 3rd so I’d need to calculate the total stakes across both horses (on a dynamic basis) to then divide the each way pot correctly. I don’t quite know how to calculate this based on the specific horses which come 2nd and 3rd without formulating the result at the outset.

Probably a bit confusing so happy to clarify, but anyone got any ideas?

rob9872 said:

More of a calculation issue than an excel one, which is a shame as I like solving excel ones

Anyhow, yours simply needs to be 3 x pots. Put the win part in with the win only pool, divide the each way (place) portion into two equal pots for positions 2 and 3. You don't give 1/4 or 1/5 etc like real racing, just divide that pot in the same ratio you would for the win pot.

Click to expand...

Solved it anyway, spent about 3 hours teaching myself VLOOKUP. Yeah, the plan was to always structure it like you say, just split the place pot evenly but it also depends on the stake that has been bet. Obviously the higher the stake the greater share of the pot you get.

shmmeee said:

Just to annoy you: use INDEX(MATCH()) rather than VLOOKUP, much better.

Click to expand...

Maybe another time, I dared not touch it now! Who knows what damage I could cause.