Log In
Register
Home
Active Member
June 3, 2020
Offline
Hi,
I am looking to animate a chart in excel that shows the change in potential (volts) of a pipeline over time at test points along the pipeline length. So the x asis will be the KP or kilometre point (fixed) from the start of the pipeline and the y axis will be the pipeline potential (variable). I data log the pipeline potential over a 24 hour period sampling once every 30 seconds so roughly 2800 reads and can have up to 30 test locations along the pipeline section that I install data loggers at.
I imagine the animation will take probably ~2 mins to show the 24 hour logging period so the frequency of each read in the animation would be ~0.05 seconds? No idea how to do that or whether that is achievable.
I've attached a sample sheet for reference.
Would be great to hear about any easy way to do this.
Cheers
PS pipeline potential is supposed to be negative
See Answer
October 5, 2010
Offline
Hi Alistair,
See attached file.
I've made the potential positive so the bars are above the x-axis.
This requires Office 365 as it uses dynamic arrays. Not sure what version of Excel you have.
regards
Phil
Answers Post
Active Member
June 3, 2020
Offline
Hi Phil,
That is exactly what I am after - thank you.
The question now becomes how can I replicate this going forward with any new data I get or use it on old data I have? I'd like to understand how to do it myself so happy to hear how we go about this and costs etc.
Cheers,
Alistair
October 5, 2010
Offline
Hi Alistair,
What is your experience of VBA? Can you look through my code and see what it is doing?
It boils down to:
1) Reading a row of data and copying that to O4
2) The formula in O2 then multiplies that row by -1 to make it positive and the results are in O2:Z2
3) The chart just plots the data in O2:Z2
4) Go to 1.
If you need to add or remove data points (sensor readings) then you'll have more/less columns so adjusting the chart and steps 1-3 should be straight forward.
Regards
Phil
Active Member
June 3, 2020
Offline
Hi Phil,
Have been able to do that so much appreciated - thank you.
Silly question time:
- is there a way to stop the animation before the end time?!
- can I move the chart to its own sheet instead of imbedded in the sheet as is now?
Cheers,
Alistair
October 5, 2010
Offline
Hi Alistair,
Yes you can stop the animation. I've added a stop button and a new sub that stops it.
To move the chart, select it, cut, paste to new sheet.
I've had to rewrite the code a little to take this into account and if you move the chart you will need to make sure the VBA is referencing the sheet where the chart is.
Regards
Phil
1 Guest(s)
