3-d referneces in chart series values

I have a chart that has three series plotted on it, the first is column 
format and the second and third are lines. The first (column) represents 
average golf scores for one hole for individuals, the second (line) 
represents par, while the third (line) represents overall average for all 
golfers.

I can plot the column graph for the 23 competitors - no problem. The problem 
is with the two line graphs. I want the lines to extend the width of the 23 
columns.

To do this I need 23 points that are the same for each line. I would like to 
use something like =player1:player23!$c$323, but I keep getting an error.

I can do something like 
=(player!$C$323,player!$C$323,player!$C$323,player!$C$323), but I can only 
seem to be able to do this 18 times.

Is there anyway to solve this dilema?

Rudimentary I know, but this represents what I am trying to do.

There are two line which need to extend the width of the chart
There are 23 columns.
___________________________________ line 2

| | | | | | | | | | | | | | | | | | | | | | | | |
--------------------------------------------------line 1
| | | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | | | | | | |
                         1 1 1 1 1 1 1 1 1 1 2 2 2 2
1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3

Thanks
0
DJFudd (3)
10/5/2008 3:07:01 AM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
439 Views

Similar Articles

[PageSpeed] 19

Hi,

If all the points are the same value for each player then actually you 
only need 2 occurances of the value for each line.
If you move the 2 lines series to the secondary axis, add the secondary 
category axis via the Chart Options dialog. Then unset the secondary 
category axis to Cross between categorys.
You will need to alter the ranged used for Category labels on the 
secondary axis to 2 points.

It would also be a lot easier if you built the chart data on a separate 
sheet. Consolidating the data from 23 sheets to 1 before charting.

Cheers
Andy


DJFudd wrote:
> I have a chart that has three series plotted on it, the first is column 
> format and the second and third are lines. The first (column) represents 
> average golf scores for one hole for individuals, the second (line) 
> represents par, while the third (line) represents overall average for all 
> golfers.
> 
> I can plot the column graph for the 23 competitors - no problem. The problem 
> is with the two line graphs. I want the lines to extend the width of the 23 
> columns.
> 
> To do this I need 23 points that are the same for each line. I would like to 
> use something like =player1:player23!$c$323, but I keep getting an error.
> 
> I can do something like 
> =(player!$C$323,player!$C$323,player!$C$323,player!$C$323), but I can only 
> seem to be able to do this 18 times.
> 
> Is there anyway to solve this dilema?
> 
> Rudimentary I know, but this represents what I am trying to do.
> 
> There are two line which need to extend the width of the chart
> There are 23 columns.
> ___________________________________ line 2
> 
> | | | | | | | | | | | | | | | | | | | | | | | | |
> --------------------------------------------------line 1
> | | | | | | | | | | | | | | | | | | | | | | | | |
> | | | | | | | | | | | | | | | | | | | | | | | | |
>                          1 1 1 1 1 1 1 1 1 1 2 2 2 2
> 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3
> 
> Thanks

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
10/5/2008 9:37:25 AM
Thanks Andy . . .

I took your advice and did it the easier way. Worked like a charm.

Something that I found out though - I had the values in cells on the same 
sheet as the chart, when I hide the rows so they wouldn't print the graph 
lines went away.

Anyway, a minor inconvenience being that your idea worked.

Thanks again.

....Dan

"Andy Pope" wrote:

> Hi,
> 
> If all the points are the same value for each player then actually you 
> only need 2 occurances of the value for each line.
> If you move the 2 lines series to the secondary axis, add the secondary 
> category axis via the Chart Options dialog. Then unset the secondary 
> category axis to Cross between categorys.
> You will need to alter the ranged used for Category labels on the 
> secondary axis to 2 points.
> 
> It would also be a lot easier if you built the chart data on a separate 
> sheet. Consolidating the data from 23 sheets to 1 before charting.
> 
> Cheers
> Andy
> 
> 
> DJFudd wrote:
> > I have a chart that has three series plotted on it, the first is column 
> > format and the second and third are lines. The first (column) represents 
> > average golf scores for one hole for individuals, the second (line) 
> > represents par, while the third (line) represents overall average for all 
> > golfers.
> > 
> > I can plot the column graph for the 23 competitors - no problem. The problem 
> > is with the two line graphs. I want the lines to extend the width of the 23 
> > columns.
> > 
> > To do this I need 23 points that are the same for each line. I would like to 
> > use something like =player1:player23!$c$323, but I keep getting an error.
> > 
> > I can do something like 
> > =(player!$C$323,player!$C$323,player!$C$323,player!$C$323), but I can only 
> > seem to be able to do this 18 times.
> > 
> > Is there anyway to solve this dilema?
> > 
> > Rudimentary I know, but this represents what I am trying to do.
> > 
> > There are two line which need to extend the width of the chart
> > There are 23 columns.
> > ___________________________________ line 2
> > 
> > | | | | | | | | | | | | | | | | | | | | | | | | |
> > --------------------------------------------------line 1
> > | | | | | | | | | | | | | | | | | | | | | | | | |
> > | | | | | | | | | | | | | | | | | | | | | | | | |
> >                          1 1 1 1 1 1 1 1 1 1 2 2 2 2
> > 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3
> > 
> > Thanks
> 
> -- 
> 
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
> 
0
DJFudd (3)
10/6/2008 11:37:49 PM
Hi,

That is because the option to 'plot visible cells' is on.

xl2003 - Select chart. Tools > Options > Chart. Uncheck 'Plot visible cells 
only'

xl2007 - Select chart. Chart Tools > Design > Data > Select Data > Hidden 
and Empty cells > Show data in hidden rows/columns.

Cheers
Andy

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"DJFudd" <DJFudd@discussions.microsoft.com> wrote in message 
news:7F7063F5-0D16-4ADF-9E6B-8541438E14F8@microsoft.com...
> Thanks Andy . . .
>
> I took your advice and did it the easier way. Worked like a charm.
>
> Something that I found out though - I had the values in cells on the same
> sheet as the chart, when I hide the rows so they wouldn't print the graph
> lines went away.
>
> Anyway, a minor inconvenience being that your idea worked.
>
> Thanks again.
>
> ...Dan
>
> "Andy Pope" wrote:
>
>> Hi,
>>
>> If all the points are the same value for each player then actually you
>> only need 2 occurances of the value for each line.
>> If you move the 2 lines series to the secondary axis, add the secondary
>> category axis via the Chart Options dialog. Then unset the secondary
>> category axis to Cross between categorys.
>> You will need to alter the ranged used for Category labels on the
>> secondary axis to 2 points.
>>
>> It would also be a lot easier if you built the chart data on a separate
>> sheet. Consolidating the data from 23 sheets to 1 before charting.
>>
>> Cheers
>> Andy
>>
>>
>> DJFudd wrote:
>> > I have a chart that has three series plotted on it, the first is column
>> > format and the second and third are lines. The first (column) 
>> > represents
>> > average golf scores for one hole for individuals, the second (line)
>> > represents par, while the third (line) represents overall average for 
>> > all
>> > golfers.
>> >
>> > I can plot the column graph for the 23 competitors - no problem. The 
>> > problem
>> > is with the two line graphs. I want the lines to extend the width of 
>> > the 23
>> > columns.
>> >
>> > To do this I need 23 points that are the same for each line. I would 
>> > like to
>> > use something like =player1:player23!$c$323, but I keep getting an 
>> > error.
>> >
>> > I can do something like
>> > =(player!$C$323,player!$C$323,player!$C$323,player!$C$323), but I can 
>> > only
>> > seem to be able to do this 18 times.
>> >
>> > Is there anyway to solve this dilema?
>> >
>> > Rudimentary I know, but this represents what I am trying to do.
>> >
>> > There are two line which need to extend the width of the chart
>> > There are 23 columns.
>> > ___________________________________ line 2
>> >
>> > | | | | | | | | | | | | | | | | | | | | | | | | |
>> > --------------------------------------------------line 1
>> > | | | | | | | | | | | | | | | | | | | | | | | | |
>> > | | | | | | | | | | | | | | | | | | | | | | | | |
>> >                          1 1 1 1 1 1 1 1 1 1 2 2 2 2
>> > 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3
>> >
>> > Thanks
>>
>> -- 
>>
>> Andy Pope, Microsoft MVP - Excel
>> http://www.andypope.info
>> 

0
andy9699 (3616)
10/7/2008 7:45:52 AM
Andy . . .

Once again you hit the nail on the head. I have done as you suggested and my 
lines are back.

Thanks

....Dan

"Andy Pope" wrote:

> Hi,
> 
> That is because the option to 'plot visible cells' is on.
> 
> xl2003 - Select chart. Tools > Options > Chart. Uncheck 'Plot visible cells 
> only'
> 
> xl2007 - Select chart. Chart Tools > Design > Data > Select Data > Hidden 
> and Empty cells > Show data in hidden rows/columns.
> 
> Cheers
> Andy
> 
> -- 
> 
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
> "DJFudd" <DJFudd@discussions.microsoft.com> wrote in message 
> news:7F7063F5-0D16-4ADF-9E6B-8541438E14F8@microsoft.com...
> > Thanks Andy . . .
> >
> > I took your advice and did it the easier way. Worked like a charm.
> >
> > Something that I found out though - I had the values in cells on the same
> > sheet as the chart, when I hide the rows so they wouldn't print the graph
> > lines went away.
> >
> > Anyway, a minor inconvenience being that your idea worked.
> >
> > Thanks again.
> >
> > ...Dan
> >
> > "Andy Pope" wrote:
> >
> >> Hi,
> >>
> >> If all the points are the same value for each player then actually you
> >> only need 2 occurances of the value for each line.
> >> If you move the 2 lines series to the secondary axis, add the secondary
> >> category axis via the Chart Options dialog. Then unset the secondary
> >> category axis to Cross between categorys.
> >> You will need to alter the ranged used for Category labels on the
> >> secondary axis to 2 points.
> >>
> >> It would also be a lot easier if you built the chart data on a separate
> >> sheet. Consolidating the data from 23 sheets to 1 before charting.
> >>
> >> Cheers
> >> Andy
> >>
> >>
> >> DJFudd wrote:
> >> > I have a chart that has three series plotted on it, the first is column
> >> > format and the second and third are lines. The first (column) 
> >> > represents
> >> > average golf scores for one hole for individuals, the second (line)
> >> > represents par, while the third (line) represents overall average for 
> >> > all
> >> > golfers.
> >> >
> >> > I can plot the column graph for the 23 competitors - no problem. The 
> >> > problem
> >> > is with the two line graphs. I want the lines to extend the width of 
> >> > the 23
> >> > columns.
> >> >
> >> > To do this I need 23 points that are the same for each line. I would 
> >> > like to
> >> > use something like =player1:player23!$c$323, but I keep getting an 
> >> > error.
> >> >
> >> > I can do something like
> >> > =(player!$C$323,player!$C$323,player!$C$323,player!$C$323), but I can 
> >> > only
> >> > seem to be able to do this 18 times.
> >> >
> >> > Is there anyway to solve this dilema?
> >> >
> >> > Rudimentary I know, but this represents what I am trying to do.
> >> >
> >> > There are two line which need to extend the width of the chart
> >> > There are 23 columns.
> >> > ___________________________________ line 2
> >> >
> >> > | | | | | | | | | | | | | | | | | | | | | | | | |
> >> > --------------------------------------------------line 1
> >> > | | | | | | | | | | | | | | | | | | | | | | | | |
> >> > | | | | | | | | | | | | | | | | | | | | | | | | |
> >> >                          1 1 1 1 1 1 1 1 1 1 2 2 2 2
> >> > 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3
> >> >
> >> > Thanks
> >>
> >> -- 
> >>
> >> Andy Pope, Microsoft MVP - Excel
> >> http://www.andypope.info
> >> 
> 
> 
0
DJFudd (3)
10/8/2008 12:48:01 AM
Reply:

Similar Artilces:

Open Office #3
I have several OpenOffice.org files that I would like to open in Excel since we no longer use OpenOffice. Is there a converter somewhere? Hi The converter is OpenOffice. Choose "excel" as filetype in its SaveAs menu. As far as I know there is no easier way, but if you know how to program OpenOffice macros then it should be pretty painless. HTH. Best wishes Harald "Scott" <Scott@discussions.microsoft.com> skrev i melding news:7EBFC106-3E61-4211-9C66-92F29983D5D1@microsoft.com... > I have several OpenOffice.org files that I would like to open in Excel since >...

Problem with Line Chart #2
Hi - I am having problems with my "Line chart with markers" where the "line" between the markers refuse to display. The chart has 3 series displayed in as a stacked column/single column and a 4th series as a line chart with markers. The line between the markers should display as a solid black line, but nothing. Does anyone have any suggestions as to what is causing this problem? Thanks ------------------------------------------------ Message posted from the Excel Tip Forum at http://www.ExcelTip.com/forum/ -- View and post usenet messages directly from http://www.Ex...

Not all column titles show on x axis on chart
I am making a chart and on the x axis only every other month is showing up on the graph. I had this problem the other night and I was able to correct it then but I do not remember what I went under to correct this problem. I need every month to show up on the x axis You need to open the Format Axis dialog for the x-axis (generally, I just right click the axis and select Format Axis for popup menu) and open the Scale tab. Depending on what type of chart you have you need to change the either a) the Major Units setting, or b) Number of categories between tick marks best wishes -- Bernar...

Flags #3
Is there anyway to affect flags for multiple contacts at the same time? - Outllook 2003. -- Guy Lapierre www.forefrontbusinesssolutions.com ...

Setting a Default Value in Excel
How do I set a default value in certain cells? The cells are already validated and I would like to have the default value be one of the validation values. Hi this would be only possible using VBA -- Regards Frank Kabel Frankfurt, Germany "g" <g@discussions.microsoft.com> schrieb im Newsbeitrag news:50F6DF59-2DD0-4696-BF66-D510438EFEF0@microsoft.com... > How do I set a default value in certain cells? The cells are already > validated and I would like to have the default value be one of the > validation > values. What is VBA? "Frank Kabel" wrot...

Percentage value labels on stacked bar chart
Hi Another problem I have with charting. I want to preapre a stacked-bar chart of 5 different values, which is easy enough. However, I also want to show data labels of the percentage of the total stack for each category. When I go intot the data labels options, the percentage one is greyed-out. Does anyone know of a way I can get the percentage values on there, without having to manually type into a text box each time?? Thanks again Dave as answered in .excel.misc: Rob Bovey has a free add-in, the XY Chart Labeler, that you can use to add the labels: http://www.appspro.com/utili...

Error 0x80070057 one or more parameter values are not valid
When I try to send an outlook message to a distribution list of many emails, the message will not send and I get the following error message: !Task '[inbox name] - sending' reported error (0x80070057): 'Could not complete the operation. One or more parameter values are not valid.' Can you help me trouble shoot this? The problem seams to occure when I'm sending via bcc: and when I select multiple names directly from my address book. Thanks in advance for the help, Bob ...

Free iPod 20gb! #3
Hey there, I just found this pretty neat site that is giving away free stuff, such as the new 20GB iPod, a 20.1" wide screen LCD monitor from Dell, a home theater system, a 30GB iPod Photo, or CASH. It is completely legitimate, and thousands of people have received their products from the free sites so far. http://www.tech4free.com/default.aspx?ref=397574 ...

Series Problem
My graph with two series does not match?? Any suggestions. How can u match a series to the x axis - is there any way to manipulate the chart without changing the data??? Thanks - Matt -- Mrinklin ------------------------------------------------------------------------ Mrinklin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25365 View this thread: http://www.excelforum.com/showthread.php?threadid=391125 Hi, Bit thin on detail there. Are you using a line chart so your values are equally spaced on the x axis instead of related to the data? If so use a xy s...

Next Greater Value after Min
I want to find the next greater value after min for "NextOrder". I'm currently using Max but I know there are orders that have a date greater than the Min but less that the Max....can this be done? SELECT CNumber, Min (CreateDate) as FirstOrder, Max (CreateDate) as NextOrder FROM dbo.tblOrder (nolock) Where CreateDate Between '1/1/10' and '5/1/10' and CNumber = '0514' Group By CustomerNumber Qaspec (Qaspec@discussions.microsoft.com) writes: > I want to find the next greater value after min for "NextOrder". I'...

newsgroup problem #3
I have been accessing newsgroups thru MS Outlook for 2 yrs..tried to reinstall MS xp last weekend and ever since then, when i select 'news' in outlook, outlook express opens? i cannot figure out how to change this? Any suggestions would be helpful at this point Thank You Outlook has always used Outlook Express to read news. Nothing different here. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, db asked: | I hav...

chart legends
Is there any easier way to change the order of the way items appear in the legend? They display in the reverse order of the way they appear on the chart. On Thu, 30 Oct 2003 08:35:36 -0800, <anonymous@discussions.microsoft.com= > = wrote: > Is there any easier way to change the order of the way > items appear in the legend? They display in the reverse > order of the way they appear on the chart. Select one data series by clicking on one of the data points. Go to the= = menu and hit Format | Selected data series | Series order tab. Rearrang= e = the data series to th...

Sum filtered values only
Hi, I have filtered a long list of values. Is there a way I can perform calculations only on the filtered values? I want to ignore values which have been hidden after the filter has been applied. Thanks, Bertie. -- claytorm ------------------------------------------------------------------------ claytorm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11610 View this thread: http://www.excelforum.com/showthread.php?threadid=383069 Hi, you can use SUBTOTAL. =SUBTOTAL(9,A1:A100) The 9 is for the sum value others will give you averages, counts etc. HTH...

negative numbers #3
I use Microsoft ME. My version of Excel is 97-2000 How do I show negative numbers in parenthesis? My version does't allow this option. Your version should have it - Format menu/Cells/Number/Accounting HTH, Greg "Keith" <anonymous@discussions.microsoft.com> wrote in message news:006e01c3c004$0c025ef0$a301280a@phx.gbl... > I use Microsoft ME. > My version of Excel is 97-2000 > How do I show negative numbers in parenthesis? > My version does't allow this option. Are you sure, I had that option built in for both versions. Regardless you could use a custom ...

Exchange 2000 #3
I have a SBS 2000 box with exchange server 2000 running on a company network. Dns is external at another company (yes I know, i don't like it either) The dns server has one ip address, which is being used for another domain right now. They want me to point an mx record at them so they can get their mail server running (SMTP and pop3). What I have done is point an mx at mail.companyname.com, and then added the host mail, which points to their ip address. I think this should work, am I correct? Does anyone see any problems with this? It is not working yet, but I have to check the NAT and all...

Can I find the last value in a column of cells?
Hi - I have a column of cells that is fixed length but the entries within it are not fixed, some columns might have more or less entries than another column. I want to find the bottom entry in the list, regardless of what row that entry is in and return the value of that entry please? eg, all columns have 12 cells, col A has 3 entries, col B has 1, col C has 6 etc. The entries are NOT in value order and cannot be sorted but each column is independant of the others, if that helps. TIA Chris Try this where 99999 is larger than ANY number possible =INDEX(E:E,MATCH(99999,E:E...

CRM 3 on SBS2003
I have a new server running SBS2003 and want to know if I will be able to run CRM 3 on the same machine. We are planning to use CRM 3 as soon as it comes out. Our server is a P4 3.2GHz, 2GB RAM, two 120GB SATA drives with RAID 1. We have less than ten workstations. Matt, you'll need SP1 for SBS2003. I did it successfully. Here are some of the recommendations for the hardware requirements: Processor Dual Intel Pentium Xeon PIII 700 MHz or comparable single Dual 1.8 GHz (Xeon P4) or comparable single CPU speeds Memory 512 MB of RAM 1 GB of RAM or more, depending on the system configu...

Problem on MS CRM 3.0 Reinstall
Dear All When I Try to reinstall CRM on another Server. I select same SQL(SQL2k Sp4) server and select connect to existing Database, I got the error message( The existing database are not compatible with this installation of CRM).so that I select Creat New Database, but CRM setup system requirement check reprot the following databases already exist on the specifisd SQL server. Whan can I do? Thank you so much for your help! Best regards Beetrood Wong what version of CRM? When you re-installed, is it possible you entered a different organization name from the original? "Beetr...

3 macros in 1 button?
I want 3 different macros to run, but I want the user to be able to press 1 button only. Do I have to nest them within each other? Joe Sub Macro3() Rows("3:2000").Select Selection.Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ActiveWindow.ScrollRow = 3 Range("B5").Select End Sub Public Sub DeleteBlankRows() Dim R As Long Dim C As Range Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ...

progressive totals in a query for a chart
I would like to be able to create progressive totals in a query for display on a chart. The total required only has entries every few months while remaining data appears daily. is this possible? ...

Interactive XY charts where data are in different number formats
I posted this question earlier but, in spite of Tushar Mehta's valiant efforts, I was unable to resolve my problem. My data are organized differently than in the examples cited on his web page and my knowledge of the OFFSET and COUNTA functions simply proved to be too spotty. So, if I may burden you again: I have an array of data organized as follows: Response 1 2 3 4 5 Data in cells A4:F19: Probability 80% 70% 65% 75% 85% Number of Customers 115 185 100 160 190 Selling price $2.15 $1.85 $2.00 $2.30 $2....

Hide the tab with the data associated with a chart
I would like to forward a file with charts however I do not want to include the tabs that have the data. If I delete the worksheet with the data I lose my chart information. Any ideas or solutions without cutting a pasting the chart to a word document Two ideas: 1. You could copy and paste the image. 2. Change the font color for the data so that it's white on white On Aug 1, 11:14 am, Irene OL <Irene O...@discussions.microsoft.com> wrote: > I would like to forward a file with charts however I do not want to include > the tabs that have the data. If I delete the w...

CRM 3.0 Problem report setup
Hello I am exerienceing problems with a CRM 3.0 setup : reports are not available config is 1) CRM server ( also a domain controller I know it is not good but no choice) win 2003 SRVR name HTC-DATA ( I know the - is not recommended but was already there) working fine . CRM web client and outlook client OK Installed by domain admin + no host header + no https + Network services running CRm services 2) SQL server + report (SQL SRVR 2000) HTC-sharepoint Also a sharepoint 2003 portal (again no other option) The application pool runing the root default web service is specific to sharepoint bu...

Visio Org Chart from Exchange
Hi, Apologies if this is the wrong group for a Visio query, but I cannot seem to find any Technet newsgroups specifically for Visio. I am running Visio 2007 Professional and Exchange 2003 std SP2. I am trying to create an org chart from Exchange data with the 'import org data' wizard in Visio and choosing Exchange server directory. The directory is populated and users have managers set in AD, these show as manger and direct reports in the global address book. When I complete the wizard, only the user I have chosen to be at the top of the page is shown. No subordinates are show...

From Rows to Columun #3
I have data in rows, I want to past to columns so i can import intio access... HELP Example (Current data) 3 42 42 3 38 38 4 22 I want to move to 3 42 42 3 38 38 4 22 THANKS ...