This article is also available as a TechRepublic download.

The article was originally published on August 10, 2004.

Comparing current results to results recorded in the past is
a mainstay of business decision making. Often the best way to communicate such
comparisons is through a graphical representation. The charting capability of
Microsoft Excel is an excellent tool for this type of scenario. But getting
that charting tool to behave just as we want it to behave can get a little
involved. This was the gist of tion-thee’s
question in a TechRepublic Technical Q&A:

“I have 9 columns
on Excel, say columns A1, A2, A3, B1, B2, B3, C1, C2, C—here’s the scenario, I
want to create a bar chart that displays the amount of A1, A2, A3 but the
amount of A2 and A3 should be sum up. It’s like I have a separate bar for A1
and a separate bar for A2+A3. Same goes with B1, B2+B3 and C1, C2+C3, this
should be showed in 1 chart only. Right now I’m having a hard time doing it so
what I opted to do is to separate the chart for A1 and A2+A3…”

TechRepublic member gill_potter
suggested the specific informational nugget that would make this possible
in his answer:

“The quick way is
to create a new row A4 that is a sum of A2 and A3. Then select just the 1 row
and ctrl select the 4 row and click the chart wizard. This will create a bar
chart that has the A1 value next to the A4 value and then the next bucket will
be the B1 value compared to the B4 value. If you want to compare all of the 1
values together and all of the 4 values together, just change the chart option
to data in columns rather then data in rows.”

The key to the solution is adding the two columns and
creating a fourth column to hold the results. Often, the best method for
solving any problem in Excel is by breaking it up piecemeal and solving the
bits until you get to the desired end results. Creating the additional data
will give tion-thee the data he/she
would like to compare in a bar graph (see Figure
A
).

Figure A

Example One

The bar chart in Figure A was derived from this simple data
table:

10

14

17

11

15

18

13

16

19

24

31

37

ColA

ColB

ColC

However, after gill-potter
made his suggestion, the question further evolved into a query about how to
insert some automation into the process, so that the comparison could be
updated as new current results were reported. A little more data manipulation
will be required to make this work.

Set up the scenario

We’ll create a simple scenario for our example. Let’s say we
want to report company revenue to a group of shareholders. We know these
shareholders want to compare revenue in 2004 with revenue in 2003 and 2002. We
also know they want to see a comparison for each quarter as well as the annual
totals.

The key factor in this case is the number of data points we
have recorded so far in 2004. If it is only February, then we are only going to
be able to compare two months; but if it is August, we have to compare the
accumulated revenue for eight months and the accumulated revenue for the first
two months of the third quarter. To get the number of revenue data points
(months) in 2004 available so far, we use the COUNT() function (the orange G1
cell in Figure B).

Figure B

Example Two

With the number of data points counted, we can determine
what quarter we are comparing using the IF() statement in combination with the
AND() function:

=IF(G1<=3,1,IF(AND((G1)>3,(G1)<=6),2,IF(AND((G1)>6,(G1)<=9),3,IF(AND((G1)>9,(G1)<=12),4,””))))

This logical formula will return “2” if the
COUNT() in G1 is greater than 3 and less than or equal to 6, which means we are
examining the second quarter. It will accordingly return, “1”,
“3”, or “4” depending on the quarter designated by the
value of G1. (See the green J5 cell in Figure B.)

The COUNT() function, in conjunction with the OFFSET()
function, is also used to accumulate the total revenues for each year up to the
month being examined. In other words, if we are looking at results through
August 2004, we use this formula:

=SUM(B5:OFFSET(B5,G1-1,0))

The result is displayed in blue cells F6, G6, and H6 (Figure
B). These values are the SUM totals of the rows ranging from January through
August. The total for the year is important for determining how much each
quarter total has contributed to the year, which is the main idea of our graphical
presentation.

The purple cells in Figure B are the running totals for each
quarter for each year. The calculations also take advantage of the OFFSET()
function in Excel. Using an IF() statement, we can calculate the value for each
quarter, like this formula for the third quarter of 2004:

=IF(J5=3,F6-F8-F9,OFFSET(B1,10,0)+OFFSET(B1,11,0)+OFFSET(B1,12,0))

The purple set of cells was created to make our calculations
for the remaining blue row of F5, G5, and H5 easier to calculate. The following
formula takes the information calculated for the year-to-date totals and for
the quarters so far and determines the value to be displayed for the month
being examined:

=IF(J5=1,F6,IF(J5=2,F9,IF(J5=3,F10,IF(J5=4,F11,F6))))

If we had not calculated the values in the purple cells
first, we would have had to put their corresponding formulas into this formula,
which would have been unwieldy and prone to error. These calculations are shown
in Figure B for the purposes of this article, but in practice you should hide
these cells on another worksheet.

Chart it

Once we have calculated the year-to-date totals for each
year and the corresponding quarterly totals, charting the results can be
accomplished with the Excel Chart Wizard. As you can see in Figure B, using the
standard bar chart found in the wizard, we can present a graphical
representation that compares each year’s revenue on an annual basis and on a
quarterly basis. Once again, in most normal circumstances, you would want the
chart to appear on a page by itself. The only data the user has to input will
be located in the green section of Figure B, where the 2004 data will be
entered.

Improvement

While Figures A and B can give you a general feel for how
the Excel worksheet works, it cannot be as helpful as a real live Excel
worksheet. That’s why we are making the two examples discussed in this article
available for download.
Neither file is a work of art, but they do work, and they should help tion-thee with his Tech Q&A
question, or at least give him something to think about.

However, there is plenty of room for improvement in these
example worksheets, and I encourage you to download them and poke around. If you
do take some time to enhance or improve them, or if you have a better technique
than what is outlined here, we’d like to see it.