I can't get Axis labels as I want!

[Sirritys]

I have table like this:

2002 2003 2004 2005 2006 2007
Q1 100,00 100,00 100,00 98,95 102,79
Q2 100,00 100,00 99,65 99,01
Q3 100,00 100,00 98,84 99,01
Q4 100,00 100,00 98,23 99,00

The problem is that when I chart the table I get into X-axis label
years 2002-2007 right after each other. Excel doesn't seem to
understand that they are supposed to fit only every fourth value in the
line.

So what I want to x-axis label is either this:
2002 Q2 Q3 Q4 2003 Q2 Q3 ...etc
OR
2002 2003 2004...etc

What excel gives me now is:
2002 2003 2004 ... etc

Anyone knows how to fix this ?

 

[Guest]

I don't understand what you mean by "Excel doesn't seem to
understand that they are supposed to fit only every fourth value in the
line." Is it you want to show the four quarters repeatedly for each year, and
this group of quarters labeled for each year? Assuming that is, set up yr
sheet as follows.
In A1: 2002
In A2: Q1; in B2: Q2; in C2: Q3; in D2: Q4;
In A33 the respective values for Q1:Q4
Repeat for 2003 in cells E1:H3, and repeat until you have all yr years
Now select A1:H3 (just to show the effect for two years; adjust your range
accordingly) and see whether you like what you get (it should display two
rows of axis lables: Quarters and below the year, the years separated by
vertical border lines).
HTH, Henk

 

[Sirritys]

I don't understand what you mean by "Excel doesn't seem to
understand that they are supposed to fit only every fourth value in the
line." Is it you want to show the four quarters repeatedly for each year, and
this group of quarters labeled for each year? Assuming that is, set up yr
sheet as follows.
In A1: 2002
In A2: Q1; in B2: Q2; in C2: Q3; in D2: Q4;
In A33 the respective values for Q1:Q4
Repeat for 2003 in cells E1:H3, and repeat until you have all yr years
Now select A1:H3 (just to show the effect for two years; adjust your range
accordingly) and see whether you like what you get (it should display two
rows of axis lables: Quarters and below the year, the years separated by
vertical border lines).
HTH, Henk

Sorry for being unclear. I'll try to make more sense now.

I'm not compromising the table structure. It has to stay the way I
posted it. No I'm plotting one curve from the table values so that I
get one curve plotting values from 2002 Q1 to 2006 Q1.

Plotting is fine but I don't know how to get right labels to X-axis. In
other words, what should I but to "Source Data -> Category (x) axis
labels".

I can do it with
"=companyname!$B$38;companyname!$A$40:$A$42;companyname!$C$38;companyname!$A$40:$A$42;companyname!$D$38;companyname!$A$40:$A$42;companyname!$E$38;companyname!$A$40:$A$42;companyname!$F$38;companyname!$A$40:$A$42;companyname!$G$38"

But there has to be easier way, right ?

Here is table with column and row numbers:

A B C D E F G
38 2002 2003 2004 2005 2006 2007
39 Q1 100,00 100,00 100,00 98,95 102,79
40 Q2 100,00 100,00 99,65 99,01
41 Q3 100,00 100,00 98,84 99,01
42 Q4 100,00 100,00 98,23 99,00

 

[Jon Peltier]

I'm not compromising the table structure.

Okay, then you are compromising chart structure.

Or perhaps you can have the best of both worlds. In another area of the
worksheet, or in a second worksheet, arrange the same data, but optimized
for charting, as Henk suggests. Use links so that updating the main table
also updates the chart data.

- Jon

 

[Sirritys]

Yes, I think I'll surrender and do it that way ;-)

I just hate to use extra sheet for something like that.
(Don't want to put it to original sheet, so that extra table wouldn't
print)

Thanks for trying thought ;-)

-Aki

 

[Don Guillett]

Jon is one of the foremost authorities on Excel charting. Perhaps you should
listen to him.

 

[Jon Peltier]

Think about it. You can either use an extra sheet, which takes up a few KB
and a few minutes, or you can do crazy things with formulas and trying to
use unwieldy discontiguous ranges, and waste hours.

- Jon

 

[Guest]

Don: fully agree, and I want to add: Jon is also ver open in sharing his
knowledge and extremely patient with all of us that still have to learn what
Jon already mastered ages ago.

I cannot agree more to Jon's advice re the extra table. The extra table is
done very quickly. You can still use the original table in any report
instead of the new table that is simply made to make the charting easier.

 

[Sirritys]

Thanks guys for the help and patience, I don't mean to dishonor anyone,
I KNOW you guys know these things better than me.

But there was one problem with using extra sheet and chart from that

When I made extra table and it's values where like "='Sheet1'!B71" the
value came out like "0" if the 1st table was empty. Now the excel plots
that "0" as opposed not plotting empty cell. I wan't the curve just
stop, not to drop to value zero.

The same problem arises when I use "" or NA(), in formula, if
corresponding cell in table1 was empty cell.

Any suggestion how to solve this if I chart from another sheet/table.

(I can't assume the value area would be widened every time new value
will be added in future, since ppl who know nothing about excel has to
be able to update this chart)


Feeling sorry for being such a hard case,
Aki Koikkalainen

 

[Jon Peltier]

=IF(LEN('Sheet1'!B71)=0,NA(),'Sheet1'!B71)

- Jon

 

[Sirritys]

Ok, I have no more complains, the last post solved my last problem.

Thanks alot for help!


-Aki