This week, I was building an Excel workbook for teachers. The idea was to create a tool for analyzing student performance on a single assessment. In my case, I was developing for a particular scoring tool (like the example below), but a teacher could make a similar version for a test, allowing one cell per item/point.

At first, this looks like a fairly straightforward affair. I have a list of students down the side. I break down each point of a rubric into the columns in the middle, then have a place to total the points and equate them to a level of performance (e.g., below standard, at standard, above standard) at the end.

Heck, I even added some fancy-schmancy conditional formatting so that the cells with the attribute points would change colour. Note that if more than one point is entered, the cell is highlighted with a border change.

Ooooo. Aahhhh. |

WTH, Excel? |

I begged. I pleaded. Please, Excel, let my zeros go. At first, I went in and changed the Options such that zeros didn't show up.

Take that! |

Bummer |

So, I set out on a hunt for an answer. Surely I was not the first person in the history of spreadsheets who wanted Excel to not fill its nothingness with zeros. I read many forum posts by people who didn't want zero included for various reasons, but the bottom line was that there was no simple answer to this issue...no magic command to make Excel do my bidding. However, I did find two workarounds that kept invisible zeros at bay.

First dealt with the Total column. I really wanted to leave the option on for showing zero, if a student "earned" it. Originally, I had used the basic SUM function: =SUM(D5:N5). But one YouTube source provided this idea: =IF(COUNT(D5:N5),SUM(D5:N5),""). In other words, tell Excel to count what is in the previous columns...if there's something in them, total them...if not, leave the cell blank. Huzzah!

But this didn't solve the column with the Performance Levels. For this one, I had to use ISBLANK. This function has to be used at the very beginning of a statement. It consists of ISBLANK(value). The "value" refers to a cell. So, by starting my formula with ISBLANK and then following with the nested IF, I knocked out problem #2.

Le Sigh |

**Update:**We found the answer! See bottom of post and comments.)

Anyway, here is what the workbook looks like when ready to use:

And here is an example of a completed version:

If you want this sample workbook to play around with, you can download it here. Or, if you want to try before you buy, you can play with it in the window below (it may be easier to click the button on the bottom right to view the workbook full screen, but you won't be able to make edits in that window):

**Bonus Round**

Yes, Virginia, there are graphs for this spreadsheet. They will autoupdate as you add information. There is one graph for the attribute points, so a teacher can look at the distribution and consider where to place additional instruction. The other graph shows the number of students performing in each category. This one could be useful for comparing pre- and post-assessments. Both would be good starting points for reflection on instructional practice.

**Update**: I did find an answer (similar to one suggested by a commenter) to the problem above. It is included in the updated spreadsheet. Use the same link to download. Thank you to Jamie for the inspiration!

A hacky fix is to do something like

ReplyDelete=IF(COUNTIF(P5:P14,"1 = Below Basic")=0,"",COUNTIF(P5:P14,"1 = Below Basic"))

I don't like having to repeat myself in the formula but it's certainly the easiest way to achieve what you're looking for.

Hmmm...for some reason, that formula isn't working for me. It does keep the zero from showing up when there is nothing to total, but when there is something to total, the box is still blank.

ReplyDeleteHuzzah! I used your formula as a starting point and was able to come up with one that would work in the spreadsheet. I used =IF(COUNTIF(P5:P14,"1 = Below Basic"),COUNTIF(P5:P14,"1 = Below Basic"),"")

ReplyDeleteThanks for putting me on the right path!

When I saw the latest issue of Educational Leadership, "For Each to Excel," I thought somehow you'd commandeered editorship. What a letdown to find out it was all about personalizing the educational experience! :)

ReplyDeleteA letdown, I'm sure; but ASCD did give a big shout out to Excel for Educators in its most recent Express issue.

ReplyDelete"A letdown, I'm sure; but ASCD did give a big shout out to Excel for Educators in its most recent Express issue."

ReplyDeleteOutstanding! :)

BTW, What's with all the goobers in my name? Blogger having an issue?

ReplyDeleteI don't know why it's freaking about about the ' in your last name. We must have encountered a time warp back to the ASCII days.

ReplyDelete