Don't Know Which Excel Spreadsheet Formula to Use for This

[sp736st88z]

Hello,


I created an Excel spreadsheet to log and calculate specific metrics for my commodity day trading, to log notes and so on. But I'm stuck on one calculation.

I have a calculation to add up the profits for each winning commodity market trade that I make and to subtract that from the losses that I have on my other trades. That works fine.

What I'm not able to figure out how to do, is to calculate my number of winning trades and my number of losing trades.

In other words, in my spreadsheet I have 30 'profit' cells and 30 'loss' cells for up to 30 trades that I might take in any given day.

Whenever I input a dollar amount in either one of my 30 'profit' cells or in one of my 30 'loss' cells, I want Excel to count that as one win or one loss, depending on whether it was put in a 'profit' or 'loss' cell. Then I want it to count all my wins and all my losses (but not to add them together).

So I might input 20 wins that average $20 each into my spreadsheet. Then I might input 10 losses that average $14 each. I want for Excel to ignore the dollar amount in each cell and only count each cell entry as one loss or one win.

Then I want it to add up (count) all my wins and all my losses and to give me the total number of wins that I had and the total number of losses that I had.

Is there a formula or some way I can do that?

 

[ttray33y]

Hello,


I created an Excel spreadsheet to log and calculate specific metrics for my commodity day trading, to log notes and so on. But I'm stuck on one calculation.

I have a calculation to add up the profits for each winning commodity market trade that I make and to subtract that from the losses that I have on my other trades. That works fine.

What I'm not able to figure out how to do, is to calculate my number of winning trades and my number of losing trades.

In other words, in my spreadsheet I have 30 'profit' cells and 30 'loss' cells for up to 30 trades that I might take in any given day.

Whenever I input a dollar amount in either one of my 30 'profit' cells or in one of my 30 'loss' cells, I want Excel to count that as one win or one loss, depending on whether it was put in a 'profit' or 'loss' cell. Then I want it to count all my wins and all my losses (but not to add them together).

So I might input 20 wins that average $20 each into my spreadsheet. Then I might input 10 losses that average $14 each. I want for Excel to ignore the dollar amount in each cell and only count each cell entry as one loss or one win.

Then I want it to add up (count) all my wins and all my losses and to give me the total number of wins that I had and the total number of losses that I had.

Is there a formula or some way I can do that?

in which columns/rows loss and win being entered?

 

[sp736st88z]

Hi ttray33y,


Thanks for your reply to my post.


My 'Number of Wins' cell is: K5
My 'Number of Losses' cell is: K6
My 'Net Wins/losses' cell is: P6


My Total Profit cell contents are: =C18+C39+C60+C81+C102+C123+C144+C165+C186+C207+C228+C249+C270+C291+C312+C333+C354+C375+C396+C417+C438+C459+C480+C501+C522+C543+C564+C585+C606+C627


My Total Loss cell contents are: =E18+E39+E60+E81+E102+E123+E144+E165+E186+E207+E228+E249+E270+E291+E312+E333+E354+E375+E396+E417+E438+E459+E480+E501+E522+E543+E564+E585+E606+E627

 

[wideboydixon]

I think this might work for you:

K5=SUMPRODUCT((MOD(ROW($C$18:$C$627)+3,21)=0)*($C$18:$C$627>0))
K6=SUMPRODUCT((MOD(ROW($E$18:$E$627)+3,21)=0)*($E$18:$E$627>0))

WBD

 

[sp736st88z]

Hi wideboydixon,

Thanks for taking your time to work through this. I do appreciate it.

I must be doing it wrong. I entered the entire K5 line formula you spelled out and put it into cell K5. Then I entered the entire K6 line formula you spelled out and put it into cell K6.

But I get a dollar value returned in cell K5 when I enter dollar values in the 'Profit' cells. And I get a dollar value returned also in cell K6 when I enter dollar values in the 'Loss' cells.

Am I putting the formula in the wrong place or something.

I tried to attach a screenshot, but I see that I can only do so if I can upload it to a server.

I input the following into the spreadsheet:


C18 $50
C39 $20
E60 $15


For my Number of Wins cell it returned $70 (it should have returned 2).


For my Number of Losses cell it returned $15 (it should have returned 1).

 

[wideboydixon]

I entered those values in to those cells and got this:

Book1
	K
5	2
6	1
Sheet1

Cell Formulas
Range	Formula
K5	=SUMPRODUCT((MOD(ROW($C$18:$C$627)+3,21)=0)*($C$18:$C$627 > 0))
K6	=SUMPRODUCT((MOD(ROW($E$18:$E$627)+3,21)=0)*($E$18:$E$627 > 0))

Perhaps you missed the " > 0" at the end?

WBD

 

[sp736st88z]

Thanks so much wideboydixon,

It worked. I must've missed a character or something when I copied the formulas.

You sure do know your formulas and spreadsheets. It is a big help. I never would've figured that out.

I appreciate your help.

Thanks again,

 

[wideboydixon]

Thanks so much wideboydixon,

It worked. I must've missed a character or something when I copied the formulas.

You sure do know your formulas and spreadsheets. It is a big help. I never would've figured that out.

I appreciate your help.

Thanks again,

You're welcome

WBD