PDA

View Full Version : Creating a dynamic piechart



Aussiebear
02-04-2017, 06:22 PM
I am trying to create a dynamic pie chart to reflect the amount of ground covered by a centre pivot irrigator for a given period. Given that I know the diameter of the centre pivot, the travel speed and the startup time, I can then calculate the circumference, the total time to run and the estimated finish time.


Given that the irrigator is 400m long, the circumference is 2513.27m. Travel speed is 1.7m/ min or 102m/hr. Total time to run is Circumference/Speed per hr = 24.64 hrs (24hrs 38mins). If I start the irrigator at 6:30am today it should finish at 7:08am tomorrow. So far so good.

I don't know if this is the right method or not but I was thinking of using elapsed time and converting it to a percentage of the total time. Is there a better method?

Second question is, If I started the irrigator at say 6:30 but didn't enter it until 10:30 the pie chart needs to show that 4 hours has passed of the total time. And the pie chart needs to be dynamic (live).

Paul_Hossler
02-04-2017, 07:00 PM
1. How dynamic? Move like clock hands, or just show the sectors?

2. B2 should be =PI()*B1 since the circumference = Pi x Diameter, or = Pi x 2 x Radius

3. Pie charts are usually slices of 100%, so depending on what you want to see it could work

p45cal
02-04-2017, 07:27 PM
See attached.
To update the chart, just recalculate the sheet - which could be done with an ontime macro automatically.
To adjust the start time, adjust D7.

Aussiebear
02-04-2017, 08:24 PM
Thank you P45cal. Live charting if possible, so that you could leave the workbook on the screen and the Value are continuing to adjust.

p45cal
02-05-2017, 04:55 AM
OnTime macro included in the attached. Currently set to update every 7 seconds. Adjust this line to alter that:
NextScheduledCalculation = Now + TimeValue("00:00:07")
It should survive a (save and) close and reopening of the file as far as progress is concerned, but not as far as automatic updating is concerned (you'd have to re-start automatic updating I think).
Green cells are for the user to enter data, other highlighted cells are calculated.
There's a commented-out update macro to calculate the sheet on the sheet's activation, if you want to enable it.

Paul_Hossler
02-05-2017, 07:01 AM
Nice

Aussiebear
02-05-2017, 04:32 PM
I'd like it to stop updating once the finish time has been reached, as currently using the example its now 115.71% and the piechart shows a sector to do of 15.71 which is incorrect

p45cal
02-06-2017, 07:05 AM
Aahh…
In the attached, I haven't stopped it updating after 1 rotation, instead I let it carry on but the colours change:
During the first pass the ground colour starts brown and as irrigation continues it turns light green.
After the end of the first rotation, the colour of the covered ground changes to a darker (supposed to be lusher) green, and as more rotations take place the ground gets a darker green again for some 7 or 8 passes, whereupon it reverts to brown and light green again.
The number of completed rotations is indicated, as is the percentage coverage of the current rotation.
The actual colours used are hidden in cells behind the chart which you can adjust to please your eyes as you want. If you put a start date in the future, unusual colours appear (light blue to start with), don't (although the colours will right themselves as time passes).

Aussiebear
02-06-2017, 08:35 PM
Thank you P45cal