Getting percentile averages without the #DIV/0! errors!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to get an average of a months worth of percentages and then the
average for the year. But some months have that column with all blank cells
and I get the above mentioned error because it can't divide zeros or blank
cells, I've tried EVERY formula I could think of or find applicable in the
Help! but am still coming up with the same thing.

If anyone knows:
1. How to ignore blank cells/zeros in cells and keep the average formula at
the end of the month for a total?
or
2. How to average 12 months of average totals some with errors and ignoring
those errors?

This might have been confusing but I've been up almost 24 hours with 4 hours
of sleep. Thanks
 
Hi there,

I'm not sure if the ISERROR(Value) function will help

Paste this formula into cell H34.

Basically if the result of I34/J34 returns an error put nothing in cell
H34 otherwise it will process the forumla

=IF(ISERROR(I34/J34)=TRUE,"",(I34/J34))

You will have to adjust the cell ranges to suite your needs etc.

Thanks
gazzr
 
What formula(e) are you using? The AVERAGE function ignores blanks and will
cater for a column of ALL 0s. However, it will include zeros (as opposed to
blank) in its calculation.

Can you post your formulae?
 
This is the only formula I found that works the best if numbers are punched
in. THis spreadsheet needs all the formulas perfect because I'm maiking it
for someone who doesn't know anything about excel and wants to be able to
just punch in the numbers and have all the desired figures to add up.

=AVERAGE(IF(AE19:AE49<>0, AE19:AE49,""))
 
=IF(0=0,"",AVERAGE(IF(AE19:AE49<>0, AE19:AE49,"")))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
I think that is really close but it makes just a blank space, however if I
change

=IF(""=0,"",AVERAGE(IF(AE19:AE49<>0, AE19:AE49,"")))

it works where there are numbers applied but still if there is a full column
of blank spaces it still gives me the #DIV/0! error, I need to get it to
allow 0 to be divided by 0 somehow or have it recognize that as a rule in the
formula.

Thanks so much for helping though I do appreciate it.
 
Unfortunately, I tested the formula and evaluated the first part, and didn't
re-instate it. It should have been

=IF(COUNT(AE19:AE49)=0,"",AVERAGE(IF(AE19:AE49<>0, AE19:AE49,"")))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
For some reason when I punch that in it just comes up as a blank cell. *sigh*

I don't know what it is, but it just wont let me ignore the blank cells in
the column.

Again thanks for your help. I've been trying to get this for almost 12 hours
now.
 
You know what... I take that back I didn't try it on the column with numbers.
You rock!

Thanks so much!!! I'd give you a hug if I could!
 
Give me a virtual hug <G>

Bob

tearingoutmyhair said:
You know what... I take that back I didn't try it on the column with numbers.
You rock!

Thanks so much!!! I'd give you a hug if I could!
Click to expand...
 
I'll give you the biggest damn virtual hug in the world if you can answer my
other question.

Actuallly it was kind of part of this question.

I need to get a total average of each column that I averaged, and now it's
giving me pretty much the same error, cuz some of thos are empty and I don't
know how to include non-adjacent cells.

You're pretty much a rockstar in my book.
 
Back
Top