#### 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
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??
Right so i got you about why Match is returning 21 , And yes i need the three triggers to be picked up in that order and then cause one trade to be placed . If they don`t happen in that order then no trade

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### offthelip

##### Well-known Member
Try this I have assumed you want the three trigger all in order, I have used column B,C and D as the column ( save me moving across to JB , Jc jd, ( thats is HUGE worksheet)
in col e:
=MATCH(1,B6:B\$6,0)
in col F
=MATCH(1,OFFSET(C\$6,E6,0,ROW()-E6-5),0)+E6
in col G
=MATCH(1,OFFSET(D\$6,F6,0,ROW()-F6-5),0)+F6

#### Greg92

##### New Member
Try this I have assumed you want the three trigger all in order, I have used column B,C and D as the column ( save me moving across to JB , Jc jd, ( thats is HUGE worksheet)
in col e:
=MATCH(1,B6:B\$6,0)
in col F
=MATCH(1,OFFSET(C\$6,E6,0,ROW()-E6-5),0)+E6
in col G
=MATCH(1,OFFSET(D\$6,F6,0,ROW()-F6-5),0)+F6
Great , thanks again for your continued patience with me I'm very new to excel . I'll let you know how I get on in the morning

#### Greg92

##### New Member
Great , thanks again for your continued patience with me I'm very new to excel . I'll let you know how I get on in the morning
Morning Offthelip,
I'm afraid that i get this error when connecting to a live market And the Error #N/A All the way done the column now

#### Attachments

• ScreenHunter_193 Feb. 23 10.53.jpg
11.3 KB · Views: 4
• ScreenHunter_195 Feb. 23 11.07.jpg
47.7 KB · Views: 4
• ScreenHunter_196 Feb. 23 11.07.jpg
24.3 KB · Views: 5

#### offthelip

##### Well-known Member

the #N/A is correct, I did say at the start that you can get rid of that by using iferror, but the error is helpful to see what is happening in development. this is what my spreadsheet looks like and it does exactly what you asked for:
the VBA error may possibly be caused by the errors on the worksheet, I can't tell because the VBA is nothing to do with this thread

#### Attachments

• 3triggers.JPG
112.7 KB · Views: 5

#### offthelip

##### Well-known Member
Can I just suggest that if you are running VBA anyway, it could be much faster and easier to do the triggers and the sequencing in VBA. ( that is what I do for automatic trading systems)

#### Greg92

##### New Member

Can I just suggest that if you are running VBA anyway, it could be much faster and easier to do the triggers and the sequencing in VBA. ( that is what I do for automatic trading systems)
I Have got it to do as you show in yours now , I'd forgotten to put the J before the first B, silly me !!!! But the run error still comes up. As for VBA I agree , but I know nothing about VBA I only opened Excel for the first time six week ago , the only other time I've got involved in programme is on my ZX81

#### Greg92

##### New Member
I Have got it to do as you show in yours now , I'd forgotten to put the J before the first B, silly me !!!! But the run error still comes up. As for VBA I agree , but I know nothing about VBA I only opened Excel for the first time six week ago , the only other time I've got involved in programme is on my ZX81
I've marked this threat as solved as you've done what many others couldn't and gave me the solution, could you help me please if I start a VBA thread or have I done your head in ?

#### offthelip

##### Well-known Member
Glad to be of help, I will certainly look at any new thread but obviously I can't promise anything. I am pleased that you are at least still alive and the wife isn't in jail!!

#### Greg92

##### New Member
Glad to be of help, I will certainly look at any new thread but obviously I can't promise anything. I am pleased that you are at least still alive and the wife isn't in jail!!
Still not sure why the MATCH and INDEX got a run error?

Replies
3
Views
211
Replies
2
Views
208
Replies
2
Views
860
Replies
1
Views
857
Replies
8
Views
2K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,042
Messages
5,767,793
Members
425,435
Latest member
cmardaz

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