• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Displaying data on dynamic chart X axis

C

comp764

Member
Hello all,

First, I would like to thank everyone in this forum for help I have received here. Right now I have six charts setup showing statistical QC data for one product. temperature, PH, Viscosity, Solids, centipoise, and age centipoise.

What I would like to do, is to show the batch# or PO# column info in the x axis.


This way when I hover the mouse over a data point it will show either one instead of show point 1,2,3 etc. I have pasted a link to the spreadsheet below.

Thanks

https://www.dropbox.com/s/gz5bv79sdpzf936/Product1QC.xlsx
 
Comp764

If you select say the Ph Readings Chart

Then select the actual PH series, Series 1

In the formula bar change the formula from

Code: 
=SERIES(PRODUCT1!$F$1,,'Product1QC-1.xlsx'!PRODUCT1_Ph,1)

to

=SERIES(PRODUCT1!$F$1,PRODUCT1!$D$2:$D$399,'Product1QC-1.xlsx'!PRODUCT1_Ph,1)


The X Axis will now show the Batch No. as will hovering the mouse over a data point
 
Thanks Hui,

But the data points are showing last entries and the Batch No. are starting from the first entries. For example, if I input the number 10 in See last which is D1 in the charts tab It will show me the last ten data entries I made. The batch numbers are showing the 1st ten entries. Is there anyway to have the data point match with the correct batch number.


Thanks
 
Of course


Add a new Named Formula

Product1_Batch
Code: 
=OFFSET(PRODUCT1_Ph,,-2)

then change the formula for the Series line as described above to be:

=SERIES(PRODUCT1!$F$1,Product1QC.xlsx!Product1_Batch,Product1QC.xlsx!PRODUCT1_Ph,1)
 
Can't get formula below to work. Keeps saying invalid reference.

=SERIES(PRODUCT1!$F$1,Product1QC.xlsx!Product1_Batch,Product1QC.xlsx!PRODUCT1_Ph,1)
 
You have to make the Named Formula "Product1_Batch" first


Here is your file with the modification on the Ph Chart

https://www.dropbox.com/s/aagi3behsmz5kr5/Product1QC_Hui.xlsx
 
Thanks Hui,

I was putting it into the Y axis that was it wasn't working.


Thank you very much for your help.
 
Hello Hui,

The the suggestions above seen to work on the modified excel sheet I posted. But I can't get it to work on the original one, which I posted a link below. Not sure what I doing wrong.

Thanks


https://www.dropbox.com/s/gogqt8bzlkakxgt/BOURB515.xlsx
 
Comp764


Using the BOURB515CHART as the example for this


The Top Left Bourbonnais S-515 Ph Readings chart has the data series as: H430_Ph


so the relevent Batch Named Formula should be

H430_Batch:
Code: 
=OFFSET(H430_Ph,,-2)


then change the series formula to:

=SERIES(BOURB515!$F$1,BOURB515.xlsx!H430_Batch,BOURB515.xlsx!H430_Ph,1)


Same for the other worksheets
 
Fantastic Hui!

Everything work out great. Now I understand how it works! Thank you so much for your help. I really appreciate all your help.


I do have one more question for you. I have a Average formula you wrote, which I'm using on my charts. The formula name is Bourb515_CPSAvg using it on the S-515 CPS Chart in the Bourb515Chart tap. The formula is not showing an average. I'm wondering if it's because the CPS column has the following formula =IF(G2>0,G2*400,NA())and it's detecting the formula and not the values.

Thanks again for all your help.


https://www.dropbox.com/s/gogqt8bzlkakxgt/BOURB515.xlsx
 
Sorry, I forgot to show you the formula I'm using.


=(ROW(OFFSET(BOURB515!$A$1,,,BOURB515CHART!$D$1,1))^0) * AVERAGE(OFFSET(BOURB515!$H$2, MAX(0, COUNTA(BOURB515!$H:$H) -BOURB515CHART!$D$1 - 1), 0,BOURB515CHART!$D$1, 1))
 
You must log in or register to reply here.
Back
Top