Average non-contiguous cells, ignore blank cells and avoid #DIV/0!

Hi everyone

This is my first post, and I doubt I will contribute anything anytime soon as I am only as proficient at Excel as I need to be, which has been to a quite basic level. Now I need some help with a problem that my tiny brain cannot cope with.

In Excel 2010, I am trying to average just three non-contiguous cells without leaving the dreaded #DIV/0! error, as it prevents some other cells from making their calculations. Some of the cells may be left blank, and the value may be entered into any one of the three cells to be valued i.e. I may need to place a value in the second cell and leave the others blank, but I still need the correct sum in the averaging cell, and don't want the #DIV/0! when they are all left blank.

I hope this makes sense.

Thanks for your time.

David

Thanks guys

It works when I add a value to all three cells, but when I only use one or two cells, the average result is incorrect unfortunately. I know it may sound odd to want a an average formula when there might be only one or two cells used out of the three, but that is the nature of my work I'm afraid.

It seems either I get the functionality I need but get the error message when the cells are blank or at zero, or the error message is removed, but the cells don not calculate right when only some have values.

Sorry to be a pain.

Hi Marcol

I don't think so, as I don't want to have to change the formula each time I vary the number of cells with a value (as I said before, can be one, two or all three with values or left blank). I'd like to have a formula that can take account of any number of blank or value cells, and return the correct result with having to edit it each time.

Appreciate your help though.

Hi daddylonglegs

6 of course. I always want either the average of two or more values, or the equal of one cell if just one value is entered. It's to do with recording my workings out as much as anything, and removing human error as much as possible, hence the auto calculation of Excel seems ideal.

Sure,

G11 = 0, I11 = 5, J11 = 7 result(in K11) = 4

Try this

=SUM(A1,C1,E1) / COUNT(A1,C1,E1)

Or, because I'm not testing this out... not sure which will work...

=SUM(A1,C1,E1) / ( COUNT(A1,C1,E1) - COUNTBLANK(A1,C1,E1) )

Daddylonglegs

You're the man! Sorry I missed that part of your post (probably didn't scroll down enough to see it)! And apologies if I didn't give you very precise information.

Was beginning to think there was no solution to this problem, so a very big thank you for your perseverance.

I am one happy Excel user (again).

A1 C1 F1 K1 V1
1 ____ 0 ____ 2
0 ____ 0 ____ 0

I want to average these columns and display a result 0 if there is 0 or ____ in entire calculation.

How can i do that for a row that has 0 and a blank? For second row it dispalys error if i use a formula.