Filters applied to creat a percentage pie chart.

**mark_sheffield** · 07-09-2010, 09:08 AM

Hi,

I have a spreadsheet with answers - Excellent, Good, Average, Poor etc which can also be filtered by month which is another column. I would like to calculate forexample the percentage of each answer in a pie chart when needed.

So forexample in colum I will filter for month of May, and then I want in a pie chart to see precentage of answer that are good, percentage of excellent etc. Can this be done easily enough?

If it take a lot of complicated forumlas to do each time can you configure a macro or something like that because this spreadsheet will be going to a novice user when it is created?

Thank you in advance.

**mark_sheffield** · 07-09-2010, 09:10 AM

I have attached my spreadsheet to show you what I mean.

**Andy Pope** · 07-09-2010, 09:10 AM

You will need a formula for each slice in the pie.
As you are filtering data you will need to use the SUBTOTAL formula

Or you could create a pivot table/chart

**mark_sheffield** · 07-09-2010, 09:17 AM

Originally Posted by Andy Pope

You will need a formula for each slice in the pie.
As you are filtering data you will need to use the SUBTOTAL formula

Or you could create a pivot table/chart

OK, I have never used a Pivot Table before so i will have a look in to that. Any other suggetsions would be helpful though.

Thanks

**Andy Pope** · 07-09-2010, 09:28 AM

Actually you data layout is all wrong for pivot tables.

See attached it uses formula to produce a summary table on the visible rows.
The chart plots 1 of the Q's which you can select in B1.

**mark_sheffield** · 07-09-2010, 09:51 AM

Originally Posted by Andy Pope

Actually you data layout is all wrong for pivot tables.

See attached it uses formula to produce a summary table on the visible rows.
The chart plots 1 of the Q's which you can select in B1.

That looks great although I haven't a clue how you have done it.

The chart takes in to account everything in the table, B1 doesn't seem to do anything, its always all of the questions. Is there a little tweak I need to do for this.

But I thank you very much, my colleague will be very happy with this I think.

**Andy Pope** · 07-09-2010, 09:56 AM

My bad!. All formula where testing first question.

Replace formula in G2 then fill across and down

=SUMPRODUCT((G$14:G$25=$F2)*($M$14:$M$25))/SUM($M$14:$M$25)

**martindwilson** · 07-09-2010, 10:01 AM

there is a small problem the cell references have been fixed in g2 so all columns refer to q1.1
needs to change to
=SUMPRODUCT((G$14:G$25=$F2)*($M$14:$M$25))/SUM($M$14:$M$25) dragged across and down that is why b1 isnt doing anything obvious
oh i see andy has spotted it as well!!!1

**mark_sheffield** · 07-09-2010, 10:03 AM

Originally Posted by Andy Pope

My bad!. All formula where testing first question.

Replace formula in G2 then fill across and down

=SUMPRODUCT((G$14:G$25=$F2)*($M$14:$M$25))/SUM($M$14:$M$25)

Outstanding- Thank you ever so much!

**mark_sheffield** · 07-09-2010, 10:29 AM

Any ideas why I would get values like 208%, 167% etc?

**Andy Pope** · 07-09-2010, 10:31 AM

cause the G still has a $ infront making it a absolute reference.

**mark_sheffield** · 07-09-2010, 10:33 AM

Originally Posted by Andy Pope

cause the G still has a $ infront making it a absolute reference.

my bad - thanks

Filters applied to creat a percentage pie chart.

LinkBack

Thread Tools

Rate This Thread

Display

Filters applied to creat a percentage pie chart.

Re: Filters applied to creat a percentage pie chart.

Re: Filters applied to creat a percentage pie chart.

Re: Filters applied to creat a percentage pie chart.

Re: Filters applied to creat a percentage pie chart.

Re: Filters applied to creat a percentage pie chart.

Re: Filters applied to creat a percentage pie chart.

Re: Filters applied to creat a percentage pie chart.

Re: Filters applied to creat a percentage pie chart.

Re: Filters applied to creat a percentage pie chart.

Re: Filters applied to creat a percentage pie chart.

Re: Filters applied to creat a percentage pie chart.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions