Please help before the Wife kills me

Greg92

New Member
Joined
Feb 3, 2021
Messages
25
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,708
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
Joined
Feb 3, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 3, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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 1613942128808_ScreenHunter_183 Feb. 21 20.32.jpg.
 

Greg92

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

ADVERTISEMENT

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
Joined
Feb 6, 2012
Messages
1,708
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
Joined
Feb 3, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 23, 2017
Messages
1,728
Office Version
  1. 2010
Platform
  1. Windows
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
 
Solution

Greg92

New Member
Joined
Feb 3, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 23, 2017
Messages
1,728
Office Version
  1. 2010
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,199
Messages
5,623,333
Members
415,968
Latest member
Chabal74

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