Populating a bar chart with automatically-calculated numbers?

[Guest3731]

Hi. I have a bar chart. X-axis is discrete time-periods. Y-axis is
(integer) numbers of incidents per time-period.

I've also got a table/spreadsheet that contains rows, each of which
represents one of these Y-axis incidents. The date of the incident is
in, say, Column C.

My question is: can I set up a two-tab spreadsheet that has the bar
chart on one tab and the table on another, such that the bar chart
"reads" the table and increases the length of a given Y-value based on
the number of table entries that correspond to that time-period (e.g.
if there are 5 rows in which Column C reads "April", the "April" Y-
value on the bar chart is automatically set to the value "5"?

Many thanks for any insight.

 

[Pete_UK]

What you need is a separate summary table which counts how many
entries there are for each time period. This summary table could be on
the same sheet as your main data, or on a separate sheet. The bar-
chart would derive its data from this summary table and would thus
adjust as new values are added.

Hope this helps.

Pete

 

[Bernie Deitrick]

Use a pivot chart, using count on the Month column, or set up a table with
the Month names, and COUNTIF formulas to count the monthly incedences, along
the lines of

=COUNTIF(C:C,"April")

But you might actually have dates that are just formatted to show the month
name, which would require a slightly different solution.

HTH,
Bernie
MS Excel MVP

 

[Guest3731]

Use a pivot chart, using count on the Month column, or set up a table with
the Month names, and COUNTIF formulas to count the monthly incedences, along
the lines of

=COUNTIF(C:C,"April")

But you might actually have dates that are just formatted to show the month
name, which would require a slightly different solution.

HTH,
Bernie
MS Excel MVP

Thank you for both replies. This makes me wonder if I am setting up
my data wrong. The "data" spreadsheet currently contains two separate
date-columns, one formatted to show just the month name, the other
(right next to it) formatted to show just the day and year. If the
real date is 4/15/2009, I enter 4/1/2009 in the first and 4/15/2009 in
the second.

The reason being, I wanted to be able to sort the entire spreadsheet
first by an irrelevant criterion, then by month, then by name. And I
could not figure out how to do that with just "4/15/2009" as the sole
date criterion. Does that even make sense, or am I crazy?

Is there perhaps a better way to input my dates that would also be
sortable in the way I've indicate and allow for the use of a pivot
chart as you suggested?

Thanks very much for any guidance.

 

[Bernie Deitrick]

Just enter 4/15/2009 as your date, once.

If you want the month, use a column of formulas

=MONTH(A2)

where A2 is the cell with the date, and another

=YEAR(A2)

and copy those down to match. Then you can sort on Year first, then month,
etc.

But a much better way would be to select your data and just use the dates
and the data to produce counts by month, using a Pivot Table with the dates
as Row Fields, and whatever as data fields. Then you could group the row
data by month, and use Count as the setting for the data field. Excel would
produce the values automatically, with no formulas involved.

HTH,
Bernie
MS Excel MVP

Use a pivot chart, using count on the Month column, or set up a table with
the Month names, and COUNTIF formulas to count the monthly incedences,
along
the lines of

=COUNTIF(C:C,"April")

But you might actually have dates that are just formatted to show the
month
name, which would require a slightly different solution.

HTH,
Bernie
MS Excel MVP

Thank you for both replies. This makes me wonder if I am setting up
my data wrong. The "data" spreadsheet currently contains two separate
date-columns, one formatted to show just the month name, the other
(right next to it) formatted to show just the day and year. If the
real date is 4/15/2009, I enter 4/1/2009 in the first and 4/15/2009 in
the second.

The reason being, I wanted to be able to sort the entire spreadsheet
first by an irrelevant criterion, then by month, then by name. And I
could not figure out how to do that with just "4/15/2009" as the sole
date criterion. Does that even make sense, or am I crazy?

Is there perhaps a better way to input my dates that would also be
sortable in the way I've indicate and allow for the use of a pivot
chart as you suggested?

Thanks very much for any guidance.