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

 

[Greg92]

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))

Brilliant!!! Thank you so much , I'll be home by 1pm so will be able to check it out , I'm like a kid at Christmas ?

 

[Greg92]

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))

Evening Offthelip,
Had another go tonight and I've nearly got it sorted thanks to you ?????
But it seems to be giving the wrong ROW Ref but coming up with the right amount of signal it's picked up as in 2 ??

 

[Greg92]

Evening Offthelip,
Had another go tonight and I've nearly got it sorted thanks to you ?????
But it seems to be giving the wrong ROW Ref but coming up with the right amount of signal it's picked up as in 2 ??

 

[Greg92]

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))

 

[Greg92]

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))

Hi ,
Hopefully this should be the other 2 screenshots

 

[Greg92]

Maybe this will work

 

[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 nex

 

[offthelip]

Remember that match find the number of the element inthe range you have specified, the range you chosen start at row 6, so row 6 = 1 returned by match and so add 15 and you get row 21 is returned as 16. Also if you are trying to combine one triggger after the first one, the number which is returned is also from the start of the range specified in the match, which is in turn speficied by the offset from the first trigger, so the ron numnbver will be the sum of these two numbers.
Looking at you screen shots in post 12 I can't see what you are trying to do with equations in column JG. which two triggers are you trying to combine?? and what order

 

[offthelip]

Don't bother trying to load more screen shots the RAR don't seem to work, you have single trigger in columns Jb, JC and JD, , are you trying to trigger those three in that order??

 

[Greg92]

Hi Offthelip
IVE Had To WinRAR them as they were to big?

Don't bother trying to load more screen shots the RAR don't seem to work, you have single trigger in columns Jb, JC and JD, , are you trying to trigger those three in that order??

Yes thats correct