?floating stacked bar graph

  • Thread starter Thread starter Mary Schutz
  • Start date Start date
M

Mary Schutz

I am trying to graph student test data, and what I want to see is a stacked
bar graph that shows the percentage of students scoring (1) above the 39th
percentile, and (2) below the 40th percentile, with the 40 as the divider of
the stacked bar (kind of acting as a zero in a graph, with the percentage of
scores above the line representing above 39th percentile, and the bar below
the line representing the scores below the 40th). Can someone help me set
it up?

Thanks.... Mary

--
Don or Mary Schutz
PO Box 280
Wagon Mound, NM 87752
505-666-2456
(e-mail address removed)
 
If I understand you correctly...

Suppose your data are in col. A starting with A2. [For my test, I used
A2:A5.] Suppose that C1 contains the percentile value of interest (0.4,
in your case).

Then, in C2, enter the formula
=COUNTIF(A2:A5,">"&PERCENTILE(A2:A5,C1))/COUNTA(A2:A5)

And, in D2, enter =1-C2.

Now, plot C2:D2. In step 2 of the chart wizard, ensure you select 'data
in columns' option.

Also, if I were you, I wouldn't go about sharing my address and phone
number over the I'net.

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2003
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office
 
I suspect Mary is comparing students in a local class or school with
some kind of national percentiles. Otherwise, "percentage of students
scoring below the 40th percentile" is rather trivial, being 40%.

The counting approach Tushar suggests works just fine, although you need
to put the nationwide score corresponding to the 40th percentile into
it. Put this score into cell B2, then use this:

=COUNTIF(A2:A5,">"&B2)/COUNTA(A2:A5)

to get the percentage of your students who exceed the national 40th
percentile.

- Jon
 
Back
Top