Graphing "binary" data in Excel 2010

[lcripps]

If this topic exists under another title please excuse this post and kindly point me to the correct string. (I did search).

My question: I would like some help with graphing - specifically, I am evaluating questions from a test we are giving. How do I graph this data?

I am plotting the test results (Column A is the employee number, B is the Employee name, then each of the questions are Columns D through DX with DY being the total correct by each employee) (Rows are employees and then the Ratio that the question was answered correctly) The goal is to find questions that are routinely answered in correct or that have a problem with the question.

I was hoping to graph the correct and incorrect questions so those that are either high frequency correct, incorrect or not answerd identified I can improve the test.


Example of the spreadsheet. I am using =COUNTIF(D3:D15,"1")/12 for the ratio answered correct - this should also help me see the ones not answered as incorrect. 1 = correct answer 0 = incorrcet null=not answered


[TABLE="width: 381"]
<TBODY>[TR]
[TD]NO
[/TD]
[TD]Employee Name</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Question
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10001</SPAN>
[/TD]
[TD]Employee 1</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10002</SPAN>
[/TD]
[TD]Employee 2</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10003</SPAN>
[/TD]
[TD]Employee 3</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10004</SPAN>
[/TD]
[TD]Employee 4</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10005</SPAN>
[/TD]
[TD]Employee 5</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10006</SPAN>
[/TD]
[TD]Employee 6</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10007</SPAN>
[/TD]
[TD]Employee 7</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10008</SPAN>
[/TD]
[TD]Employee 8</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10009</SPAN>
[/TD]
[TD]Employee 9</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10010</SPAN>
[/TD]
[TD]Employee 10</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10011</SPAN>
[/TD]
[TD]Employee 11</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10012</SPAN>
[/TD]
[TD]Employee 12</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Ratio answered correct</SPAN>
[/TD]
[TD="align: right"]91.67%</SPAN>
[/TD]
[TD="align: right"]91.67%</SPAN>
[/TD]
[TD="align: right"]83.33%</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


I am very open to possible ways to improve this process but my specific question is - how can I graph these results to answer which questions have a high frequency of incorrect answers? I am thinking some kind of scatter plot.

Thank you in advance for any help.

 

[Steve=True]

Hi LCripps,

Welcome to the forums.

Here is what i would propose. I would use a Stacked 100% column chart if you think percentages are better viewed or just a Stacked Column Chart if count is more important.

Also, i think it is important to call out Incorrect and Not Answered as well.

What do you think?

Steve=True

 

[lcripps]

Thank you Steve=True!

yes the stacked column would be perfect and I hadn't considered showing the full 100% broke out by correct, incorrect and not answered. This view would be good to evaluate the success of the question.

I don't want to sound obtuse but how can I create the report from the data I have?

I will research on the site how to build these AND I will let you know if I solve this before your reply.

Thanks - lcripps

 

[Steve=True]

Awesome, i am so glad it will help.

Also, thanks for the great feedback.

Sorry i didn't describe the 2 different charts.

Both charts based on the data range in my picture of

C18:F20
these formulas are as follows:

Correct=countif(range,1)
Incorrect=countif(range,0)
No Answer=Countblank(range)

One on the left is a 100% stacked column chart and it puts in the %'s.

The one on the right is Stacked bar chart and since all the questions have a total of 12 possible it will also look like a 100% stacked bar chart but put in numbers for the vertical axis instead of %'s.

Let me know if you figure it out or need more help

Steve=True

 

[lcripps]

I did stay with the 100% stacked - I finished up the graphs and we were able to identify a few test questions that had a below expected result. We also identified a mistake in the grading key. Thank you again for your help and suggestion.

lcripps

 

[Steve=True]

You are very welcome and thanks for the great feedback.

Steve=True