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.

    Below is the spreadsheet.


    Michael B.
     
  2. Sorry attachment missed in opening post.

    Here it is.
     
  3. Excel has Matrix and it can combine with any other excel function like SUM, IF...
     
  4. execute2

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

     
  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.


     
  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 :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

     
    #10     Apr 12, 2004