Charting with Difference indicator

  • Thread starter Thread starter beeawwb
  • Start date Start date
B

beeawwb

Good morning all. Not quite sure how / if this one can be done.

I have a series of charts that I use, that change when I add a new week
(charting using the Offset function on Count/Counta) and my manager has
asked me if it's possible to do this.

When a number goes up, have it with an Up Arrow, and the number it went
up. When it goes down, have it with a Down Arrow, and the number it
went down.

Eg.

----UP12----DOWN14----UP3----UP5

Is there a way to make this display on the chart? I can get the actual
numbers displaying (eg 145, 157, 143, 146, 151) but not sure how I
could get the actual change to show.

Anybody know how to do this, or similar?

Many thanks in advance,

-Bob
 
Plot two series, one for up values, the other for down values. The
first series will have a #N/A for any down value; the 2nd series will
have the reverse setup.

For the first series, use a custom marker of an up arrow, for the 2nd
the custom marker will be a down arrow.

For both, set the data labels to a column that contains the absolute
difference.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Hi Bob

I'd try adding a line subtracting the current figure from
the previous period and charting this. If you use a line
graph then this will provide an up or down line.

Say the data is in A1 to F3
Wk4 wk1 wk2 wk3 wk4
Sales 214 150 324 125 324
Diff -64 174 -199 199

Formula in C3 is =C2-B2 and copied across.

You can add Values when the chart wizard gets to format
adding headers - click in Data Labels and click the Show
Values button.
 
Sorry, I don't *quite* understand what you mean.

The first part I understand is saying that I will need a new column fo
each, to say whether it went up or down?

I'll paste some of my sample data.

Week Ending: 19.Dec.03, 26.Dec.03, 02.Jan.04, 09.Jan.04
New Claims: 22, 115,114,115,113
DM Mail: 728, 684, 650, 566, 548
DM Urgent: 89, 18, 19, 22, 6
DM Total: 838, 755, 739,650, 589

So, what do I need. 1 new row for each?

New Claims Shift
DM Mail Shift
DM Urgent Shift
DM Total Shift

?

Then how do I get those set to labels on my chart? (I should mentio
it's a line chart). Even if they don't have an up / down arrow, jus
having numbers like 87, -2, -33, etc would be helpful.

Thanks so much for the help.

-Bo
 
I refer you to my reply before. Yes you have to have a
line of differences for each transaction. Using your
supplied data this looks like:

Week Ending 19.Dec.03 26.Dec.03 02.Jan.04
09.Jan.04 17.Jan.04
New Claims 22 115 114 115 113
DM Mail 728 684 650 566 548
DM Urgent 89 18 19 22 6
DM Total 838 755 739 650 589

Difference 26.Dec.03 02.Jan.04 09.Jan.04 17.Jan.04
New Claims 93 -1 1 -2
DM Mail -44 -34 -84 -18
DM Urgent -71 1 3 -16

Use a Cone shape for the chart type (this will provide
arrows and include data labels to get the values.

You could also use a data table that shows the values
used in the chart.

Regards
Peter
 
Sorry Peter, I didn't actually see your reply the first time. It must
have been posted when I was writing mine.

I have created the Cone chart, but the problem is, it really needs to
be done as a Line Chart, that's what my manager wants. Is there any way
to show differences on a line chart, just by labelling? What I have
done currently is create a series of Text Boxes, 1 in Wingdings for
Up/Down Arrow, another normal one with the shift number. The problem
is, I have to do the math manually and the numbers aren't linked to the
chart itself.

Thanks for any help you guys can provide.

-Bob
 
OK, now that you've shared some specifics...

First of all, you have 4 entries in the first row but 5 in each of the
subsequent rows. For convenience, I added a new column header to row 1
(16.Jan.04).

This addresses only one of the business entities: New claims.

Suppose the week ending data are in C1:G1 and the new claims are in
C2:G2.

Then, in A8, enter the literal 'New Up' and in D8, the formula
=IF(D2>C2,D2,NA()). In A9, enter the literal 'New Down' and in D9, the
formula =IF(ISNA(D8),D2,NA()). In A10, enter the literal 'New Abs' and
in D10, the formula =ABS(D2-C2). Copy D8:D10 to E:G

Plot A1:G2 as a line chart. Set the markers to none (double-click the
plotted series, and in the resulting dialog box select the Patterns
tab).

Add C8:G9 to the chart. For these two series, set the line to none
(same Patterns tab as in the previous paragraph). In the Legend box,
select the entry for each of these two new series and delete it.

Create a up arrow (Drawing Toolbar | AutoShapes > Block Arrows) and a
down arrow. Format each so that it is aesthetically appealing (make it
small while still retaining its arrow-ness). Color them if desired
(maybe green for the up arrow and red for the down arrow?)

Select and copy the up arrow. Select the markers for the up series
(corresponding to the contents of row 8). Paste.

Do the same for the down arrow and the down series (corresponding to
the contents of row 9)

Use Rob Bovey's XY Chartlabeler add-in (www.appspro.com) or John
Walkenbach's Chart Tools (www.j-walk.com) to add row 10 (C10:G10) as
the data labels for the first series (C2:C10).

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Somewhere in the discussion the OP mentioned it had to be a line chart.
So, I skipped the waterfall suggestion. ;-)

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Hi Bob -

Sounds like a job for a Waterfall chart. I have some information here:

http://peltiertech.com/Excel/Charts/Waterfall.html

And I know Tushar has an example on his site (I'm surprised he didn't
mention waterfalls). Use the suggested technique from this page:

http://peltiertech.com/Excel/ChartsHowTo/CustomSeriesFormatting.html

to use an Up Arrow autoshape for the series showing increases, and a
Down Arrow autoshape for the decreases.

- Jon
Click to expand...
 
That solution with the Down, Up, ABS worked exactly the way I wanted
Well, a bit more complex than I would have liked, but it looks great
The only problem I'm having is Excel 97 crashing when I'm halfwa
through the 3rd chart. Done it twice now. I may just save after
complete each 'segment' of the charting. That might stop it.

Thanks for the help!

-Bo
 
You are welcome. Glad to be of help.

Dunno about the XL97 crash. Check if you have 'Autosize' enabled
(double click a chart element such as the x-axis, then select the Font
tab). If you do, uncheck it. Might help.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
I got it in the end. It wasn't that I was doing anything specific, i
would crash at varying times. I just saved every few sections (adde
series, changed values, added markers, etc) and it came out fine. I
may have been just something random, although I find it very od
whenever any business machine crashes a program. Still, no harm done
just a bit of frustration at the time.

Thanks once again.

-Bo
 
Back
Top