One for Mathare possibly!

[MattR]

I'm trying to work out a dutching formula for three events. The required profit will be entered on the sheet (let's say A1 for ease) from that I need to work out three stakes based on odds in B1, B2, B3. So in C1,C2,C3 I am trying to get a formula that would work this out. I don't want to have it in a macro, just in excel itself. I can't seem to get anywhere without getting circular references.

[mathare]

Let me have a think about this one, and a bit of a play. I know it's possible I just need to tinker and work out how.

I'll be back with an answer later this evening

[MattR]

Thanks Mat, if I see another excel circular reference window I'll uninstall excel

[mathare]

I've done the easy case - fixed stake with a variable profit. I'm working on the other side of the coin now....

[eastyorkshireracing]

I have got this one sussed out if you get stuck

Just let me know :D

Gordon

[eastyorkshireracing]

Just one pointer, it will require cols A, B, C & D :)

Gordon

[mathare]

Just one pointer, it will require cols A, B, C & D :)

Gordon

My solution uses columns A to F! Methinks I may have over-engineered it slightly

It's quite snazzy though :)

[mathare]

I'm off for me tea in a mo so fee free to jump in Gordon...

[eastyorkshireracing]

OK, Here it is

Just copy and paste into the appropriate cells.....

C1 =IF(B3="","",VALUE(LEFT(B1,FIND("/",B1)-1)/MID(B1,FIND("/",B1)+1,LEN(B1))))
C2 =IF(B3="","",VALUE(LEFT(B2,FIND("/",B2)-1)/MID(B2,FIND("/",B2)+1,LEN(B2))))
C3 =IF(B3="","",VALUE(LEFT(B3,FIND("/",B3)-1)/MID(B3,FIND("/",B3)+1,LEN(B3))))

C5 ="Outlay"
C6 ="Profit"
C7 ="% ROI"

D1 =IF(C3="","",(SUM(C:C)/C1)*(A$1/SUM(C:C)))
D2 =IF(C3="","",(SUM(C:C)/C2)*(A$1/SUM(C:C)))
D3 =IF(C3="","",(SUM(C:C)/C3)*(A$1/SUM(C:C)))

D5 =SUM(D1:D3) ......... [stupid forum that is sum D1 to D3]
D6 =A1-D5
D7 =IF(ISERROR(((A1/D5)-1)*100),0,((A1/D5)-1)*100)


Then put the return you require in cell A1

The three prices go in B1, B2 & B3 (usual 3/1, 4/1 10/1 format (1/1 for evens)).....

A few minor jiggles might make it look more attractive :wink

Gordon

[mathare]

If D6 is your profit you want that to equal A1 though which means D1:D3 must sum to 0.

Doesn't it?

[eastyorkshireracing]

Surely the profit is the required return - the outlay (A1 minus D5).

Gordon

[eastyorkshireracing]

I have misunderstood that part. I was just looking at it as a required return not total profit, oh well back to the drawing board :)

Gordon

[mathare]

I have thought about this over my tea and a few minutes of Top Gear on Dave and concluded that Matt's initial idea will involve circular references and there's no easy way round it. So I have given up

Matt - download the attached and have a play. Just tweak the max stake to get the required profit. I've included the Betdaq odds from the Supreme Novice's Hurdle as an example

[eastyorkshireracing]

It is possible to put a new column E

then:

E1 =(A1/D6)*D1
E2 =(A1/D6)*D2
E3 =(A1/D6)*D3

This would then be your require stakes, assuming that it is not negative equity :)

Bit messy though

Gordon

[MattR]

Thanks for all the hard work guys, about to go to bed so I'll have a proper look through it all tomorrow and see what's what. Thanks again, much appreciated :)

[mathare]

I think I may have solved this in the shower but without a PC or even a piece of paper with me at the time I couldn't be sure my 'solution' didn't still involve circular references. I may try and look at this later

[mathare]

Thank goodness for showers! :D

Use this version and change the dropdown in I1 to Max Stake or Min Profit accordingly.

And not a circular reference in sight!

[eastyorkshireracing]

I think I may have solved this in the shower..... my 'solution' didn't still involve circular references.....

..... are you sure that wasn't "circular motions"

Gordon

[mathare]

All my best ideas seem occur to me in the shower or when I am trying to get to sleep. I solve so many problems at those times

[Win2Win]

All my best ideas seem occur to me in the shower or when I am trying to get to sleep. I solve so many problems at those times

That's what I do.....but obviously not with Mat at the same time

[MattR]

Great stuff Mat :D

[scoobydoo]

Looks like youre going to need another one of those 'Golden shower moments' Mat!

[MattR]

Mat,

Just to let you know it all works perfectly. I pasted it into a new sheet into the place I needed it and just amended any cell references in the fomula as I had to move the corresponding position of the min profit box.
Thanks again

[mathare]

Just to let you know it all works perfectly.

I know :D

Glad it's of use to you Matt