Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / New Users / January 2006

Tip: Looking for answers? Try searching our database.

Grouping together cells with similar label.

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
chaitanyag@hotmail.com - 15 Jan 2006 09:07 GMT
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.
Reply to this Message
rd - 15 Jan 2006 16:41 GMT
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

> Hi,
> This is probably a simple question, but what I am trying to do is draw
[quoted text clipped - 24 lines]
>
> Any help is much appreciated.
Reply to this Message
Jim Cone - 15 Jan 2006 16:45 GMT
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

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.
Reply to this Message
Debra Dalgleish - 15 Jan 2006 16:59 GMT
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

> Hi,
> This is probably a simple question, but what I am trying to do is draw
[quoted text clipped - 24 lines]
>
> Any help is much appreciated.

Signature

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

Reply to this Message
chaitanyag@hotmail.com - 15 Jan 2006 23:25 GMT
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.

> 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.
[quoted text clipped - 37 lines]
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
Reply to this Message
Debra Dalgleish - 16 Jan 2006 00:56 GMT
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
[quoted text clipped - 65 lines]
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html

Signature

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

Reply to this Message
chaitanyag@hotmail.com - 23 Jan 2006 09:59 GMT
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.
Reply to this Message
Debra Dalgleish - 24 Jan 2006 04:11 GMT
To convert the entries to real dates, you can use one of the techniques
shown here:

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

> 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
[quoted text clipped - 24 lines]
> Clearly its a formatting, data typing issue. just can't seem to clear
> the cells and enter a new format.

Signature

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

Reply to this Message
HS Hartkamp - 20 Jan 2006 09:24 GMT
I would look at sorting the list and using the subtotals-feature of excel,
if I understand your question/situation correctly.

Bas Hartkamp

> Hi,
> This is probably a simple question, but what I am trying to do is draw
[quoted text clipped - 24 lines]
>
> Any help is much appreciated.
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.