Results 1 to 3 of 3

Thread: collecting data

  1. #1
    Lounger
    Join Date
    May 2007
    Location
    desertland, Western Australia, Australia
    Posts
    33
    Thanks
    1
    Thanked 0 Times in 0 Posts
    ok.. I have included a spread sheet of stockmarket data.. [attachment=85292ortfolio_ex.xlsx]

    1) I want this to add the (-) and (+) values seperately as well as the sum in (J22) these will be shown in (J23) and (J24) respectively
    2) in (H3, 4, 9, 14, 19) I mark a (x) denoting intention to sell
    HOPEFULLY we can make these 5 marked lines, change colour and NOT be counted in (D) (I) and (J) columns, and those columns display new values
    THEN.. the data in these lines will be shown in respective columns (D25) (I25) and (J25) as
    (D25) total purchased price of five marked line items
    (I25) total of market value of five marked line items
    (J25) LOSS value of five marked line items


    *smiles*
    Ultimately I will add more features... but this is where I am right now.. please HELP!!!!
    I THINK i have uploaded and managed this correctly..
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The sum of the positive values in J3:J21 is =SUMIF($J$3:$J$21,">0") and that of the negative values is =SUMIF($J$3:$J$21,"<0")

    The sum of the values in D321 where the corresponding cell in column H does NOT contain x is
    =SUMIF($H$3:$H$21,"<>x",D321)
    and the sum of the items where the corresponding cell in column H does contain x is
    =SUMIF($H$3:$H$21,"x",D321)

    Similar for column I.

    If you want to exclude the rows with "x" in column H from the sum of positive and negative values in column J, the formulas for J23 and J24 become
    =SUMPRODUCT(($H$3:$H$21<>"x")*($J$3:$J$21>0)*$J$3: $J$21)
    and
    =SUMPRODUCT(($H$3:$H$21<>"x")*($J$3:$J$21<0)*$J$3: $J$21)

    See attached version.
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    May 2007
    Location
    desertland, Western Australia, Australia
    Posts
    33
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hans.. you are a God!!

    thanks mate.. did exactly what I needed!!

    cheers
    kio

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •