Please help before the Wife kills me

[Greg92]

A month of trying but I can't solve this so please help me!!! I have a sheet for sports trading, within this sheet I have prices for the first 3 selections in the market. The prices have been converted from fractional odds to decimal odds and then into a numbers which run 1 to 350 . From this I've setup up 3 moving averages and volume for each selection . I have a countdown clock which runs from 780 seconds out to start of event. Each second updates on new row so each column with Price , MA1,MA2,MA3,Volume dynamically updates ever second for each selection. So A2 Price B2 MA1,C2 MA2,D2 MA3, E2 VOLUME, F2 Signal MA1/2 CROSSOVER G2 MA2/3CROSSOVER H2 Volume signal. I use IF statements for the signals =IF(B2>C2),1,0 and the same for other triggers. Now the problem!! The only time I want to place a trade is if these triggers happen in order so THE MA1 crosses MA2 Trigger, MA2 crosses MA3 trigger . VOLUME trigger so I can't just SUM column as I'm getting 1,0 all the way down but it only works if they are in the order above so can't take any signals that happen before signal 1 . Hope this makes sense, just a note MA = moving average.
Many thanks for reading and hopefully helping me Greg

 

[mrshl9898]

Could you provide a couple of examples of what values are in the cells and what you want the result to be?

 

[Greg92]

Could you provide a couple of examples of what values are in the cells and what you want the result to be?

Hi mrshl9898,
Yes I'll take some screenshot and post them soon , Thank you so much for getting back to me ?

 

[Greg92]

Hi ,
In the screenshot you can see IW is countdown clock, this one is in 5second intervals, IX are the decimal odds, IY are the converted odds ,IZ MA1, JA MA2 , JB Signal from first MA ,JC Signal from second MA , JD Volume, signal from volume over £700
What I'd like to happen is sheet monitors for the signal 1 from first MA in column JB, then when that arrives the sheet stops monitoring JB and moves to monitor JC but only from the row downwards where the first signal in JB was . Then when that signal arrives move to monitor JE but this time only from the row the second signal was produced downwards and when that signal arrives put a 1 in the JF column .

 

[Greg92]

Could you provide a couple of examples of what values are in the cells and what you want the result to be?

Hi , I've uploaded a screenshot and hopefully an explanation ?

 

[mrshl9898]

Thanks for that. Appreciate that I have no idea of the context here, and am not sure what is or isn't working.

Which columns do you need formulas or solutions for?

And can you manually enter what results you expect to see.

From there I hope to be able to use that data available to derive the result.


Cheers

 

[Greg92]

Thanks for that. Appreciate that I have no idea of the context here, and am not sure what is or isn't working.

Which columns do you need formulas or solutions for?

And can you manually enter what results you expect to see.

From there I hope to be able to use that data available to derive the result.


Cheers

Ok I will do , but I'm afraid it will be tomorrow now as unable to use the desktop till then as my wife's asleep!! ?

 

[offthelip]

I think you can solve your problem by using match and offset. I have assumed (to make it easy for me) that MA1 is in col A , MA2 is in col B and mA3 in Col C.
I then change trigger logic so that you only get a 1 at the point where the crossover occurs, so I have these equations in columns D and E. Note I have assume the data starts at row 3
D3 is:

=IF(AND(A3>B3,A2<=B2),1,0)

E3 is

=IF(AND(B3>C3,B2<=C2),1,0)

Then to detect the row number of the first cross over I have this in column F:

=MATCH(1,D$3:D3,0)

Then to detect thte second cross over I use the number in column F to determine where to start the match for the next cross over, So in coluimn G I have :

=MATCH(1,OFFSET(E$3,F3,0,ROW()-F3-2),0)

This gives you an error until you have got both triggers in sequence . obviously you can use iferror to get a sensible response, You can also reuse this for the next trigger

 

[Greg92]

I think you can solve your problem by using match and offset. I have assumed (to make it easy for me) that MA1 is in col A , MA2 is in col B and mA3 in Col C.
I then change trigger logic so that you only get a 1 at the point where the crossover occurs, so I have these equations in columns D and E. Note I have assume the data starts at row 3
D3 is:

=IF(AND(A3>B3,A2<=B2),1,0)

E3 is

=IF(AND(B3>C3,B2<=C2),1,0)

Then to detect the row number of the first cross over I have this in column F:

=MATCH(1,D$3:D3,0)

Then to detect thte second cross over I use the number in column F to determine where to start the match for the next cross over, So in coluimn G I have :

=MATCH(1,OFFSET(E$3,F3,0,ROW()-F3-2),0)

This gives you an error until you have got both triggers in sequence . obviously you can use iferror to get a sensible response, You can also reuse this for the next trigger

Morning Offthelip,
Just one thing is the signal comes when
B3-A3>=2 as this signals a sharpish move, can I just change the first equation to that ? Cheers Greg

 

[offthelip]

Yes of course , but I suggest you add in the check that it was below 2 on the previous iteration so that you continue to just get a 1 at the trigger point i.e.

=IF(AND(B3-A3>=2,B2-A2<2),1,0))