Question about chart vs. pivot table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to display some data that I would like to display in a chart and/or
table, but I cannot for the life of me figure out how to do it. This is the
situation. In one row, I have a series of 0's and 1's the represent yes and
no. The column for each is a separate entity. Each column is really
irrelevant to what I'm trying to figure out other than the total number of
columns. What I am trying to chart is what percentage of all of the columns
contain a 0 (no) and what percentage contain a 1 (yes). It looks sort of
like the below:

col 1 col 2 col 3 col4 col5
y/n 0 0 1 0 1


Does this make sense? I'm sure this is simple, I'm just not getting it from
reading all of the help on charts and pivot tables (and if you can't tell....
Excel isn't my primary tool!). Thanks in advance!
 
Hi!

You're right, this will be a snap -- and you don't have
to go to pivot tables at all here .... you just need a
couple of formulas and a simple pie chart.

You can use the function COUNTIF to count the number of
1's and the number of 0's, then chart those two results
in a pie chart if you want to display the %'s
graphically.

(Note that i'm using A2:X2 as a sample data range in all
formulas below ... just substitute your actual data range
and you'll be good to go)...

for the number of 1's in your data range:
= COUNTIF(A2:X2, 1)
for the number of 0's in your data range:
= COUNTIF(A2:X2, 0)

Or, you can show the percentages directly in your data
with these two functions (and you can chart these two
with a simple pie chart as well):

=(COUNTIF(A2:X2,1)/COUNT(A2:X2))

Formula above counss the number of 1's in the data range
and then divides that by the total number of responses to
get the percent of 'yes' responses:

= 1 - cell address where first formula appears

Formula above assumes that all cells in your data range
for the first formula contain either a 0 or a 1. As long
as that's true, 1 minus the first formula's result gives
you the percentage of 'no' responses.

Hope this helps.

Best,
Stephanie Krieger
author of Microsoft Office Document Designer (from
Microsoft Press)
email: MODD_2003 at msn dot com
blog: arouet.net
 
Just wanted to add that if you use the first option in
the response I just posted -- the 2 COUNTIF functions,
then chart with a pie chart, the pie chart slices will be
a percentage of the total by default. To display the
values on the chart as percent of the total, rather than
the # of responses -- in step 3 of the Chart Wizard check
the Percentage option on the Data Labels tab of the
dialog box.

Best,
Stephanie
 
Stephanie -

Thanks for the info! I'm not sure I'm totally clear on everything, but
figure I'll give it a try and see how it goes.

Another question, if I may. If I have a similar situation (one row of data
that needs to be evaluated as a percentage of the whole) where there are 5
different values instead of the 0 and 1 in the previous example, would I use
the same formula's, etc. that you posted for the original question?

I'm trying to produce trends graphically. So I'm looking for the easiest
way to do it. The number of columns will change daily (more added) and I
don't want to have to redo the formula every time if at all possible. I
think the info you gave me would require that I edit it each time, correct?
Is there anyway that I can do it without having to edit?

Your help is SO appreciated!
Lisa
 
One more thing. Can I put the formula's on a 2nd sheet in the same workbook?
The spreadsheet with the data is one that will be merged with others (with
the same format) every month so I cannot add new cells.

Thanks!
 
Hi, Lisa,

You're very welcome!

A quick yes to two of your questions -- you can
definitely put the formulas on a separate sheet (the
sheet name is just typed at the start of the data range
followed by an exclamation point ...would look like this
with the example from the earlier posts (where the name
of the sheet containing your data range is 'Data':

=COUNTIF(Data!A2:X2,1)

And, yes, you can definitely use the COUNTIF functions
with the 5 options just as well...

In terms of how to do this so you don't have to edit it
all the time -- most likely ... but the solution options
depend on where your data is coming from (i.e., do you
manually enter it or does it come from a database or some
other automatic source?) and your Excel version.

You can send me a sample of the data if you like (along
with the answers to those two questions) ... and I'll
send you a sample solution. Use MODD_2003 at msn dot com.

Stephanie
 
I posted out of order ... see my response at 8:44
yesterday for answer to this question ...

Stephanie
 
MODD_ said:
A quick yes to two of your questions -- you can
definitely put the formulas on a separate sheet (the
sheet name is just typed at the start of the data range
followed by an exclamation point ...would look like this
Click to expand...

No, please don't! Typing is time-consuming and *highly* error prone.
Use the mouse. Select the range of interest and XL will do *all* the
necessary work to create the *correct* cell reference including, as
needed, workbook name, worksheet name, special characters, etc.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top