# Thread: Convert data to chart (2003)

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

2. ## 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. ## 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
•