I have problems with conditions

[Greenlee]

I think its quite complicated so I would need to show you but i will try

So this is my table (sheet name: BACKTESTING)
Gyazo - 18e6618f6f26ba50b4937012cb11de5a.png

This is my second sheet (sheet name BACKTESTING RESULTS)

Gyazo - 8a07ad8210ee284c294add4d27a7adb5.png

So what do I want to do:

so this is the formula for now:
=COUNTIFS(BACKTESTING!D3:D7;"=BAT";BACKTESTING!B3:B7;"=EUR/AUD";BACKTESTING!C3:C7;"=M15";BACKTESTING!K3:K7;"=1") from backtesting page but I want to add 2 more conditions from 2nd sheet
1st when you choose TF=timeframe M15 that would show results for M15 not for M5,H1 or H4 and second when I would choose on the right side EUR/AUD it would show EUR/AUD not also EUR/JPY ( dropdown of currencies) ...

If I was not understandable enough please let me know

 

[Greenlee]

Yes I understand I can swap for M5 but then the condition M15 disappear so lets say I want to keep both conditions when I pick M15 I would have results for M15 and when I pick M5 I would have results for M5 .)

 

[Chris Mack]

This?

=IF(F2<>"EUR/AUD","",COUNTIFS(BACKTESTING!D3:D7;"=BAT";BACKTESTING!B3:B7;F2;BACKTESTING!C3:C7;E3;BACKTESTING!K3:K7;"=1"))

 

[Greenlee]

A kind of this not completely because I want AND condition what do I mean with AND condition

=IF(OR(E3<>"M15";F2<>"EUR/AUD");"";COUNTIFS(BACKTESTING!D3:D10000;"=BAT";BACKTESTING!B3:B10000;F2;BACKTESTING!C3:C10000;E3))</pre>

When I use this code it shows me results for TF: 15Minutes and pair EUR/AUD btw how do I write a code when I change TF lets say I pick TF: 1H, will show me in this square the result for TF:1H?

I hope I was clear if not please let me know

 

[Chris Mack]

Well, the "E3" part of the code should take care of that.

The Timeframe is in cell E3, no?

 

[Greenlee]

Yes its true I pick the time frame in E3, but when I change it to TF:M5 in D2 there is no results ( only shows when i pick M15) so I need to add some code to show me results for M5 to but I don't know how to

In square D2 there is this code for M15 but I want to show me results for M5 when I pick TF: M5 in E3
Gyazo - f16fb663d020ecd3765bb27df4384ea5.png

=IF(OR(E3<>"M15";F2<>"EUR/AUD");"";COUNTIFS(BACKTESTING!D3:D10000;"=BAT";BACKTESTING!B3:B10000;F2;BACKTESTING!C3:C10000;E3))

 

[Chris Mack]

Use the formula in post #12.

 

[Greenlee]

Oh I manage to solve it now and the last thing I want to add the same thing we made now with time frame I want to do with Currency so that means when I change currency that shows me different results?

Thank you very much Chris u solved me many problems really appreciate this

 

[Chris Mack]

For that you can just remove the IF test completely:

=COUNTIFS(BACKTESTING!D3:D7;"=BAT";BACKTESTING!B3:B7;F2;BACKTESTING!C3:C7;E3;BACKTESTING!K3:K7;"=1")

Chris.

 

[Greenlee]

Much appreciate you are solving all my problems

I have 2 more questions

1st. How would I calculate grand total

Gyazo - 7fc06747184d98aa516350701d1b057b.png ( the picture in down right corner)

Total trades ( from all patterns ( BAT,CYPHER, DOUBLETOP.....) that I would pick only time frame and currency?

2nd question

I would like to calculate AVG WIN pips for each pattern (BAT... )

How would I like to be calculated? This is the condition

=COUNTIFS(BACKTESTING!D3:D10000;"=BAT";BACKTESTING!B3:B10000;F2;BACKTESTING!C3:C10000;E3;BACKTESTING!K3:K10000;"=1")

if this is true then it needs to sum from BACKTESTING!N3:N10000 and BACKTESTING!P3:P10000 and divide by wins of pattern D3(on sheet BACKTESTING RESULTS) ( Gyazo - 3c2a76380ebfe1d8887a0a23d58d7d83.png

Thank you

 

[Greenlee]

I manage to solve first problem I just need help with a second problem thanks in advance