Hi,


I am with four SIMPLE data sets, TIME against Temperature, see attached, one per sheet, and wish to see the plots on one graph, how is this done ?

I didnt know if all data had to be on one sheet for such.

Furthermore I have two columns as ON or OFF, the ON time being in colour, so a line parallel to time X axis with gaps is envisaged, should the data be 0 and 1 or YES and NO or ON and OFF ?

Time along X, Temperature on Y
the ON OFF to be a gappy line parallel to X,
Different colour lines for each Sheets temperatures and data.


I also have some data for the evo home systems 'seen' temperature and target it was aiming at. I need to add these in using photoshop, The Y axis must have a second set of values 14deg C to 28C to do so, hence use photoshop as this otherwise is getting to be mental overload !
Though I see one can have a second set of values on the right hand side Y axis, so it is possible.

I was unable to monitor the achieved temperatures whilst standing at boiler anyway., so at best I have achieved room temperature at start and end of the session.

I need a standard timeline along X axis , same value progression for all sheets. and the time values take up their appropriate positions on that timeline, as opposed to each entry has its time and each are 1 unit apart, that would give a false graph line as it could see 5 entries 10 secs between them take up same width as 5 with 6 minutes between them !


The Y axis must have the same spacing between degrees Centigrade for all graphs, if they have to be created separately.



As such then I can overlay them in Photoshop and axes all match.


Could someone create the graph with the attached excel2013 data and post result for opening in Excel 2013. That way perhaps I can see how its made, and indicate the steps taken, method etc.


If its easier to make one graph per sheet/day, I then use photoshop to set them over each other. Axes must as said match for all.


The ON OFF data is one area I havent a clue on, as at start of post.


Columns to take data from are:-
Time SW (stop watch) (00:00:00 format)
Boiler temp 56 57 58 65 etc
Flame ON OFF
Motor ON OFF


I have a boiler that has a mind of its own, one day room takes 6 hrs to attain 22deg C, another day its far quicker, due to temps of 85 max on boiler and not the inadequate 65 max.
Yet I am told Boiler is fine.

My attempt to see what is going on.

here is the data


Cheers


DBenz

Attached Files

Worcester Bosch Boiler Data Log suss heating problems forum.zip (18.7 KB, 1 views)

Messing around.
In the attached:
Sheet2: Chart and slicer at cell B2. Select the date(s) you want to see in the slicer.
Sheet1: Pivot table that the chart is based upon. That pivot table is based on the data in the first 5 columns of that sheet BUT not directly; that data has been through a Power Query whech feeds the pivot.

Attached Files

MSOfficeForums50114Worcester Bosch Boiler Data Log suss heating problems forum.xlsx (70.5 KB, 5 views)

Hi,

OMG awesome. I wish I knew how to do that.
I like the flame and motor displays.
THANK YOU THANK YOU THANK YOU



and now can this tweak be done ?


This post by you has missed an update I made before it appeared.


I need to see the graphs against a normal time progression on X axis, 1cm being e.g. 5 mins increment for each days graph. I can then overlay them in Photoshop and X matches. (Y also to be same progression, it already is)



With the observations on stopwatch getting plotted as e.g. 00:10 to 00:12 in same distance along X axis as 00:23 to 00:30, (2mins progression same as 7 mins on X axis disyance ) it disallows one being compared regards time flow to another.


I had edited post (perhaps you didnt see it) to ask for a standard timeline where e.g. 1cm was 5 mins, and the stop watch times get allotted the appropriate position, without times shown, just graph.


A friend says to do that the rows would have to have 1 second increments so 30mins observation period would be 30x60 = 1800 rows and the observations placed on the matching rows for the time so 1m30sec or 00:01:30 would be on the 90th row.
hate to think what a 5hr observation would be.


Is that what is needed ? or can Excel look at a time and place it on a standard time progression X axis ?



The rows, many not having entries for deg C, are then plotted on the graph so if graph is 200mm wide each row would be 0.11mm along the graph. Not sure if plots can be that close. Certainly the SW time would not appear, but is not necessary as the timeline would be 0 5 10 15 20 etc mins marked.


DBenz

Hi,

a friend has tried to get this to be on a standard timeline, as mentioned, and attached is the result.
I have had to get it into photoshop and draw in vertical lines every 5 mins.
I need though the flame on and motor on data also into the graph.
he says alter the formula for the temperature column to read flame on, I now see 1 and 0, but col H still has temperature.

Not sure how BCDE gets into HIJK
doing this will obviously remove the dots ferom the graph.


I also had to use photoshop to put lines between the dots !



Help !


DBenz

Attached Images

Worcester Bosch Boiler temperature v flame and motor data photoshop.jpg (129.5 KB, 8 views)

Attached Files

Copy of Worcester Bosch Boiler Data Log suss heating problems forum FLAMEon.xlsx (409.0 KB, 2 views)

Can very probably do this without PhotoShop but I won't be able to look at it for a day or two.

You don't need to have lots of evenly spaced (small) time intervals if the data are plotted on on an xy-scatter plot. This isn't possible with pivot tables so another way is possible with a standard excel table(s).
Also when plotting several days' data it's going to be very difficult to discern flame and motor statuses if the days all overlap.
So the attached is only to show how things might work in principle:
Your raw data stacked on top of each other on Sheet1
A Power Query table on Sheet3 which is filtered by a slicer on Sheet4
A chart on Sheet4

Choose a date(s) from the slicer to observe changes in the chart.
The chart has major gridlines every hour and minor ones every 5 minutes.
To show how easy it is to add further plots, you'll notice that when you choose 12/12/22 that another series appears; this is data from Sheet3!G340:H351.

Excel's default automatic x-axis limits aren't very good here so there's a little macro which sets them when you change the date(s) you're viewing (this needs a little more work). This macro is triggered by the Calculate event of Sheet3 which calls the macro Macro3.

I see there are SW times and GMT times which will have to be aligned - all to GMT or all to SW. You can tell me more about that.

Attached Files

MSOfficeForums50114Worcester Bosch Boiler Data Log suss heating problems forum.xlsm (72.5 KB, 1 views)