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


--
Gazzr
------------------------------------------------------------------------
Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075
View this thread: http://www.excelforum.com/showthread...hreadid=538761

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?

"tearingoutmyhair" wrote:

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

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,""))



"tearingoutmyhair" wrote:

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

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tearingoutmyhair" wrote in
message ...
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,""))



"tearingoutmyhair" wrote:

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

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.



"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tearingoutmyhair" wrote in
message ...
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,""))



"tearingoutmyhair" wrote:

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

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)

"tearingoutmyhair" wrote in
message ...
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.



"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tearingoutmyhair" wrote in
message ...
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,""))



"tearingoutmyhair" wrote:

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

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.

"Bob Phillips" wrote:

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)

"tearingoutmyhair" wrote in
message ...
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.



"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tearingoutmyhair" wrote in
message ...
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,""))



"tearingoutmyhair" wrote:

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

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!

"Bob Phillips" wrote:

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)

"tearingoutmyhair" wrote in
message ...
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.



"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tearingoutmyhair" wrote in
message ...
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,""))



"tearingoutmyhair" wrote:

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

Give me a virtual hug <G

Bob

"tearingoutmyhair" wrote in
message ...
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!

"Bob Phillips" wrote:

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)

"tearingoutmyhair" wrote in
message ...
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.



"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tearingoutmyhair"
wrote in
message ...
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,""))



"tearingoutmyhair" wrote:

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

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.

"Bob Phillips" wrote:

Give me a virtual hug <G

Bob

"tearingoutmyhair" wrote in
message ...
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!

"Bob Phillips" wrote:

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)

"tearingoutmyhair" wrote in
message ...
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.



"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tearingoutmyhair"
wrote in
message ...
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,""))



"tearingoutmyhair" wrote:

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

I did.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tearingoutmyhair" wrote in
message ...
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.

"Bob Phillips" wrote:

Give me a virtual hug <G

Bob

"tearingoutmyhair" wrote in
message ...
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!

"Bob Phillips" wrote:

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)

"tearingoutmyhair"
wrote in
message ...
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.



"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tearingoutmyhair"
wrote in
message
...
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,""))



"tearingoutmyhair" wrote:

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