Excel Formula Question

ElectricSavant

ElectricSavant

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.

Below is the spreadsheet.


Michael B.
 
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.
 
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 ?

Quote from execute2:

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.
More...
 
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.

Quote from execute2:

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.
More...
 

Attachments

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.


Quote from harrytrader:

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 ?
More...
 

Attachments

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 {}.


Quote from ElectricSavant:

I am using some of those functions...

Michael B.
More...
 
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 :D)

See also
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

Quote from ElectricSavant:

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.
More...
 
You must log in or register to reply here.
Back
Top