Windows 7 - Formulas in a series for Charts

Asked By robbb

05-Feb-10 09:52 PM

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) <br><br>=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) <br><br>When I do this there is an error. Can I do this?

07-Feb-10 04:52 PM

it is 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 <59bb2582.-1@webcrossing.JaKIaxP2ac0>,


--
Team EM to the rescue!    http://www.team-em.com

08-Feb-10 12:10 PM

Hi Carl, <br><br>The searched for OATBRAn and found this site: <br><br><a href="http://www.coventry.ac.uk/ec/~nhunt/oatbran/">http://www.coventry.ac.uk/ec/~nhunt/oatbran/</a> <br><br>It does not seem to show an example of what you talked about or help me with my issue. Is this the correct site?? <br><br>Bob <br><br>> it is not easy, but it can be done.  You need to set up Names to  <br>

08-Feb-10 06:59 PM

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 <59bb2582.1@webcrossing.JaKIaxP2ac0>,


--
Team EM to the rescue!    http://www.team-em.com

09-Feb-10 01:32 PM

Hi Carl, <br><br>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: <br><br>         A    B    C    D    E    F    G    H <br>
Data    xxx  xxx  xxx  xxx  xxx  xxx  xxx  xxx <br><br>My two cases are as follows: <br><br>Case 1 <br>
        A    B    C    D    E    F    G    H <br>
Data   190  200  150  250  300  400  350  375 <br><br>Case 2 <br>
        A    B    C    D    E    F    G    H <br>
Data   195  210  160  240  310   NA   NA   NA <br><br>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.  <br><br>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. <br><br>The actual series that plots this looks like: <br><br>=SERIES(Sheet1!$A$2,Sheet1!$B$1:$I$1,Sheet1!$B$2:$I$2,1)  <br><br>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. <br><br>I hope this is clearer. If you feel what you have sent me addresses this case, could you help clarify how? <br><br>Thanks again, <br><br>Bob <br><br>> That is the URL I was thinking of.  Sorry if none of their graphs  <br>

09-Feb-10 01:36 PM

Hi Carl, <br><br>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. <br><br>Bob <br><br>> Hi Carl, <br>

09-Feb-10 04:32 PM

this format, some or all of this message may not be legible.

--B_3348577967_5296176
Content-type: text/plain;
charset="ISO-8859-1"
Content-transfer-encoding: 8bit

On 2/9/10 1:32 PM, in article 59bb2582.3@webcrossing.JaKIaxP2ac0,

Don?t use NA, use #N/A, or the formula =na().  These values will not plot.
--
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom


--B_3348577967_5296176
Content-type: text/html;
charset="ISO-8859-1"
Content-transfer-encoding: quoted-printable

in article <a href=3D"59bb2582.3@webcrossing.JaKIaxP2ac0">59bb2582.3@webcrossi=
ng.JaKIaxP2ac0</a>, "<a href=3D"robbbo@officeformac.com">robbbo@officefor=
mac.com</a>" <<a href=3D"robbbo@officeformac.com">robbbo@officeformac.=
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 t=
able looks like the following where "xxx" are numbers: <BR>
         A    B=
   C    D    E   &nb=
sp;F    G    H <BR>
Data    xxx  xxx  xxx  xxx  xxx  xx=
x  xxx  xxx <BR>
My two cases are as follows: <BR>
Case 1 <BR>
        A    B  =
;  C    D    E    F &=

10-Feb-10 02:04 PM

System Problem <br><br>> Hi Carl, <br>

10-Feb-10 02:06 PM

Bob, <br><br>Actually the NA are set to zero in the cells.  <br><br>Thanks, <br><br>Bob <br><br>>  <br>