Profit / Loss calculation spreadsheet

[cmb80]

Hi all, I need some help with one formula (cell S19) - which I have manually typed £54.46 into, but I need to know how to manually calculate this figure.

The figure drives the yellow cells in G27:G39 to be as equal as possible, and also the cells AB3:AB4 to be as close as possible to the positive and negative of each other.

The spreadhseet is used for cross market betting, and at the moment I am manually typing this figure into S18 based on guesswork and adding / decreasing it to suit.

I'm hoping there is a math whizz here who can help.

[ben_hensel]

The hardest part was understanding what's going on in your spreadsheet. Doing it "manually" would be a bit of a monster because you're asking for iteration; well, computers are good at that, though.

Anyway, the way I recommend doing this is getting the MSE of range G27:G39. That's in cell H41. Then you can use a numerical method (such as Solver) to find the value of S18 that will drive H41 towards zero.

I've bound a macro to run that Solver onto a button labeled "solve S18" on the sheet. Make sure you've got solver enabled in both excel and in VBA to run it.

Anyway, see attached.

[cmb80]

Hi ben_hensel - thank you so much. Apologies that the spreadsheet was a bit much to get your head around but your solution works great. I never knew it'd be as complex though.

Thanks again