divide by zero error...

[doublejoy]

I am creating a 'weekly average spreadsheet' (Excel 2007) for a teacher. It has a column for each student's name and his test grade for each day (M-F). The last column gives his weekly average.

She also needs an average for all the students' for the week (vertically). My formula is correct, i.e., =AVERAGE(H2:H11). However, I'm getting a divide by zero error because I have some blank rows above my formula (so she can add new students' names as they come.

How can I get rid of the divide by zero error so she can see the running averages as she inputs grades (but still leaving empty rows for new names)?

Thanks so much for your help!

[martindwilson]

how about you ask your "teacher",sounds like a class exercise to me. lol
if not ,post a workbook with what is wrong,what you have and result you'd expect

[daddylonglegs]

You only get the error when there are no numbers in the range, so add a check like this

=IF(COUNT(H2:H11),AVERAGE(H2:H11),"")

[martindwilson]

dadylonglegs
i must get you to do my homework!!

[doublejoy]

how about you ask your "teacher",sounds like a class exercise to me. lol
if not ,post a workbook with what is wrong,what you have and result you'd expect

Wow, just wow. You really shouldn't assume.

[doublejoy]

You only get the error when there are no numbers in the range, so add a check like this

=IF(COUNT(H2:H11),AVERAGE(H2:H11),"")

Thank you for your help - unfortunately, it didn't resolve the issue, but I most definitely appreciate your assistance.

[doublejoy]

dadylonglegs
i must get you to do my homework!!

How rude and presumptuous.

[shg]

unfortunately, it didn't resolve the issue,

That was Martin's point about posting a workbook.

[martindwilson]

yep I'm good at rudeness and presumption but i did qualify by

if not ,post a workbook with what is wrong,what you have and result you'd expect

[doublejoy]

[QUOTE=martindwilson;2150140]yep I'm good at rudeness and presumption but i did qualify by[/QUOT

Most people would not be proud of those qualities. Do you realize that responses such as yours have a potential 'chilling effect' on people asking future questions? I came to this forum expecting to be helped with a basic Excel question - the purpose of such a forum. I didn't ask the question with the expectation that I would be accused of seeking help with 'homework'. How about you simply ignore my questions in the future?

[shg]

Neutral corners, Gentlemen, please.

[daddylonglegs]

Thank you for your help - unfortunately, it didn't resolve the issue,.....

Why not? What result did you get with that formula, perhaps I misunderstood your request.....

Normally AVERAGE function will only give #DIV/0! error if all cells in the range are blank, so my suggested formula is effectively saying "If there are numbers in the range, return the average, otherwise return a blank".

[doublejoy]

Why not? What result did you get with that formula, perhaps I misunderstood your request.....

Normally AVERAGE function will only give #DIV/0! error if all cells in the range are blank, so my suggested formula is effectively saying "If there are numbers in the range, return the average, otherwise return a blank".

I actually got a response from another Excel forum that worked. Here it is:

=IF(COUNT(C2:G2),AVERAGE(C2:G2),"")

I put this formula in H2 and then dragged it down to the other cells in the column. It gave me each student's average and allowed me to keep blank rows (so she could add/delete students as they come) yet still have a running average at all times. Most importantly, it removed the 'divide by zero' error.

I really appreciate your help with this and I wish I had explained it a bit clearer. I should have simply stated that I wanted to execute a formula that would accomplish this:

IF the number in H2 is zero, then leave H2 blank (no divide by zero), but,
IF the number in H2 is greater than zero, I want Excel to calculate (in H2) the AVERAGE of cells C2:G2


Thanks, daddylonglegs!