Mac Forum / Applications / Excel / February 2010

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 ,
> robbbo@officeformac.com 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?
>
> --
> Team EM to the rescue! http://www.team-em.com
>

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 ,
> robbbo@officeformac.com 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 ,
> > > robbbo@officeformac.com 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
> > >

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 ,
> > robbbo@officeformac.com 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 ,
> > > > robbbo@officeformac.com 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

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 ,
> > > robbbo@officeformac.com 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 ,
> > > > > robbbo@officeformac.com 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

Bob,

Actually the NA are set to zero in the cells.

Thanks,

Bob

>
> On 2/9/10 1:32 PM, in article 59bb2582.3@webcrossing.JaKIaxP2ac0, "robbbo@officeformac.com" wrote:
>
> 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
> Don’t use NA, use #N/A, or the formula =na().  These values will not plot.
> --
> Bob Greenblatt [MVP], Macintosh
> bobgreenblattATmsnDOTcom
>
>

Reply to this Message