Basic Bar Chart Question

I have a database where column H2:H89 are the prices homes sold for during a
given period. I would like to create a bar chart with each bar representing
a price range. For instance, I'd like the first vertical bar to be homes
that sold for less than $99,999, next bar $100k-$124,999k, next one
$125k-$149,999k etc ...... I don't know how to set up the chart data area to
pick out these ranges automatically.

Can anybody get me started or point me to a website that has the help there.
I tried Excel help but didn't get too far.

TIA,

--Jim

On Wed, 29 Oct 2003 05:22:09 -0500, Spike9458 =

wrote:

I have a database where column H2:H89 are the prices homes sold for =

during a
given period. I would like to create a bar chart with each bar =

representing
a price range. For instance, I'd like the first vertical bar to be hom=
es
that sold for less than $99,999, next bar $100k-$124,999k, next one
$125k-$149,999k etc ...... I don't know how to set up the chart data =

area to
pick out these ranges automatically.

I think you should start by using the FREQUENCY or HISTOGRAM functions i=
n =

your spreadsheet. Then chart the results of those functions. I know th=
e =

histogram function (and perhaps also the frequency function) requires th=
e =

Analysis ToolPak add-in, which comes with Excel. You might need to =

install that add-in if you haven't used it before.

Use Excel help to show you how to use each of these functions and (if =

required) install the add-in. If you need a few more pointers on using =

those functions after you've read the help, come back and ask again.

-- =

Dave
dvt at psu dot edu

=FREQUENCY($H$2:$H$89,$H98:$H$175)

This is the formula I copied down beside the column I wanted to graph.
$h$2:$h$89 is the column of data I want to graph (prices range from $24000
to over $500000 ... results returned "0" in each cell, and nothing came up
in h98:h175 ... I really don't have a clue, tried using the Excel help file,
but I think I need even more basic help than that. I have made graphs like
the one I want in the past, but it's been several years, and my forgetter is
working all too well. The end result is that I want the axis across the
bottom to be the price of a house, in $25000 increments from $100k to $600k,
and on the vertical axis I want the quantity, so that my graph shows that X
number of houses were in the $150k-$175k range, and so forth ... making
sense?

Thanks for your help!

--Jim
"dvt" wrote in message newsprxs734n8qi5lh2@localhost...
On Wed, 29 Oct 2003 05:22:09 -0500, Spike9458
wrote:

I have a database where column H2:H89 are the prices homes sold for
during a
given period. I would like to create a bar chart with each bar
representing
a price range. For instance, I'd like the first vertical bar to be homes
that sold for less than $99,999, next bar $100k-$124,999k, next one
$125k-$149,999k etc ...... I don't know how to set up the chart data
area to
pick out these ranges automatically.

I think you should start by using the FREQUENCY or HISTOGRAM functions in
your spreadsheet. Then chart the results of those functions. I know the
histogram function (and perhaps also the frequency function) requires the
Analysis ToolPak add-in, which comes with Excel. You might need to
install that add-in if you haven't used it before.

Use Excel help to show you how to use each of these functions and (if
required) install the add-in. If you need a few more pointers on using
those functions after you've read the help, come back and ask again.

--
Dave
dvt at psu dot edu

On Thu, 30 Oct 2003 06:55:48 -0500, Spike9458 =

wrote:

=3DFREQUENCY($H$2:$H$89,$H98:$H$175)

This is the formula I copied down beside the column I wanted to graph=
..
$h$2:$h$89 is the column of data I want to graph (prices range from =

$24000
to over $500000 ... results returned "0" in each cell, and nothing cam=
e =

up
in h98:h175 ...

The end result is that I want the axis across the
bottom to be the price of a house, in $25000 increments from $100k to =

$600k,
and on the vertical axis I want the quantity, so that my graph shows =

that X
number of houses were in the $150k-$175k range, and so forth ... makin=
g
sense?

You are pretty close. The second range of values (in your case H98:H175=
) =

should have numbers you entered. Based on what you say, you want these =

values in H98:H118

100,000
125,000
150,000
175,000
....
600,000

Now you should edit your formula to reference H98:H118 instead of =

H98:H175. Now put that formula in cell I98. Select cells I98:I119, =

making sure I98 is the active cell. Hit F2 to edit the formula, then =

array-enter the formula with the keystroke ctrl-shift-enter. Your formu=
la =

should be surrounded by curly braces {} and should be copied down to all=
=

cells in your selected range.

The results shown in I98:I119 will be the number of values that fit the =

bins 0-100k, 100-125k, 125-150k, .... above 600k. You can then chart =

I98:I119.

Hope this helps.

--
Dave
dvt at psu dot edu

dvt,

That's EXACTLY what I was trying to do ... thank-you for your patience and
insight.

--Jim

"dvt" wrote in message newsprxuyk6usqi5lh2@localhost...
On Thu, 30 Oct 2003 06:55:48 -0500, Spike9458
wrote:

=FREQUENCY($H$2:$H$89,$H98:$H$175)

This is the formula I copied down beside the column I wanted to graph.
$h$2:$h$89 is the column of data I want to graph (prices range from
$24000
to over $500000 ... results returned "0" in each cell, and nothing came
up
in h98:h175 ...

The end result is that I want the axis across the
bottom to be the price of a house, in $25000 increments from $100k to
$600k,
and on the vertical axis I want the quantity, so that my graph shows
that X
number of houses were in the $150k-$175k range, and so forth ... making
sense?

You are pretty close. The second range of values (in your case H98:H175)
should have numbers you entered. Based on what you say, you want these
values in H98:H118

100,000
125,000
150,000
175,000
....
600,000

Now you should edit your formula to reference H98:H118 instead of
H98:H175. Now put that formula in cell I98. Select cells I98:I119,
making sure I98 is the active cell. Hit F2 to edit the formula, then
array-enter the formula with the keystroke ctrl-shift-enter. Your formula
should be surrounded by curly braces {} and should be copied down to all
cells in your selected range.

The results shown in I98:I119 will be the number of values that fit the
bins 0-100k, 100-125k, 125-150k, .... above 600k. You can then chart
I98:I119.

Hope this helps.

--
Dave
dvt at psu dot edu

DVT ... one more question: How could I add 2 lines to my graph ... one that
would represent the Average sales price, and one that would represent the
Median sales price?

--Jim

"dvt" wrote in message newsprxuyk6usqi5lh2@localhost...
On Thu, 30 Oct 2003 06:55:48 -0500, Spike9458
wrote:

=FREQUENCY($H$2:$H$89,$H98:$H$175)

This is the formula I copied down beside the column I wanted to graph.
$h$2:$h$89 is the column of data I want to graph (prices range from
$24000
to over $500000 ... results returned "0" in each cell, and nothing came
up
in h98:h175 ...

The end result is that I want the axis across the
bottom to be the price of a house, in $25000 increments from $100k to
$600k,
and on the vertical axis I want the quantity, so that my graph shows
that X
number of houses were in the $150k-$175k range, and so forth ... making
sense?

You are pretty close. The second range of values (in your case H98:H175)
should have numbers you entered. Based on what you say, you want these
values in H98:H118

100,000
125,000
150,000
175,000
....
600,000

Now you should edit your formula to reference H98:H118 instead of
H98:H175. Now put that formula in cell I98. Select cells I98:I119,
making sure I98 is the active cell. Hit F2 to edit the formula, then
array-enter the formula with the keystroke ctrl-shift-enter. Your formula
should be surrounded by curly braces {} and should be copied down to all
cells in your selected range.

The results shown in I98:I119 will be the number of values that fit the
bins 0-100k, 100-125k, 125-150k, .... above 600k. You can then chart
I98:I119.

Hope this helps.

--
Dave
dvt at psu dot edu

Jon Peltier has some instructions for adding lines to a chart:

http://www.geocities.com/jonpeltier/...s.html#AddLine

Spike9458 wrote:
DVT ... one more question: How could I add 2 lines to my graph ... one that
would represent the Average sales price, and one that would represent the
Median sales price?

--Jim

"dvt" wrote in message newsprxuyk6usqi5lh2@localhost...
On Thu, 30 Oct 2003 06:55:48 -0500, Spike9458
wrote:


=FREQUENCY($H$2:$H$89,$H98:$H$175)

This is the formula I copied down beside the column I wanted to graph.
$h$2:$h$89 is the column of data I want to graph (prices range from
$24000
to over $500000 ... results returned "0" in each cell, and nothing came
up
in h98:h175 ...


The end result is that I want the axis across the
bottom to be the price of a house, in $25000 increments from $100k to
$600k,
and on the vertical axis I want the quantity, so that my graph shows
that X
number of houses were in the $150k-$175k range, and so forth ... making
sense?


You are pretty close. The second range of values (in your case H98:H175)
should have numbers you entered. Based on what you say, you want these
values in H98:H118

100,000
125,000
150,000
175,000
...
600,000

Now you should edit your formula to reference H98:H118 instead of
H98:H175. Now put that formula in cell I98. Select cells I98:I119,
making sure I98 is the active cell. Hit F2 to edit the formula, then
array-enter the formula with the keystroke ctrl-shift-enter. Your formula
should be surrounded by curly braces {} and should be copied down to all
cells in your selected range.

The results shown in I98:I119 will be the number of values that fit the
bins 0-100k, 100-125k, 125-150k, .... above 600k. You can then chart
I98:I119.

Hope this helps.

--
Dave
dvt at psu dot edu




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

Jim -

Try this site:

Add a Horizontal or Vertical Line to a Column or Line Chart
http://www.geocities.com/jonpeltier/...s.html#AddLine

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Spike9458 wrote:
DVT ... one more question: How could I add 2 lines to my graph ... one that
would represent the Average sales price, and one that would represent the
Median sales price?

--Jim

"dvt" wrote in message newsprxuyk6usqi5lh2@localhost...
On Thu, 30 Oct 2003 06:55:48 -0500, Spike9458
wrote:


=FREQUENCY($H$2:$H$89,$H98:$H$175)

This is the formula I copied down beside the column I wanted to graph.
$h$2:$h$89 is the column of data I want to graph (prices range from
$24000
to over $500000 ... results returned "0" in each cell, and nothing came
up
in h98:h175 ...


The end result is that I want the axis across the
bottom to be the price of a house, in $25000 increments from $100k to
$600k,
and on the vertical axis I want the quantity, so that my graph shows
that X
number of houses were in the $150k-$175k range, and so forth ... making
sense?


You are pretty close. The second range of values (in your case H98:H175)
should have numbers you entered. Based on what you say, you want these
values in H98:H118

100,000
125,000
150,000
175,000
....
600,000

Now you should edit your formula to reference H98:H118 instead of
H98:H175. Now put that formula in cell I98. Select cells I98:I119,
making sure I98 is the active cell. Hit F2 to edit the formula, then
array-enter the formula with the keystroke ctrl-shift-enter. Your formula
should be surrounded by curly braces {} and should be copied down to all
cells in your selected range.

The results shown in I98:I119 will be the number of values that fit the
bins 0-100k, 100-125k, 125-150k, .... above 600k. You can then chart
I98:I119.

Hope this helps.

--
Dave
dvt at psu dot edu