General Topics
Markets
Technical Topics
Brokerage Firms
Company Specific
Community Lounge
Site Support

# Excel Formula Question

Discussion in 'Trading Software' started by ElectricSavant, Apr 11, 2004.

1. I am multiplying the #of shares times the Entry Price in my spreadsheet and the answer is in Cell J3

F3*G3=J3

My question is......Can I count just the negative numbers? What is the formula? so far the formula in M3 is this:

=if(L3="","",K3-J3) L3 is the exit price, K3 is the amount of money received for the exit and J3 is the amout of money invested in the position.

Michael B.

2. Sorry attachment missed in opening post.

Here it is.

• ###### adx rank list of holdings.xls
File size:
59 KB
Views:
89
3. Excel has Matrix and it can combine with any other excel function like SUM, IF...

4. I am a little confused as to what you are trying to do, but there are several ways to use only negative numbers depending on what you are tryting to do

You can use countif, sumif ...

But I think this is what you want

You can nest a loop into the statement saying
=if(CELL>=0,"",XXXXXXXXXX) where CELL is the cell you want to check for negativity and XXXXXXXXXXX is your formula you want to calculate if negative value exists.

=IF(K3>=0,"",(IF(L3="","",K3-J3)))

I hope this helps.

5. I'm not sure also what he wants, it would be better to give the whole numerical results to be sure. Is Profit/Loss represent profit OR Loss or Cumulative Profit / Cumulative Loss ?

6. I am using some of those functions...

Michael B.

7. I have one column showing a combo profit OR loss in currency. I want to show another cell off to the right just totaling the wins and another cell showing just the losses using the values from that one column. Attached is what I have so far....

Michael B.

• ###### adx rank list of holdings.xls
File size:
56.5 KB
Views:
39
8. Harry, I have seen your work in excel which is most excellent....and thank you for your help. I am not using the column to show cumulative values, but instead one cell off to the right.

See attached. (I corrected it to read "total" realized profit)

Thanks again, I am getting there slowly but surely.

Michael B.

• ###### adx rank list of holdings.xls
File size:
56.5 KB
Views:
39
9. But I don't see any matrix in your spreadsheet: normally a matrix is within {formula} and you can only create them by special combination of keys Ctrl+Shift+Enter after entering the formula WITHOUT {}.

10. Does execute2 suggestion not enough ? What function is missing ? Or write what you want in pseudo-code and It would be possible to really tell you precisely how it translates in Excel (sorry I'm a bit slow also because I still don't understand what you need exactly )

http://www.exceltip.com/excel_tips/Summing/104.html
Summing Tips - All Tips (17 tips)

Totaling Rounding numbers in Microsoft Excel
Summing values in a vertical range in Microsoft Excel
Summing values in vertical and horizontal ranges in Microsoft Excel
Summing data by selecting cells in Microsoft Excel
Sum subtotals in Microsoft Excel
Extending the range of sums for the SUM formula in Microsoft Excel
AutoSum, additional functions in Microsoft Excel
Summing data in the intersection of two ranges in Microsoft Excel
Summing data from a cell in a number of sheets in Microsoft Excel
Sumif - using comparison operators (< >) as criteria in Microsoft Excel
Using SUMIF to sum two ranges according to criteria in Microsoft Excel
Summing totals with text characters as criteria in Microsoft Excel
Summing filtered data in Microsoft Excel
Summarize data according to criteria from the data table using Sumif formula, Offset formula and Combo Box in Microsoft Excel
Summing a range containing errors and values in Microsoft Excel
Summing using multiple criteria in Microsoft Excel
Sum the largest two numbers in range in Microsoft Excel

#10     Apr 12, 2004
ET IS FREE FOR TRADERS BECAUSE OF THE FINANCIAL SUPPORT FROM THESE SPONSORS: