Blank cell issues

Hi Barbie,

In order to stop the #DIV/0! being displayed you could add a test to your formula.
This is the revised formula for cell E286.

=IF(ISERROR(AVERAGE(G6,M6,S6,AE6,AK6,G100,M100,S100,Y100,AE100,AK100,G193)),0,AVERAGE(G6,M6,S6,AE6,AK6,G100,M100,S100,Y100,AE100,AK100,G193))

If the ISERROR is true then 0 (zero) is displayed, otherwise the result of average is displayed.

If the sheet works for you then it's fine.

Cheers
Andy

Hi - welcome to the board!

I took a look at the attachment. The answer to your question "Is my whole calculating basically flawed from the start?" is yes (from a design point of biew) - although if it serves it's general purpose I guess it's OK.

As you have discovered, the way you have set up your data is giving you headaches. Here is not the place to go into the elements of good spreadsheet design, but the folliowing links might prove of interest:

http://spreadsheetstyle.com/

and

http://bsstudents.uce.ac.uk/le…ourse%20Material/smbp.pdf

although the latter is a rather large document. also, do a search on the web for "first normal form", read some of the articles & see how it might affect your design descisions.

more specifically, you are not getting div/0 errors because of the NN's and NA's. dividing a number by text values returns the #value! error. You're getting them because you're dividing by zero.

One way to get round this is to check for this condition before you perform the calculations:

rather than

=AVERAGE(G34,M34,S34,AE34,AK34,G128,M128,S128,Y128,AE128,AK128,G221)

try:

=if(sum(G34,M34,S34,AE34,AK34,G128,M128,S128,Y128,AE128,AK128,G221)=0,"Whatever",AVERAGE(G34,M34,S34,AE34,AK34,G128,M128,S128,Y128,AE128,AK128,G221))

...substitute "something" with whatever you want returned instead of div/0.

There are other alternatives, but given the 'complexities' of your spreadsheet design, there probably not worth discussiong until we know whether you intend to re-consider the way you're data is set up.

HTH

paddy

Andy,

Just a (no doubt unnecessary) note of caution for the OP re your suggested:

=IF(ISERROR(AVERAGE(G6,M6,S6,AE6,AK6,G100,M100,S100,Y100,AE100,AK100,G193)),0,AVERAGE(G6,M6,S6,AE6,AK6,G100,M100,S100,Y100,AE100,AK100,G193))

iserror() will mask all errors, including syntactical ones in the formula construction. In geeneral, error checks are best made as specific as possible:

if(error.type(AVERAGE(G6,M6,S6,AE6,AK6,G100,M100,S100,Y100,AE100,AK100,G193))=2,0,AVERAGE(G6,M6,S6,AE6,AK6,G100,M100,S100,Y100,AE100,AK100,G193))

paddy

Hi Paddyd,

Fair point.
I couldn't find a ISDIV function and now I know why. :duh:

For information here are the values returned by Error.Type

If error_val is ERROR.TYPE returns 
#NULL!   1 
#DIV/0!   2 
#VALUE!   3 
#REF!   4 
#NAME?   5 
#NUM!   6 
#N/A   7 
Anything else #N/A

Cheers
Andy