I am trying to get the correct daily profit factor formula to work. The problem I am running into is when there is just one trade and it is positive. I attached the file.
I am trying to get the correct daily profit factor formula to work. The problem I am running into is when there is just one trade and it is positive. I attached the file.
It appears that your daily profit factor formula is basically the ratio of the positive trades to the negative trades. dpf=sum(positive trades)/sum(negative trades). For a day with a single trade, either sum(positive) or sum(negative) will be 0 (and for other days when there are multiple trades but all of one type such as march 27 where there are two trades and both are positive).
As near as I can tell, the current calculation is correct, and this behavior (sometimes returning 0 or #Div/0) is simply a feature of this definition of daily profit factor. A quick internet search did not find anybody who talked about an alternate definition for profit factor when either gains or losses are 0. "Fixing" this probably needs to start by explaining to us how you want to define profit factor when either sum is 0. Once we know how you want to calculate profit factor when either gains or losses is 0, we should be able to help you program that calculation into the spreadsheet.
Originally Posted by shg
Profit factor is the sum of all the profitable trades / negative trades.
The week from 4/1 - 4/5, the profitable trade total was $30,437
The week from 4/1 - 4/5, the negative trade total was $5,465
This gives the weekly profit factor 30,437 / 5,465 = 5.57
I would like to show 0.00 if there was one trade with negative profit and show 100.00 if there was one trade with positive profit.
What errors is the IFERROR() function intended to trap? If the #Div/0 error is the only error that you ever expect, you could simply change your current IFERROR() to =IFERROR(calculation,100).
That will not work because if there is a day where there are no trades, it still puts 100 as the Profit Factor
It would appear, then, that you need to add an IF() test to see if a given day has no trades. =IF(COUNTIFS(Q:Q,V3)=0,"no trades",IFERROR(...))
Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks