Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert data to chart (2003)

    I am trying to convert a series of rows of budget data detail into a chart of quarterly expenses. In the attached worksheet, the quarterly amounts in Col. L start in the quarter identified in Col. I and stop in the quarter identified in Col. J. I have tried unsuccessfully to accomplish with IF statements. Cell N23 is highlighted red and represents the only cell in Col. N for which my IF statements do not work properly; but I can not figure out how to remedy. In Row 91 I started trying to determine just the totals using SUMPRODUCT; again I was stumped.

    A non-VBA solution will be easier to explain to my colleagues, but if VBA method is easier, it is fine. (Scientists are frequently suspicious of any "Black box" type of answers.)

    Thank you,

    Dr. Andy
    Attached Files Attached Files

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

    Re: Convert data to chart (2003)

    I'd change the quarters in columns I and J to numbers instead of text:

    In I7: =IF(LEFT(B7)<>"Q",1,VALUE(MID(B7,2,1)))
    In J7: =IF(LEFT(B7)<>"Q",$I$1,VALUE(RIGHT(B7,1+OR(LEN(B7) >7,LEN(B7)=3))))

    Then use this formula in M7:

    =IF(AND(M$6>=$I7,M$6<=$J7),$L7,"")

    Fill to the right then down.

    The formulas in row 90 are fine, but if you prefer SUMPRODUCT, use

    =SUMPRODUCT((M$6>=$I$7:$I$89)*(M$6<=$J$7:$J$89)*$L $7:$L$89)

    in M90 and fill to the right.

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert data to chart (2003)

    Another fine learning experience.

    Thank you Hans

    Dr. Andy

Posting Permissions

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