# Blank cell issues

• OK, heres my problem. I have as an example 10 clients. These clients are graded on their improvement. 1 to 1 = 0%, 1 to 2 = 50%, 1 to 3 = 100% and 2 to 3=50% etc in 13 seperate categories.
=IF(F6=" "," ",(IF(F6=0,"0%",((IF(F6=1,50%,100%))))))
Some clients dont need services or someone else does the service for them. These entries are NN (not needed) or NA (not available).
=IF(E6=""," ",(E6-C6))
Leaving a blank instead of a 0%
My problem is this: I am taking the percentages from each client in each of the 13 different categories and averaging them out per client.
=Average(.....
(Client #1 has improved 60% on total as an example)
One client hasnt improved at all. There are no entries for it to average. All the services were either NN or NA so I'm getting a #DIV/0! error. I somehow need it to show as a 0%. I'm no excel wiz so I need some direction here. Any help would be appreciated! Thanks!!
P.S. Is my whole calculating basically flawed from the start?? If anyone wants to see the whole thing I've attached it too.
:cat:

• 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

[h4]Cheers
Andy
[/h4]

• 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:

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

• 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))

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

Code
``````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

[h4]Cheers
Andy
[/h4]

• Thanks alot for the help guys. I'm sure I'll spend a good part of the day tomorrow trying this stuff out. I'll also check out that site for an improved design. I'm not too analytical and its the best I could come up with but I'm ALWAYS open to any new suggestions. I'll post back and let you know how it went. Thanks again!!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!