Numbers: Issues with display of graphs

Hi 'w,

Numbers charts the data that you supply, not the data that you display.

The hours and minutes displayed in the first chart have been entered to record only the number of hours plus the number of minutes, so what you see is what the cell actually contains.

The average hours, though, are calculated values which may contain values expressing seconds and milliseconds of duration, formatted to display the duration to the nearest whole minute. You see the formatted value, but the chart gets the actual value in that cell.

In the example below, the chart is showing the values in the two blue filled cells in the table.

The upper cell contains a copy of the AVERAGE calculated in the bottom row of the Hours column and formatted to display only Hours and Minutes, charted by the first column.

The lower cell contains a copy of the value calculated in the cell directly below it—the same AVERAGE, rounded to the closest whole minute—charted by the second column.

Formulas below.

Formulas:

Both formulas use the same data in column E of the table, most of which is not visible in the image above.

For the chart column labeled Mon, Jan 2, 2016:

=AVERAGE(E)

For the chart column labeled Tue, Jan 3, 2017:

=DURATION(,,,ROUND(DUR2MINUTES(AVERAGE(E)),0))

Because ROUND works with numbers, but not with durations, it's necessary to convert the average duration to a number, using DUR2MINUTES, round that number to the nearest whole number, then convert the rounded number back to the duration represented by the rounded number.

Regards,

Barry

Barry wrote:

Numbers charts the data that you supply, not the data that you display.

From the 'Charting Basics' template at File > New:

Then with display format slightly changed but no other changes, the whole chart changes:

That is, if 'Same as Data Source' is selected.

So the data you display can affect a chart.

SG

use the function dur2hours() to convert the duration into decimal hours for plotting

so in the cell that computes the duration now update the formula like this:

in the cell now:

=someformula()

replace with:

=dur2hours(someformula())

"So the data you display can affect a chart."

So I see.

I'm sure I tried that in the example above, and found it did not cure the issue. The difference may be using duration values rather than number values. Formatting the AVERAGE values to display only hours and minutes did not remove the decimal places from the value label on the bars.

Barry

select the chart, then use the Series formatter to set the number of places used for the displayed values:

You could consider using the DUR2HOURS function to convert the duration to decimal hours and then format that number for the chart.

SG

then you can use a formula like this:

=DURATION(0,0,INT(DUR2HOURS(B4)),INT(60×(B4−INT(DUR2HOURS(B4)))+0.5))

this assumes that the duration you want to "fix" is in cell B4