#### Greg92

##### New Member
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
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
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
166.1 KB · Views: 7
• ScreenHunter_192 Feb. 22 21.20.jpg
239.8 KB · Views: 7

#### Greg92

##### New Member
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
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

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
Maybe this will work

#### Greg92

##### New Member

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

Replies
3
Views
179
Replies
2
Views
193
Replies
2
Views
787
Replies
1
Views
838
Replies
8
Views
2K

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.

### Which adblocker are you using?

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

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