"IF" Formula Help

Krisco

New Member
Joined
Sep 26, 2021
Messages
9
Office Version
  1. 365
Platform
  1. MacOS
Hi Everyone,

I am just trying to work out this formula for my Backtesting Spreadsheet. I am wanting to work out the Total % of Wins (Long)[K5] & the Total % of Wins (Short)[L5]. One of the formulas that I have tried using for Total % of Wins (Short)[L5] is: =IF(V8:V50)">0",COUNTIF(B8:B50,"Short")/COUNTA(B8:B50). This formula keeps coming up with error messages. Any help would be appreciated. Thanks

Main - 2 target back - forward - live trading & testing spreadsheet.ods
BCDEFGHIJKLMNOPQRSTUV
4AVG % GainsAVG stop lossBull MarketBear MarketAVG R/RWinning %Total trades No. TradesTotal winsTotal lossesTotal % Wins (Long)Total % Wins (Short)% Sold at TP1% Sold at TP2WinLossWinLossWinLossTotal $ Gain/Loss
5#DIV/0!4%33%33%#DIV/0!1.00%11050%50%70%30%20%80%0%100%0%100%$11,408.70
6TRADE DATAORDER INFOTIMEFRAME STATSPNL STATS
7Long/ShortBull/Bear/Sideways Market On Higher TimeframePAIRDateTimeATR UsedStop Loss PriceStop Loss Distance from Entry in (%)Entry PriceTP 1 (Sold at)Profit $ (TP 1)Profit (TP1) (%)TP 2 (Sold at)Profit $ (TP 2)Profit (TP 2) (%)Position Size (How many Coins/Shares)Timeframe (D, 4hr, 1hr)Win/Loss Per Trade ($)
8LongBull1.540,928.004.38%42,803.0051,731.00$6,249.6014.60%$60,000.00$5,159.1012.05%2282.83D$11,408.70
9Bear$0.00#DIV/0!$0.00#DIV/0!D$0.00
10Sideways$0.00#DIV/0!$0.00#DIV/0!4hr$0.00
11short$0.00#DIV/0!$0.00#DIV/0!4hr$0.00
12$0.00#DIV/0!$0.00#DIV/0!4hr$0.00
13$0.00#DIV/0!$0.00#DIV/0!D$0.00
14$0.00#DIV/0!$0.00#DIV/0!D$0.00
15$0.00#DIV/0!$0.00#DIV/0!D$0.00
16$0.00#DIV/0!$0.00#DIV/0!1hr$0.00
17$0.00#DIV/0!$0.00#DIV/0!$0.00
18$0.00#DIV/0!$0.00#DIV/0!$0.00
19$0.00#DIV/0!$0.00#DIV/0!$0.00
20$0.00#DIV/0!$0.00#DIV/0!$0.00
21$0.00#DIV/0!$0.00#DIV/0!$0.00
22$0.00#DIV/0!$0.00#DIV/0!$0.00
23$0.00#DIV/0!$0.00#DIV/0!$0.00
24$0.00#DIV/0!$0.00#DIV/0!$0.00
25$0.00#DIV/0!$0.00#DIV/0!$0.00
26$0.00#DIV/0!$0.00#DIV/0!$0.00
27$0.00#DIV/0!$0.00#DIV/0!$0.00
28$0.00#DIV/0!$0.00#DIV/0!$0.00
29$0.00#DIV/0!$0.00#DIV/0!$0.00
30$0.00#DIV/0!$0.00#DIV/0!$0.00
31$0.00#DIV/0!$0.00#DIV/0!$0.00
32$0.00#DIV/0!$0.00#DIV/0!$0.00
33$0.00#DIV/0!$0.00#DIV/0!$0.00
34$0.00#DIV/0!$0.00#DIV/0!$0.00
35$0.00#DIV/0!$0.00#DIV/0!$0.00
36$0.00#DIV/0!$0.00#DIV/0!$0.00
37$0.00#DIV/0!$0.00#DIV/0!$0.00
38$0.00#DIV/0!$0.00#DIV/0!$0.00
39$0.00#DIV/0!$0.00#DIV/0!$0.00
40$0.00#DIV/0!$0.00#DIV/0!$0.00
41$0.00#DIV/0!$0.00#DIV/0!$0.00
42$0.00#DIV/0!$0.00#DIV/0!$0.00
43$0.00#DIV/0!$0.00#DIV/0!$0.00
44$0.00#DIV/0!$0.00#DIV/0!$0.00
45$0.00#DIV/0!$0.00#DIV/0!$0.00
46$0.00#DIV/0!$0.00#DIV/0!$0.00
47$0.00#DIV/0!$0.00#DIV/0!$0.00
48$0.00#DIV/0!$0.00#DIV/0!$0.00
49$0.00#DIV/0!$0.00#DIV/0!$0.00
50$0.00#DIV/0!$0.00#DIV/0!$0.00
51$0.00#DIV/0!$0.00#DIV/0!$0.00
52$0.00#DIV/0!$0.00#DIV/0!$0.00
53$0.00#DIV/0!$0.00#DIV/0!$0.00
54$0.00#DIV/0!$0.00#DIV/0!$0.00
55$0.00#DIV/0!$0.00#DIV/0!$0.00
56$0.00#DIV/0!$0.00#DIV/0!$0.00
57$0.00#DIV/0!$0.00#DIV/0!$0.00
Strategy
Cell Formulas
RangeFormula
B5B5=AVERAGE(M8:M1007)+AVERAGE(P8:P1007)
C5C5=AVERAGE(I8:I1007)
D5D5=COUNTIF(C8:C1007,"Bull")/COUNTA(C8:C1007)
E5E5=COUNTIF(C8:C1007,"Bear")/COUNTA(C8:C1007)
F5F5=B5/C5
H5H5=I5+J5
I5I5=COUNTIF(V8:V1007,">0")
J5J5=COUNTIF(V8:V1007,"<0")
K5K5=COUNTIF(B8:B1007,"Long")/COUNTA(B8:B1007)
L5L5=COUNTIF(B8:B1007,"Short")/COUNTA(B8:B1007)
N5N5=100%-M5
P5,R5,T5P5=IFERROR(COUNTIFS($R8:$R1007,P3,$V8:$V1007,">0")/COUNTIF($R8:$R1007,P3),NA())
Q5,S5,U5Q5=1-P5
V5V5=SUM(V8:V1007)
I8I8=(J8-H8)/J8
L8:L57L8=(K8-J8)*M$5
M8:M57M8=((K8-J8)/J8)*M$5
O8:O57O8=(N8-J8)*N$5
P8:P57P8=((N8-J8)/J8)*N$5
Q8Q8=(X5*Y$5)/I8
V8:V57V8=IF(AC8="S",(H8-J8))+L8+O8
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
the first condition for your IF statement is wrong. IF(V8:V50)">0" should be IF(V8:V50>0, *rest of equation here*). The cells below the formula will need to be blank as it's a Spill array formula.

I haven't tested the rest as bebo's solution will likely work too
 
Upvote 0
the first condition for your IF statement is wrong. IF(V8:V50)">0" should be IF(V8:V50>0, *rest of equation here*). The cells below the formula will need to be blank as it's a Spill array formula.

I haven't tested the rest as bebo's solution will likely work too
Thanks for your help also Akira181
 
Upvote 0

Forum statistics

Threads
1,213,583
Messages
6,114,506
Members
448,575
Latest member
hycrow

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