Boolean values in charts

Hi, my data represents body temperature over time in days - and thats no problem - I have this in a line chart - however I also have several boolean values which I would like to show on the chart - and they represent true/false values for each day - e.g. did you do eat meat? - or did you feel ill? (These are not the actual questions but just to give you the idea.)

I capture the boolean values as 0 (no) or 1(yes).

I'm having a problem displaying the boolean values in a meaningful way.

I have attached a sample of what the data looks like - as I say not actual data - just to give you the idea where I'm having trouble.

Any ideas?

Thx.

Hello,

you can use two helper columns to create the chart data for the "meat" and "feel ill" series.

For "meat"
=IF(C2,1,NA())

for "feel ill"
=IF(D2,1.2,NA())

Chart the temperature in a line chart. Then select the "meat" helper column, copy it. Select the chart and use the Paste Special dialog to add it as a new series.

Select the new series and format it. Tick the option to send it to the secondary axis. Change the format to have no line, just a data marker.

Then select the "feel ill" helper column, copy the data, paste special as a new series. It will be put on the secondary axis automatically.

The two helper columns have different Y values, so they don't overlap.

Format the secondary Y axis with a maximum and a minimum value to suit your visualisation needs. You may want all the markers above the temp line. Or below it. Or one above and one below. You can fine tune that with either the settings of the secondary Y axis or by changing the return value for the "feel ill" helper formula. You can hide the secondary Y axis by setting its tick marks and labels to "none".

See attached file for a suggestion.

cheers, teylyn