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 / October 2006

Tip: Looking for answers? Try searching our database.

question re: best way to organze data for this chart

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
MarkB - 30 Oct 2006 18:45 GMT
Have product sales data spreadsheet that I need to produce chart for sales
presentation.
Data includes sales transactions spread over several months. Sample:

Transaction 1 - July 20  $12,000
Transaction 2 - July 25  $25,000
Transaction 3 - Aug 1   $35,000
Transaction 4 - Aug 20 $15,000 etc

We have several sales per month at different prices. We'd like to show bar
chart by month for sales in groups of $10,000 increments. So each month
there should be bars for sales in these ranges:

$10,000 - $19,999
$20,000 - $29,999
$30,000 - $39,999 etc.

There would be a bar for each group above with the height controlled by
number of sales in each price category that month) and would like the total
of those sales to appear on the bar and the total number of transactions -
how many sales in that category) at the top of that bar. This would repeat
for each month being included. I can manually grab the numbers and place in
new chart and annotate the totals and count but wondiering how much of that
can be automated/done from the chart wizard or options.

Summary:
if we had 3 sales in one month in the $10,000 - $19,999 range, that bar
would be 3 units tall
if we had 4 sales in one month in the $20,000 - $29,999 range, that bar
would be 4 units tall
if we had 2 sales in one month in the $30,000 - $39,999 range, that bar
would be 2 units tall

Thanks.
Bernard Liengme - 30 Oct 2006 19:27 GMT
Use FREQUENCY to get a summary of how many sales in each category
Make an XY chart of the resulting data.
Have a go and come back if you need more help
best wishes
Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Have product sales data spreadsheet that I need to produce chart for sales
> presentation.
[quoted text clipped - 31 lines]
>
> Thanks.
MarkB - 30 Oct 2006 20:30 GMT
I tried frequency and read the help screens, but am missing something, I use
the formula
=FREQUENCY(C2:C35,E2:E12) in cell E36, but when I enter it, it returns a
zero in that cell.
noteL: C2 to C25 contains the date, and E2-E12 is where I typed the BIN
values.

I'm not getting anything useful.

> Use FREQUENCY to get a summary of how many sales in each category
> Make an XY chart of the resulting data.
[quoted text clipped - 35 lines]
>>
>> Thanks.
Bernard Liengme - 31 Oct 2006 15:19 GMT
If bin is E2:E12, then select F2:F13 (13 to get one extra)
type formula, commit it with CTRL+SHIFT+ENTER as it is an array formula
best wishes
Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

>I tried frequency and read the help screens, but am missing something, I
>use the formula
[quoted text clipped - 44 lines]
>>>
>>> Thanks.

Rate this thread:






 
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.