Grouping together cells with similar label.

[[email protected]]

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.

[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


<[email protected]> wrote in message
news:[email protected]...
> 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.
>

[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
news:[email protected]
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.

[Debra Dalgleish]

Add a heading to the columns, then you could create a pivot table to
summarize the data, and create a pie chart from the pivot table results.

There are instructions for pivot tables in Excel's Help, and Jon Peltier
has information and links:

http://peltiertech.com/Excel/Pivots/pivotstart.htm


[email protected] wrote:
> 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.
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[[email protected]]

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.

Debra Dalgleish wrote:
> Add a heading to the columns, then you could create a pivot table to
> summarize the data, and create a pie chart from the pivot table results.
>
> There are instructions for pivot tables in Excel's Help, and Jon Peltier
> has information and links:
>
> http://peltiertech.com/Excel/Pivots/pivotstart.htm
>
>
> [email protected] wrote:
> > 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.
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html

[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

[email protected] wrote:
> 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.
>
> Debra Dalgleish wrote:
>
>>Add a heading to the columns, then you could create a pivot table to
>>summarize the data, and create a pie chart from the pivot table results.
>>
>>There are instructions for pivot tables in Excel's Help, and Jon Peltier
>>has information and links:
>>
>> http://peltiertech.com/Excel/Pivots/pivotstart.htm
>>
>>
>>[email protected] wrote:
>>
>>>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.
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[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


<[email protected]> schreef in bericht
news:[email protected]...
> 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.
>

[[email protected]]

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.

[Debra Dalgleish]

To convert the entries to real dates, you can use one of the techniques
shown here:

http://www.contextures.com/xlDataEntry03.html


[email protected] wrote:
> 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.
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html