Grouping together cells with similar label.

C

chaitanyag

Hi,
This is probably a simple question, but what I am trying to do is draw
a pie chart of certain number of values. Each of those values has a
label attached to it. I was wondering if there is anyway to get excel
to group together the values with similar labels before drawing the pie
chart.

Basically, The cells look something like this :

10 | A
20 | B
30 | C
10 | A
10 | A
10 | B
10 | A


When I graph this, the pie chart contains 7 segments. With 7 labels.

Is there anyway to tell excell to add up all the A cells, B cells and C
cells and graph them together? Essentially making it look as if I
graphed it from something like this :

40 | A
30 | B
30 | C

Any help is much appreciated.
 
R

rd

You can use the Sumif formula to add cells that meet certain criteria. if
the numbers in your example are in range A1:A7 and the letters in B1:B7, the
the formula =SUMIF(B1:B7,"A",A1:A7) will add all the numbers corresponding
to A and gives the sum 40. change the criteria in the middle to b
(=SUMIF(B1:B7,"B",A1:A7) to get 30 and so on. then graph the resulting
columns.

regards,
Rashed
 
J

Jim Cone

c,

You can sum the values using the SumIf function and then make
your chart. If the example is in range B5:C11, then the following
formulas in three cells with the labels in the column to the left will
chart as you want...
=SUMIF($C$5:$C$11,"=A",$B$5:$B$11)
=SUMIF($C$5:$C$11,"=B",$B$5:$B$11)
=SUMIF($C$5:$C$11,"=C",$B$5:$B$11)

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



<[email protected]>
wrote in message
Hi,
This is probably a simple question, but what I am trying to do is draw
a pie chart of certain number of values. Each of those values has a
label attached to it. I was wondering if there is anyway to get excel
to group together the values with similar labels before drawing the pie
chart.
Basically, The cells look something like this :

10 | A
20 | B
30 | C
10 | A
10 | A
10 | B
10 | A

When I graph this, the pie chart contains 7 segments. With 7 labels.
Is there anyway to tell excell to add up all the A cells, B cells and C
cells and graph them together? Essentially making it look as if I
graphed it from something like this :
40 | A
30 | B
30 | C
Any help is much appreciated.
 
C

chaitanyag

Thanks for the prompt reply. The pivot tables were exactly what I was
looking for. A more complex related question. Is there someway to group
together labels into weeks based on the dates?

For example if I have (for this month)

4/1/06 | 10 | A
6/1/06 | 20 | B
11/1/06 | 30 | C
13/1/06 | 10 | A
15/1/06 | 10 | A
19/1/06 | 10 | B
20/1/06 | 10 | A

can it recognise and group togther the entries like so ...

Week1 | 4/1/06 | 10 | A
Week1 | 6/1/06 | 20 | B
Week2 | 11/1/06 | 30 | C
Week2 | 13/1/06 | 10 | A
Week2 | 15/1/06 | 10 | A
Week3 | 19/1/06 | 10 | B
Week3 | 20/1/06 | 10 | A

basically, recognise that 4/1/06 and 6/1/06 fall in the same week and
group them together. Then recognise that 11/1/06, 13/1/06 and 15/1/06
fall in the same week as well. etc.
 
D

Debra Dalgleish

You can group the data in 7 day intervals:

In the pivot table, right-click the Date field button
Choose Group and Show Detail > Group
From the Group by list, choose Days
Set the number of days to 7
Set the start date to a Sunday or Monday, e.g. 1/1/05
Click OK

Thanks for the prompt reply. The pivot tables were exactly what I was
looking for. A more complex related question. Is there someway to group
together labels into weeks based on the dates?

For example if I have (for this month)

4/1/06 | 10 | A
6/1/06 | 20 | B
11/1/06 | 30 | C
13/1/06 | 10 | A
15/1/06 | 10 | A
19/1/06 | 10 | B
20/1/06 | 10 | A

can it recognise and group togther the entries like so ...

Week1 | 4/1/06 | 10 | A
Week1 | 6/1/06 | 20 | B
Week2 | 11/1/06 | 30 | C
Week2 | 13/1/06 | 10 | A
Week2 | 15/1/06 | 10 | A
Week3 | 19/1/06 | 10 | B
Week3 | 20/1/06 | 10 | A

basically, recognise that 4/1/06 and 6/1/06 fall in the same week and
group them together. Then recognise that 11/1/06, 13/1/06 and 15/1/06
fall in the same week as well. etc.
Click to expand...
 
H

HS Hartkamp

I would look at sorting the list and using the subtotals-feature of excel,
if I understand your question/situation correctly.

Bas Hartkamp
 
C

chaitanyag

Cool. That is exactly what I was looking for. It works fine with a test
spreadsheet that i created. But in the main spread sheet, whenever i
try to group them together, it says "Cannot group that selection". If i
only choose a certain subset (a few rows where the dates appear right
justified), then it works fine. When I choose the whole table, (where
some rows in the date column are inexplicably right justified and some
are left justified), then it gives me the "cannot group..." error.

Any idea what might be causing this? I checked the format of the cells.
I set all of them to be date. No luck. I deleted all the rows and
reentered the data again. Still no luck. I am sure its something to do
with the formatting/allowed values of the cells. but just can't figure
out what.

rows looks like this
Date
15/04/2005 <--- Left justified. "Insert function" text box in the
toolbar shows 15/04/2005
15/04/2005
16/04/2005

5/01/05 <--- Right justified. "Insert function" text box in the toolbar
shows 1/05/2005
5/02/05
5/03/05

Clearly its a formatting, data typing issue. just can't seem to clear
the cells and enter a new format.
 
Top