Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    code/formula help (excel2003)

    Hi all,

    Attached file includes details of my question.
    What I have is a huge list of wells (column A) and four columns of data at different time. I want to have an automated mean to output the total of the difference between QO15 & QO10, if it is <0 (negative) for all the wells with a cat2010 value that is different in cat2015.
    tia
    TIA
    dubdub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: code/formula help (excel2003)

    I don't understand your "sample of output". Why doesn't well # 10 feature in it?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code/formula help (excel2003)

    Hi Hans,

    because the output i included is a one step chnage between cat2010 & 2015. for well # 10 the change in cat2015 from cat2010 was three steps 2 to 5. if those can ce separated in a different set that will be great.
    TIA
    dubdub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: code/formula help (excel2003)

    Can you please provide a complete description of what you want and a complete sample of the desired output?

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code/formula help (excel2003)

    Hans,

    attached file has it, but please note that the one step change sets has to cover all the one step changes between cat2010 and cat2015, and the same applied for the more than one step change sets.


    Regards,
    TIA
    dubdub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: code/formula help (excel2003)

    I don't understand what you mean by "please note that the one step change sets has to cover all the one step changes between cat2010 and cat2015, and the same applied for the more than one step change sets."

    And in the spreadsheet, you say "I can use filtering, but it mandates entering a new column) every time you enter a new conditions and shuffling back and forth to do the changes." What changes to the conditions do you foresee?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: code/formula help (excel2003)

    I am not sure what you are after.

    If I understand what you want (just the sum) for the "1 step changes" and just negative numbers. how about this array (confirm with ctrl-shift-enter):
    =SUM(IF(((I3:I9-E3:E9)=1)*((F3:F9-B3:B9)<0),F3:F9-B3:B9))

    For more than 1, this array (confirm with ctrl-shift-enter):
    =SUM(IF(((I3:I9-E3:E9)>1)*((F3:F9-B3:B9)<0),F3:F9-B3:B9))

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code/formula help (excel2003)

    Hans,

    Let me see if I can re-describe the data structure and what I want to have. The data sample in the file consists of well number and four columns of data for every well at two different dates, year 2010 and 2015. the cat2010 which is one of these four columns reflects the well base status code, I have 6 well base status codes,1,2,3,4,5,6. The cat2015 reflects 2015 well status codes, and it has the same well status codes numbers. I want to quantify the change in well status codes by (QO15-QO10) with concentration on the negative values and get the total. What I need is an output similar to the part highlighted in green in the attached file for all well status codes.
    I have made some changes to the previous format (green highlights), the new format (yellow highlights) using Steve formula, yet I still need formula help in getting the data for the new added columns, well counts and well numbers. Moreover, help in coding the process if possible.

    regards,
    TIA
    dubdub

  9. #9
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code/formula help (excel2003)

    hi steve,

    kindly, please see my reply to Hans.

    regards,
    TIA
    dubdub

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: code/formula help (excel2003)

    I'd use a combination of
    - Add columns with formulas for Step, QO15-QO10 and QW15-QW10.
    - Use AutoFilter to display only rows for which QO15-QO10 is negative.
    - Sort on Step, then on Cat2010, then on Well.
    - Insert subtotals on change of Cat2010 for QO15-QO10 and QW15-QW10.
    No code needed. See attached version.

  11. #11
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code/formula help (excel2003)

    thanks Hans,

    one more clarification, did you manually insert a line for subtotal on change of Cat2010 for QO15-QO10 and QW15-QW10. if so, is there any other easy way to do it.

    regards,
    TIA
    dubdub

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: code/formula help (excel2003)

    No, I used Data | Subtotals...

  13. #13
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code/formula help (excel2003)

    thanks.
    TIA
    dubdub

Posting Permissions

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