Creating a Chart

  • Thread starter Thread starter Gunjani
  • Start date Start date
G

Gunjani

This Pivottable /Chart wizard has confused me...
I have the following Table:

Name Day1 Day2 Day3 Day4 Day5
Tom E L R N S
Dick L N S E R
Harry N S E L E
Jane S E R L N

I like to create a chart to view who has the most E,L,N, etc. How do I
acheive this. What do I select as Row,Column and Data in the Pivotchart
Wizard?

--
Many Thanks

Gunjani
"Living is easy with your eyes closed..."
-- John Lennon
 
Your table isn't set up to create a pivot table and pivot chart. You
could reorganize the data, e.g.:

Name Day Code
Tom Day1 E
Tom Day2 L

Then create a pivot table with Name in the Row Area, Day in the Column
Area, and Code in the Data area, where it will become Count of Code.

Or, create a summary table, and base a normal chart on the summary. For
example, enter the names in cells A8:A11, and enter the codes in cells B7:F7

In cell B8, enter the formula:

=SUMPRODUCT(($A$2:$A$5=$A8)*($B$2:$F$5=B$7))

Copy the formula across to column F, and down to row 11
Select a cell in the summary table, and run the chart wizard to create a
column chart.
 
Gunjani,

I don't think the pivot table will do what you want. You could put the
following in a column (I used column H):
=COUNTIF(B2:F2,"E")

Then use this for the name of the person with the most E (put in a cell
somewhere):
=INDEX(A2:A5,MATCH(MAX(H2:H5),H2:H5))

And for the value (put just to the right of above),
=INDEX(H2:H5,MATCH(MAX(H2:H5),H2:H5))

All that stuff is for the greatest E. Do the same for the greatest L,
putting them directly under the first two, then N, etc. Now make an
ordinary chart from the table you've just built.
 
Your table isn't set up to create a pivot table and pivot chart. You
could reorganize the data, e.g.:

Name Day Code
Tom Day1 E
Tom Day2 L

Then create a pivot table with Name in the Row Area, Day in the Column
Area, and Code in the Data area, where it will become Count of Code.

Or, create a summary table, and base a normal chart on the summary. For
example, enter the names in cells A8:A11, and enter the codes in cells B7:F7

In cell B8, enter the formula:

=SUMPRODUCT(($A$2:$A$5=$A8)*($B$2:$F$5=B$7))

Copy the formula across to column F, and down to row 11
Select a cell in the summary table, and run the chart wizard to create a
column chart.
Click to expand...
Thank u Debra as suggested used the following formula

=SUMPRODUCT(($A$2:$A$5=$A8)*($B$2:$F$5=B$7))

It works but I thought Sumproduct function summed all the selected cells
and then multplied by the product of all the cells.... I could never
follow the excel help functions!!!
There are couple of other issues relating to this workshett which I will
post later, if you could assist please thanks.
--
Many Thanks

Gunjani
Yesterday is gone and tomorrow has not yet come:
We must live each day as if it were our last so that when
God calls us we are ready, and prepared, to die with a
clean heart.
-- Mother Teresa
 
In this situation, the result would be a count of the cells where the
criteria are met.
 
In this situation, the result would be a count of the cells where the
criteria are met.
Click to expand...
Hi Debra,
Following on from above I have replaced Day1,Day2 etc with Mon, Tue, Wed
etc. for a whole month and then following on another set of rows for
each of the next six consecutive months. i.e Rows A2:A5 (Jan),A7:A10
(Feb),A12:A15(Mar),A17:A20(Apr)etc

Trying to acheive the same but I want to exclude Sat and Sun from the
formula (eventually creating a separate chart for Sat Sun Individually)
Used the following to exclude Sat and Sun but to no avail
=SUMPRODUCT(($A$2:$A$30=$A33)*($B$2:$AF$30=B$32)*($B&1:$AF$33<>"Sat")*
($B&1:$AF$30<>"Sun")

How would I create a Summary tables for Tom,Dick,Harry & Jane for Mon-
FRi, Sat, Sun

--
Many Thanks

Gunjani
Don't put off for tomorrow what you can do today. For
tomorrow never comes.
-- Unknown
 
Back
Top