Please help before the Wife kills me

Greg92

New Member
Joined
Feb 3, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 3, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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 😁
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Greg92

New Member
Joined
Feb 3, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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 ??
 

Attachments

  • ScreenHunter_189 Feb. 22 21.14.jpg
    ScreenHunter_189 Feb. 22 21.14.jpg
    166.1 KB · Views: 7
  • ScreenHunter_192 Feb. 22 21.20.jpg
    ScreenHunter_192 Feb. 22 21.20.jpg
    239.8 KB · Views: 7

Greg92

New Member
Joined
Feb 3, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 3, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=IF(AND(B3-A3>=2,B2-A2<2),1,0))
 

Greg92

New Member
Joined
Feb 3, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
Excel Formula:
=IF(AND(B3-A3>=2,B2-A2<2),1,0))
Hi ,
Hopefully this should be the other 2 screenshots
 

Greg92

New Member
Joined
Feb 3, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:
Excel Formula:
=IF(AND(A3>B3,A2<=B2),1,0)
E3 is
Excel Formula:
=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:
Excel Formula:
=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 :
Excel Formula:
=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
 
Last edited:

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,143
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Dec 23, 2017
Messages
2,143
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Feb 3, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,147,635
Messages
5,742,248
Members
423,717
Latest member
rubthenut

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top