Basic Bar Chart Question

  • Thread starter Thread starter Spike9458
  • Start date Start date
S

Spike9458

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
 
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.
Click to expand...

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.
 
=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
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.
Click to expand...

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.
 
=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 ...
Click to expand...
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?
Click to expand...

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.
 
dvt,

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

--Jim

=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 ...
Click to expand...
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?
Click to expand...

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.
 
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

=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 ...
Click to expand...
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?
Click to expand...

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.
 
Back
Top