Excel Chart Y Axis to Rollover Back to Zero at 360

Question

I'm trying to create an Excel chart in VBA with time on the X axis and bearing angles (from North) on the Y axis. This looks fine most of the time except where the sample of data is dealing with Northerly bearings. In this case, we might get a sample with bearing values of 000.1, 000.2, 359.9, 359.8, 000.5 etc.. The chart Y axis then stretches from 0 to 360 with data at the bottom and data at the top with a lot of empty space in between. Is there any way that the Y axis can be configured to either restart at zero when it reaches 360 or split into two so that for example, it covers the range 0 to 5 and then the range 355 to 360? I'm simply asking whether this is feasible or not. I don't want to get too busy with coding just yet if it's a non-starter.

EDIT: - a couple of images to try and explain the problem. One image shows the sample of bearings to be around 90 degrees and it produces a nice Y axis. The second image shows the sample of bearings to be around 359-360 degrees with the odd one in the middle at 0 degrees

For the sample with Bearings around 360, I would like to be able to either "rollover" the Y axis back to 0 as it reaches 360 or have a split Y axis such that the section between 5 degrees and 355 degrees is not visible.

Answer 1

You need to mess with your data. Suppose the times are in A2:A41 and the angles in B2:B41. Plotting columns A and B works fine unless the values are on either side of 360.

Put a test formula in E1:
=AND(MAX(B2:B41)>350,MIN(B2:B41)<10)

This tells us that some values are close to 0 and some close to 360.

Enter this formula in cell C2 and copy it down to C41:
=IF(AND($E$1,B2<10),B2+360,B2)

This adds 360 to any small value if the large values are near 360.

Finally, plot columns A vs C.

Below are two pairs of charts where I've implemented this, one not close to 360 and one close to 360.