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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### mrshl9898

##### Well-known Member
Could you provide a couple of examples of what values are in the cells and what you want the result to be?

#### Greg92

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

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

##### New Member

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

##### Well-known Member
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

##### New Member

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

##### Well-known 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 next trigger

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

##### Well-known 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))``

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,164,657
Messages
5,838,635
Members
430,558
Latest member
Krampus

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