Formulas in a series for Charts

R

robbbo

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have a chart that is driven off a simple table that is filled in by the user. That table has up to 13 data points, but in most cases the table will only have 5 to 8 data points. If they have 8 the remaining data will be zero. I would like to chart to scale automatically to only have 8 points not all 13, so if I put a "If" formula in the series that plot the chart I can automatically control the amount of data point plotted. The series look like this: =SERIES('TREND ANALYSIS'!$B$24,'TREND ANALYSIS'!$C$10:$O$10,'TREND ANALYSIS'!$C$24:$O$24,1). I would like to replace the "O" with (if(O24=0,N,O)$24)

=SERIES('TREND ANALYSIS'!$B$24,'TREND ANALYSIS'!$C$10:$(if(O24=0,N,O)$24)$10,'TREND ANALYSIS'!$C$24:$(if(O24=0,N,O)$24)$24,1)

When I do this there is an error. Can I do this?
 
C

Carl Witthoft

It's not easy, but it can be done. You need to set up Names to
reference regions in the spreadsheet and define the graph series in
terms of those names. Then a lot of work with INDIRECT and CONCATENATE
to redefine those names automatically may do the job.

Take a look at OATBRAN (google it) for some examples which may help.
 
R

robbbo

Hi Carl,

The searched for OATBRAn and found this site:

http://www.coventry.ac.uk/ec/~nhunt/oatbran/

It does not seem to show an example of what you talked about or help me with my issue. Is this the correct site??

Bob
It's not easy, but it can be done. You need to set up Names to
> reference regions in the spreadsheet and define the graph series in
> terms of those names. Then a lot of work with INDIRECT and CONCATENATE
> to redefine those names automatically may do the job.
>
> Take a look at OATBRAN (google it) for some examples which may help.
>
> In article ,
> [email protected] wrote:
>
> > Version: 2008
> > Operating System: Mac OS X 10.5 (Leopard)
> > Processor: Intel
> >
> > I have a chart that is driven off a simple table that is filled in by the
> > user. That table has up to 13 data points, but in most cases the table will
> > only have 5 to 8 data points. If they have 8 the remaining data will be zero.
> > I would like to chart to scale automatically to only have 8 points not all
> > 13, so if I put a "If" formula in the series that plot the chart I
> > can automatically control the amount of data point plotted. The series look
> > like this: =SERIES('TREND ANALYSIS'!$B$24,'TREND ANALYSIS'!$C$10:$O$10,'TREND
> > ANALYSIS'!$C$24:$O$24,1). I would like to replace the "O" with
> > (if(O24=0,N,O)$24) =SERIES('TREND ANALYSIS'!$B$24,'TREND
> > ANALYSIS'!$C$10:$(if(O24=0,N,O)$24)$10,'TREND
> > ANALYSIS'!$C$24:$(if(O24=0,N,O)$24)$24,1) When I do this there is an
> > error. Can I do this?
Click to expand...
>
> --
> Team EM to the rescue! http://www.team-em.com
>
Click to expand...
 
R

robbbo

Hi Carl,

Thanks so much for your help, but I still do not see what you have shown me fixes my problem. Let me restate my problem in another way to make sure you understand. I have a blank table that I supply to people to fill out. The table looks like the following where "xxx" are numbers:

A B C D E F G H
Data xxx xxx xxx xxx xxx xxx xxx xxx

My two cases are as follows:

Case 1
        A B C D E F G H
Data 190 200 150 250 300 400 350 375

Case 2
        A B C D E F G H
Data 195 210 160 240 310 NA NA NA

I plot the data in the table to a chart of A through H vs data. In case 1 everything works well, but in case 2 the user only has data for A through E. If I use the same series formulas in the charts for both cases, the case 2 has the chart plot F, G and H as a zero and the chart does not look right. What I want to do is build into the chart series a functional way of detecting the NAs in Case 2 and only plotting A through E truncating F through H. In my real case NA could be zeros as well.

Another drawback to they way I currently have the plotting working is that in Case 1 the charting program automatically scales the data from 195 being the lowest value to 400 being the highest value so you have a nice dynamic range. In case 2 the lowest value is now "0" even though there are no values in F, G, and H so the dynamic range is reduced considerably.

The actual series that plots this looks like:

=SERIES(Sheet1!$A$2,Sheet1!$B$1:$I$1,Sheet1!$B$2:$I$2,1)

In case 2 I want $I to be $F as $G, $H, and $I contain no data, so I am looking to make $I a variable or formula. Please note Data A is in column "B" of the workbook shifted by one letter.

I hope this is clearer. If you feel what you have sent me addresses this case, could you help clarify how?

Thanks again,

Bob
That is the URL I was thinking of. Sorry if none of their graphs
> suggest solutions for your case.
>
> see if a couple of my toys help:
> http://home.comcast.net/~cgwcgw/SelectData.xls.zip should do exactly
> what you want.
>
>
> In article ,
> [email protected] wrote:
>
> > Hi Carl, The searched for OATBRAn and found this site: > href="http://www.coventry.ac.uk/ec/~nhunt/oatbran/">http://www.coventry.ac.uk/
> > ec/~nhunt/oatbran/ It does not seem to show an example of what
> > you talked about or help me with my issue. Is this the correct site??
> > Bob > It's not easy, but it can be done. You need to set up
> > Names to
> > > reference regions in the spreadsheet and define the graph series in
> > > terms of those names. Then a lot of work with INDIRECT and CONCATENATE
> > >
> > > to redefine those names automatically may do the job.
> > >
> > > Take a look at OATBRAN (google it) for some examples which may help.
> > >
> > > In article ,
> > > [email protected] wrote:
> > >
> > > > Version: 2008
> > > > Operating System: Mac OS X 10.5 (Leopard)
> > > > Processor: Intel
> > > >
> > > > I have a chart that is driven off a simple table that is filled in by the
> > > >
> > > > user. That table has up to 13 data points, but in most cases the table
> > > > will
> > > > only have 5 to 8 data points. If they have 8 the remaining data will be
> > > > zero.
> > > > I would like to chart to scale automatically to only have 8 points not
> > > > all
> > > > 13, so if I put a "If" formula in the series that plot the
> > > > chart I
> > > > can automatically control the amount of data point plotted. The series
> > > > look
> > > > like this: =SERIES('TREND ANALYSIS'!$B$24,'TREND
> > > > ANALYSIS'!$C$10:$O$10,'TREND
> > > > ANALYSIS'!$C$24:$O$24,1). I would like to replace the "O" with
> > > >
> > > > (if(O24=0,N,O)$24) =SERIES('TREND ANALYSIS'!$B$24,'TREND
> > > > ANALYSIS'!$C$10:$(if(O24=0,N,O)$24)$10,'TREND
> > >
Click to expand...
Click to expand...
Click to expand...
 
R

robbbo

Hi Carl,

When I generated this last post everything was spaced correctly, but it appears this forum'a text editor removed my spaces, So to clarify, each 3 digit number is under a separate letter in case 1 190 is under A, 200 is under B, and 150 is under C and so forth. Hope this helps.

Bob
Hi Carl,
>
> Thanks so much for your help, but I still do not see what you have shown me fixes my problem. Let me restate my problem in another way to make sure you understand. I have a blank table that I supply to people to fill out. The table looks like the following where "xxx" are numbers:
>
> A B C D E F G H
> Data xxx xxx xxx xxx xxx xxx xxx xxx
>
> My two cases are as follows:
>
> Case 1
> A B C D E F G H
> Data 190 200 150 250 300 400 350 375
>
> Case 2
> A B C D E F G H
> Data 195 210 160 240 310 NA NA NA
>
> I plot the data in the table to a chart of A through H vs data. In case 1 everything works well, but in case 2 the user only has data for A through E. If I use the same series formulas in the charts for both cases, the case 2 has the chart plot F, G and H as a zero and the chart does not look right. What I want to do is build into the chart series a functional way of detecting the NAs in Case 2 and only plotting A through E truncating F through H. In my real case NA could be zeros as well.
>
> Another drawback to they way I currently have the plotting working is that in Case 1 the charting program automatically scales the data from 195 being the lowest value to 400 being the highest value so you have a nice dynamic range. In case 2 the lowest value is now "0" even though there are no values in F, G, and H so the dynamic range is reduced considerably.
>
> The actual series that plots this looks like:
>
> =SERIES(Sheet1!$A$2,Sheet1!$B$1:$I$1,Sheet1!$B$2:$I$2,1)
>
> In case 2 I want $I to be $F as $G, $H, and $I contain no data, so I am looking to make $I a variable or formula. Please note Data A is in column "B" of the workbook shifted by one letter.
>
> I hope this is clearer. If you feel what you have sent me addresses this case, could you help clarify how?
>
> Thanks again,
>
> Bob
>
> > That is the URL I was thinking of. Sorry if none of their graphs
> > suggest solutions for your case.
> >
> > see if a couple of my toys help:
> > http://home.comcast.net/~cgwcgw/SelectData.xls.zip should do exactly
> > what you want.
> >
> >
> > In article ,
> > [email protected] wrote:
> >
> > > Hi Carl, The searched for OATBRAn and found this site: > href="http://www.coventry.ac.uk/ec/~nhunt/oatbran/">http://www.coventry.ac.uk/
> > > ec/~nhunt/oatbran/ It does not seem to show an example of what
> > > you talked about or help me with my issue. Is this the correct site??
> > > Bob > It's not easy, but it can be done. You need to set up
> > > Names to
> > > > reference regions in the spreadsheet and define the graph series in
> > > > terms of those names. Then a lot of work with INDIRECT and CONCATENATE
> > > >
> > > > to redefine those names automatically may do the job.
> > > >
> > > > Take a look at OATBRAN (google it) for some examples which may help.
> > > >
> > > > In article ,
> > > > [email protected] wrote:
> > > >
> > > > > Version: 2008
> > > > > Operating System: Mac OS X 10.5 (Leopard)
> > > > > Processor: Intel
> > > > >
> > > > > I have a chart that is driven off a simple table that is filled in by the
> > > > >
> > > > > user. That table has up to 13 data points, but in most cases the table
> > > > > will
> > > > > only have 5 to 8 data points. If they have 8 the remaining data will be
> > > > > zero.
> > > > > I would like to chart to scale automatically to only have 8 points not
> > > > > all
> > > > > 13, so if I put a "If" formula in the series t
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Greenblatt

Hi Carl,

Thanks so much for your help, but I still do not see what you have shown me
fixes my problem. Let me restate my problem in another way to make sure you
understand. I have a blank table that I supply to people to fill out. The
table looks like the following where "xxx" are numbers:

A B C D E F G H
Data xxx xxx xxx xxx xxx xxx xxx xxx

My two cases are as follows:

Case 1
A B C D E F G H
Data 190 200 150 250 300 400 350 375

Case 2
A B C D E F G H
Data 195 210 160 240 310 NA NA NA

I plot the data in the table to a chart of A through H vs data. In case 1
everything works well, but in case 2 the user only has data for A through E.
If I use the same series formulas in the charts for both cases, the case 2 has
the chart plot F, G and H as a zero and the chart does not look right. What I
want to do is build into the chart series a functional way of detecting the
NAs in Case 2 and only plotting A through E truncating F through H. In my real
case NA could be zeros as well.

Another drawback to they way I currently have the plotting working is that in
Case 1 the charting program automatically scales the data from 195 being the
lowest value to 400 being the highest value so you have a nice dynamic range.
In case 2 the lowest value is now "0" even though there are no values in F, G,
and H so the dynamic range is reduced considerably.

The actual series that plots this looks like:

=SERIES(Sheet1!$A$2,Sheet1!$B$1:$I$1,Sheet1!$B$2:$I$2,1)

In case 2 I want $I to be $F as $G, $H, and $I contain no data, so I am
looking to make $I a variable or formula. Please note Data A is in column "B"
of the workbook shifted by one letter.

I hope this is clearer. If you feel what you have sent me addresses this case,
could you help clarify how?

Thanks again,

Bob
Click to expand...
Don¹t use NA, use #N/A, or the formula =na(). These values will not plot.
 
R

robbbo

System Problem
Hi Carl,
>
> When I generated this last post everything was spaced correctly, but it appears this forum'a text editor removed my spaces, So to clarify, each 3 digit number is under a separate letter in case 1 190 is under A, 200 is under B, and 150 is under C and so forth. Hope this helps.
>
> Bob
>
> > Hi Carl,
> >
> > Thanks so much for your help, but I still do not see what you have shown me fixes my problem. Let me restate my problem in another way to make sure you understand. I have a blank table that I supply to people to fill out. The table looks like the following where "xxx" are numbers:
> >
> > A B C D E F G H
> > Data xxx xxx xxx xxx xxx xxx xxx xxx
> >
> > My two cases are as follows:
> >
> > Case 1
> > A B C D E F G H
> > Data 190 200 150 250 300 400 350 375
> >
> > Case 2
> > A B C D E F G H
> > Data 195 210 160 240 310 NA NA NA
> >
> > I plot the data in the table to a chart of A through H vs data. In case 1 everything works well, but in case 2 the user only has data for A through E. If I use the same series formulas in the charts for both cases, the case 2 has the chart plot F, G and H as a zero and the chart does not look right. What I want to do is build into the chart series a functional way of detecting the NAs in Case 2 and only plotting A through E truncating F through H. In my real case NA could be zeros as well.
> >
> > Another drawback to they way I currently have the plotting working is that in Case 1 the charting program automatically scales the data from 195 being the lowest value to 400 being the highest value so you have a nice dynamic range. In case 2 the lowest value is now "0" even though there are no values in F, G, and H so the dynamic range is reduced considerably.
> >
> > The actual series that plots this looks like:
> >
> > =SERIES(Sheet1!$A$2,Sheet1!$B$1:$I$1,Sheet1!$B$2:$I$2,1)
> >
> > In case 2 I want $I to be $F as $G, $H, and $I contain no data, so I am looking to make $I a variable or formula. Please note Data A is in column "B" of the workbook shifted by one letter.
> >
> > I hope this is clearer. If you feel what you have sent me addresses this case, could you help clarify how?
> >
> > Thanks again,
> >
> > Bob
> >
> > > That is the URL I was thinking of. Sorry if none of their graphs
> > > suggest solutions for your case.
> > >
> > > see if a couple of my toys help:
> > > http://home.comcast.net/~cgwcgw/SelectData.xls.zip should do exactly
> > > what you want.
> > >
> > >
> > > In article ,
> > > [email protected] wrote:
> > >
> > > > Hi Carl, The searched for OATBRAn and found this site: > href="http://www.coventry.ac.uk/ec/~nhunt/oatbran/">http://www.coventry.ac.uk/
> > > > ec/~nhunt/oatbran/ It does not seem to show an example of what
> > > > you talked about or help me with my issue. Is this the correct site??
> > > > Bob > It's not easy, but it can be done. You need to set up
> > > > Names to
> > > > > reference regions in the spreadsheet and define the graph series in
> > > > > terms of those names. Then a lot of work with INDIRECT and CONCATENATE
> > > > >
> > > > > to redefine those names automatically may do the job.
> > > > >
> > > > > Take a look at OATBRAN (google it) for some examples which may help.
> > > > >
> > > > > In article ,
> > > > > [email protected] wrote:
> > > > >
> > > > > > Version: 2008
> > > > > > Operating System: Mac OS X 10.5 (Leopard)
> > > > > > Processor: Intel
> > > > > >
> > > > > > I have a chart that is driven off a simple table that is filled in by the
> > > > > >
> > > > > > user. That table has up to 13 data points, but in most cases the table
> > > > > > will
> > > > > > only have 5 to 8 data points. If they have 8 the remaining data will be
> > > > > > z
Click to expand...
Click to expand...
Click to expand...
 
Top