Showing average results on a graphic with Numbers '09

HI M,

You need to supply Numbers with the values you want to graph.

Formula in Average::B2: =AVERAGEIF(Data :: C,A2,Data :: B)

Fill down to B3.

Regards,

Barry

Does this do what you want? It uses the AVERAGEIF() function to calculate the average for each group in a separate table. Then that table is used to create a graph.

Oops, as I was doing this basically same answer was posted. Well at least I know I'm thinking along the right lines.

Hi SGIII,

It's not usually an "oops!" More often it's the result of thinking along the same lines but coming to the question while the other person is working on a similar solution. In some cases where I've done the same thing, I've edited my post to remove my solution as redundant, in others, I've let it stand. In this case, I'm glad you chose the latter patth, as it gives me a chance to comment on graphs in general and the visual messages they present.

Your chart and mine came from the same data, and started out looking pretty much the same.

Yours has edits to the title and the colour of the bars; otherwise it is the graph as Numbers delivers it.

Mine has three edits: the Min and Max values on the Y axis have been changed from the automatically generated values to the actual minimum and maximum scores (0 and 15), and the number of steps set to a value that makes those steps occur (as the default four steps did in your chart) at half point intervals.

Visually, the default graph tells the viewer, "Wow! The control group did four times as well as the Patient group!" while the modified one presents what I think is a more accurate picture: "The control group did a bit better than the patient group—maybe about 10-15 percent better."

Regards,

Barry

You do need to 'do all the average calculations,' as Numbers wil graph only the data you supply to the chart.

Excluding the -9 values from the calculations requires switching from AVERGEIF, which handles a single condition, to AVERAGEIFS, which allows specifying multiple conditions, all of which must be TRUE for the datum to be included in the calculation.

For the case described, the formula would be

=AVERAGEIFS(J,C,"=1",J,"<>-9")

If the formula is on a separate sheet, you will need to add the sheet name and table name to each cell reference in the formula:

=AVERAGEIFS(finale 1::tableau 1::J,finale 1::tableau 1::C,"=1",finale 1::tableau 1::J,"<>-9")

(Replace the commas in my version with semi colons to fit your localization.)

Regards,

Barry

Note: No time to test it this morning.

B

Hi Barry,

Thanks for the helpful explanation of uses of Max and Min values and number of steps.

Sumner

And to "let Numbers know 1= Control ; 2 = Patient ..." maybe try a lookup table together with the VLOOKUP() function something like this:

My formula in column C is:

=AVERAGEIFS(Data :: B,Data :: C,B2,Data :: B,"<>-9")

The lookup lets you leave the original data table untouched and have your average calculations based on the group "code" in the original data rather than the more friendly group "name" that you want for the chart.

BTW, have you discovered you can quickly do sums/averages/etc on categories/groups in a data table by going to Table/Show Reorganize Panel? Don't think you can produce charts based on that, but it can be a convenient way to start making sense of the data.