Hi Louis,
Let's call the table containing the data "Data".
Column D cells contain one of three answers, "A", "B", or "C".
Column D cells contain years listed as numbers, in the range 1700 to 2016.
"0-2 years ago" means a year number of 2016, 2015 or 2014.
"2 - 20 years ago" is ambiguous. Today, 2014 is 2 years ago, but 2014 is already counted in the 0-2 years category. A more precise specification would be "more than 2 and up to 20 years ago"
Similarly, a better specification for the third category would be "more than 20 years ago"
These could be changed, depending which groups you wanted it place the events of 2 and 20 years ago.
For the second chart, you say: "I need a Pie Chart that will show percentages in 3 categories. One with years from 0-2 years ago, one for 2-20 years, and one with 20+ years ago."
Do you need a single chart showing what portion of the answers applies to each time period, or do you need a set of charts, each showing the distribution of one answer through time? I have assumed the first in the examples below.
The example uses an auxiliary table to collect the data for each chart.
The table on the left ( Data) contains 300 randomly generated year numbers between 1700 and 2016 (column A) and 'answers' (column B). The randomizing formulas were written to produce an approximately equal number of years in each specified period, and to produce more B answers than A answers and more C answers than B answers.
Formulas:
Years ago:
B2: COUNTIF(Data::C,"<="&A2)
B3: COUNTIF(Data::C,"<="&A3)−B2
B4: COUNTIF(Data::C,"<="&A4)−B3−B2
B2 counts the entries in column A of 'Data' that are up to 2 years old.
B3 counts the entries in column A of 'Data' that are up to 20 years old, then subtracts the count in B2
B4 counts the entries in column A of 'Data' that are up to 2000 years old, then subtracts the counts in B2 and B3,
Select B2-B4, then click the Charts button and choose the Pie chart icon.
Answers:
B2: COUNTIF(Data::B,A)
Fill down to B3 and B4
Each formula counts the number of answers in column B of 'Data' that match the answer in the same row of column A of 'Answers' as the formula is in.
Select B2-B4, then click the Charts button and choose the Pie chart icon.
Minor tweaking has been done to the charts to display the legend in a column and to adjust the wedge colours of the upper chart.
Regards,
Barry
