Overlapping 2 charts (XL2003)

[Cartman]

I have a 5 row 3 column sheet in A1:C5. Here are the 15 values (first
3 are column headings):
type day amount
A,1,10
A,2,11
A,3,12
B,3,7

I'm aiming to have a chart with 1,2,3 on x axis, and 3 blue values: 10
units tall over the 1; 11 over the 2; 12 over the 3. Then I want a red
7 over the x value 3. So I want to see both 12 and 7 above the 3 on
the x axis, either overlapping or side by side.

I created a chart by highlighting C2:C4 and hitting F11. I can name
that series as A. So far so good. Now I want to overlay B data, so
under Source Data I add a series named B with values =sheet1!c5:c5.

The problem is that series B (the bar 7 units high) shows above x axis
value 1, not above 3.

I've try jimmying with "Category (X) axis labels:" on Source Data as
b2:b5, but series B keeps wanting to be "left justified" even though
it doesn't correspond to the x access value. I tried setting the
Source Data "Data Range" (the other tab there) to C2:C5, and you can
see that it now shows a value 4 on the x axis!

Can I achieve what I want here? Do I need to go 3 dimensional?

 

[Barn E. Fife]

I have a 5 row 3 column sheet in A1:C5. Here are the 15 values (first
3 are column headings):
type day amount
A,1,10
A,2,11
A,3,12
B,3,7

I'm aiming to have a chart with 1,2,3 on x axis, and 3 blue values: 10
units tall over the 1; 11 over the 2; 12 over the 3. Then I want a red
7 over the x value 3.  So I want to see both 12 and 7 above the 3 on
the x axis, either overlapping or side by side.

I created a chart by highlighting C2:C4 and hitting F11. I can name
that series as A. So far so good. Now I want to overlay B data, so
under Source Data I add a series named B with values =sheet1!c5:c5.

The problem is that series B (the bar 7 units high) shows above x axis
value 1, not above 3.

I've try jimmying with "Category (X) axis labels:" on Source Data as
b2:b5, but series B keeps wanting to be "left justified" even though
it doesn't correspond to the x access value. I tried setting the
Source Data "Data Range" (the other tab there) to C2:C5, and you can
see that it now shows a value 4 on the x axis!

Can I achieve what I want here? Do I need to go 3 dimensional?

You want to put A data in one column (let's say D), then B data in the
next. Then highlight the 2 columns D2:E5 and hit F11; set the X axis
as B2:B5.

Using the above data, you can set D1="A" and E1="B"; D2=IF($A2=D
$1,$C2,"") and copy that to generate values for columns D and E, if
you don't want to have to manually rearrange which columns data are in.

 

[Barn E. Fife]

Using the above data, you can set D1="A" and E1="B";  D2=IF($A2=D

$1,$C2,"")

Sorry, make that
D2=IF($A2=D> $1,$C2,0)
instead of
D2=IF($A2=D> $1,$C2,"")