simple user-friendly spreadsheet for some 1st-grade teachers

[redskyluv]

Hi Everyone,

Okay, I'm trying to create a simple user-friendly spreadsheet for some 1st-grade teachers. All they have to do is enter in the raw scores and the rest will be done for them. Usually, I don't have a problem. but I'm stumbling a bit on this one.

I'm using this as the formula. =COUNTIFS(B7:B32,"1", G7:G32, "'>=19")
It works unless there is a blank cell then I get the #Div/0 error. Which would be fine but I have to compile the data from 5 separate sheets onto one master with all the results.

I then use the result from that formula and divide by 24 points possible to get the average.

This is what is needed. Students who meet certain criteria and then those scores would be average out. I have a class of 24 students, 3 meet the criteria. I need to know how many passed with a 19 or more and the average 3 test scores. If no students met the criteria then it can remain blank without the #div/0.

Need - Number of students who meet criteria, Thier Average, and % passing 19/24

Hoping someone can help me.

Thank you for all that you do!

[AliGW]

To get rid of the #DIV/0 message, try this:

=IFERROR(COUNTIFS(B7:B32,"1", G7:G32, ">=19"),"")

or

=IF(ISERROR(COUNTIFS(B7:B32,"1", G7:G32, ">=19"),"",COUNTIFS(B7:B32,"1", G7:G32, ">=19"))

If you are wanting any more help with the spreadsheet, then you will need to attach it here.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jason.b75]

I'm using this as the formula. =COUNTIFS(B7:B32,"1", G7:G32, "'>=19")
It works unless there is a blank cell then I get the #Div/0 error.

That formula alone will not ever return a #DIV/0 error, even if the errors exist in the data columns.

If you do have that error, then something else is causing it.

[AliGW]

It could be the formula itself:

=COUNTIFS(B7:B32,"1", G7:G32, "'>=19")

It should be this:

=COUNTIFS(B7:B32,"1", G7:G32, ">=19")

[jason.b75]

Good catch, Ali, but that would just be evaluated as a text string that is trying to count cells that contain ''>=19 (note 2 single inverted commas, not 1 double!)

To be more specific, you have to do some kind of division by zero to get a #DIV/0 error, there is no division of any kind in the formula.

COUNTIFS will also ignore any #DIV/0 errors in the data being counted, so it is not coming from there.

[AliGW]

True enough, Jason. Hopefully we'll get to see the workbook.