J
Jim S
What does #DIV/0! mean when it shows up in a cell with a formula?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Gord Dibben
Occurs when a number is divided by zero.
A1 contains 123
B1 contains nothing or 0
=A1/B1 will throw the #DIV/0! error.
You can trap the error using
=IF(AND(ISNUMBER(B1),(B1<>0)),A1/B1,"")
which will make the cell look blank if B1 is blank or 0 or not a number.
Gord Dibben MS Excel MVP
A1 contains 123
B1 contains nothing or 0
=A1/B1 will throw the #DIV/0! error.
You can trap the error using
=IF(AND(ISNUMBER(B1),(B1<>0)),A1/B1,"")
which will make the cell look blank if B1 is blank or 0 or not a number.
Gord Dibben MS Excel MVP
C
CLR
From the HELP..........
What does the error #DIV/0! mean?
The #DIV/0! error value occurs when a formula divides by 0 (zero).
Possible cause Suggested action
Using the cell reference to a blank cell or to a cell that contains zero as
a divisor. (If an operand is a cell that is blank, Microsoft Excel
interprets the blank as zero.) Change the cell reference, or enter a value
other than zero in the cell used as a divisor. You can enter the value #N/A
into the cell referenced as the divisor, which will change the result of the
formula to #N/A from #DIV/0! to denote that the divisor value is not
available.
Entering a formula that contains explicit division by zero (0) ¾ for
example, =5/0. Change the divisor to a number other than zero.
Running a macro that uses a function or a formula that returns #DIV/0!. Make
sure the divisor in the function or formula is not zero or blank.
Vaya con Dios,
Chuck, CABGx3
What does the error #DIV/0! mean?
The #DIV/0! error value occurs when a formula divides by 0 (zero).
Possible cause Suggested action
Using the cell reference to a blank cell or to a cell that contains zero as
a divisor. (If an operand is a cell that is blank, Microsoft Excel
interprets the blank as zero.) Change the cell reference, or enter a value
other than zero in the cell used as a divisor. You can enter the value #N/A
into the cell referenced as the divisor, which will change the result of the
formula to #N/A from #DIV/0! to denote that the divisor value is not
available.
Entering a formula that contains explicit division by zero (0) ¾ for
example, =5/0. Change the divisor to a number other than zero.
Running a macro that uses a function or a formula that returns #DIV/0!. Make
sure the divisor in the function or formula is not zero or blank.
Vaya con Dios,
Chuck, CABGx3
C
Chip Pearson
It means that your formula attempted to divide a number by 0, which is an
illegal operation. You can write your formula to circumvent the error with
something like the following:
=IF(B1=0,"",A1/B1)
or
=IF(your_formula = 0,"",your_formula)
or, in 2007,
=IFERROR(A1/B1,"")
illegal operation. You can write your formula to circumvent the error with
something like the following:
=IF(B1=0,"",A1/B1)
or
=IF(your_formula = 0,"",your_formula)
or, in 2007,
=IFERROR(A1/B1,"")
G
Guest
Hi Jim,
Say, a2=10 and a3=0.
You are trying to divide a number by 0 is in in cell a1 =a2/a3.
you can solve it by making sure that you do not divide by zero or if you do
not know if it will be zero try the following:
in cell a1 =if(a3=0,0,a2/a3) and this will put a 0 in place of the #div/0.
Say, a2=10 and a3=0.
You are trying to divide a number by 0 is in in cell a1 =a2/a3.
you can solve it by making sure that you do not divide by zero or if you do
not know if it will be zero try the following:
in cell a1 =if(a3=0,0,a2/a3) and this will put a 0 in place of the #div/0.
J
Jim S
OK, This is the formula that I have that is pulling data from all pages of a
multipage document and some of the cells in this formula are at 0 until data
is entered and other cells have data, how do I add the IF option to this
formula?
AVERAGE(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+SF1!F24+SF2!F24+SF3!F24+TAOS!F24)/9
--
Jim Salyer
Area Supervisor
Home 505-474-4863
Mobile 505-670-4138
Fax 505-474-4540
multipage document and some of the cells in this formula are at 0 until data
is entered and other cells have data, how do I add the IF option to this
formula?
AVERAGE(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+SF1!F24+SF2!F24+SF3!F24+TAOS!F24)/9
--
Jim Salyer
Area Supervisor
Home 505-474-4863
Mobile 505-670-4138
Fax 505-474-4540
R
Roger Govier
Hi Jim
It depends what you mean.
If you want the average of these cells 9 values, then it would be
=SUM(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+SF1!F24+SF2!F24+SF3!F24+TAOS!F24)/9
If those 9 cells sum to 0, then dividing by 9 will not give an error.
If you say
=AVERAGE(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+SF1!F24+SF2!F24+SF3!F24+TAOS!F24)
then if they sum to 0, you will get the #DIV0 error message (you don't
need the /9 on the end for this formula)
If the sheets are consecutive in the workbook, then you could use
=IF(SUM(ESP1:TAOS!F24)=0,"",AVERAGE(ESP1:TAOS!F24)
Alternatively you can create 2 dummy sheet called First and Last.
Drag these to positions which encompass the sheets you want to perform
the calculations upon, and use
=IF(SUM(first:last!F4)=0,"",AVERAGE(first:last!F24)
--
Regards
Roger Govier
It depends what you mean.
If you want the average of these cells 9 values, then it would be
=SUM(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+SF1!F24+SF2!F24+SF3!F24+TAOS!F24)/9
If those 9 cells sum to 0, then dividing by 9 will not give an error.
If you say
=AVERAGE(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+SF1!F24+SF2!F24+SF3!F24+TAOS!F24)
then if they sum to 0, you will get the #DIV0 error message (you don't
need the /9 on the end for this formula)
If the sheets are consecutive in the workbook, then you could use
=IF(SUM(ESP1:TAOS!F24)=0,"",AVERAGE(ESP1:TAOS!F24)
Alternatively you can create 2 dummy sheet called First and Last.
Drag these to positions which encompass the sheets you want to perform
the calculations upon, and use
=IF(SUM(first:last!F4)=0,"",AVERAGE(first:last!F24)
--
Regards
Roger Govier
Jim S said:OK, This is the formula that I have that is pulling data from all
pages of a multipage document and some of the cells in this formula
are at 0 until data is entered and other cells have data, how do I add
the IF option to this formula?
AVERAGE(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+SF1!F24+SF2!F24+SF3!F24+TAOS!F24)/9
--
Jim Salyer
Area Supervisor
Home 505-474-4863
Mobile 505-670-4138
Fax 505-474-4540
J
joeu2004
OK, This is the formula that I have that is pulling data from all pages of a
multipage document
AVERAGE(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+SF1!F24+
SF2!F24+SF3!F24+TAOS!F24)/9
First of all, you probably want:
AVERAGE(ESP1!F24,ESP2!F24,LV!F24,LA!F24,RAT!F24,SF1!F24,SF2!F24,SF3!
F24,TAOS!F24)
AVERAGE() will accept up to 30 arguments in this form.
some of the cells in this formula are at 0 until data
is entered and other cells have data
Then I suspect you have a serious problem either with the format of
those cells or with the cell references.
AVERAGE() probably returned #DIV/0! because AVERAGE() thinks that all
the cells are blank or contain non-numeric values (e.g. text). If the
cells __appear__ to have numeric values, check their formats.
J
joeu2004
Errata....
Oops, my bad! I was thinking of __my__ form of AVERAGE().
AVERAGE(A1+...+A9) works just fine if all cells in the range are blank
(or zero). There is no problem with AVERAGE() if all the arguments
sum to zero. If any cell is non-numeric, the expression will returns
#VALUE!, and so does AVERAGE().
First of all, you probably want:
AVERAGE(ESP1!F24,ESP2!F24,LV!F24,LA!F24,RAT!F24,SF1!F24,SF2!F24,SF3!
F24,TAOS!F24)
[....]
AVERAGE() probably returned #DIV/0! because AVERAGE() thinks that all
the cells are blank or contain non-numeric values (e.g. text).
Oops, my bad! I was thinking of __my__ form of AVERAGE().
AVERAGE(A1+...+A9) works just fine if all cells in the range are blank
(or zero). There is no problem with AVERAGE() if all the arguments
sum to zero. If any cell is non-numeric, the expression will returns
#VALUE!, and so does AVERAGE().
J
joeu2004
=AVERAGE(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+SF1!F24+
SF2!F24+SF3!F24+TAOS!F24)
then if they sum to 0, you will get the #DIV0 error message
Are you sure? Works fine for me when all cells are on the same sheet.
R
Roger Govier
Sorry,
I should have said if all cells in the range are blank, and therefore
sum to zero, then you get the #DIV/0 error.
If any of the cells contain a value, then COUNT will be at least 1 and
the error won't occur.
--
Regards
Roger Govier
Are you sure? Works fine for me when all cells are on the same sheet.
I should have said if all cells in the range are blank, and therefore
sum to zero, then you get the #DIV/0 error.
If any of the cells contain a value, then COUNT will be at least 1 and
the error won't occur.
--
Regards
Roger Govier
=AVERAGE(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+SF1!F24+
SF2!F24+SF3!F24+TAOS!F24)
then if they sum to 0, you will get the #DIV0 error message
Are you sure? Works fine for me when all cells are on the same sheet.
J
joeu2004
Sorry,
I should have said if all cells in the range are blank, and therefore
sum to zero, then you get the #DIV/0 error.
Not when I try it. AVERAGE(A1+A2+A3) works just fine when all 3 cells
are blank. I suspect you are making the same mistake that I made and
thinking of AVERAGE(A1,A2,A3), which does indeed fail with #DIV/0! if
all 3 cells are blank.
R
Roger Govier
Ah, yes I can see that AVERAGE(A1+A2+A3) would work, as the + between
each item is coercing a zero value for the blank cells.
I always use
=AVERAGE(A1:A100) as I wouldn't want to type all the plus's and with
some formulae would hit the number of characters limit.
My way would be
=IF(COUNT(A1:A100)>0,AVERAGE(A1:A100),"")
--
Regards
Roger Govier
Not when I try it. AVERAGE(A1+A2+A3) works just fine when all 3 cells
are blank. I suspect you are making the same mistake that I made and
thinking of AVERAGE(A1,A2,A3), which does indeed fail with #DIV/0! if
all 3 cells are blank.
each item is coercing a zero value for the blank cells.
I always use
=AVERAGE(A1:A100) as I wouldn't want to type all the plus's and with
some formulae would hit the number of characters limit.
My way would be
=IF(COUNT(A1:A100)>0,AVERAGE(A1:A100),"")
--
Regards
Roger Govier
Sorry,
I should have said if all cells in the range are blank, and therefore
sum to zero, then you get the #DIV/0 error.
Not when I try it. AVERAGE(A1+A2+A3) works just fine when all 3 cells
are blank. I suspect you are making the same mistake that I made and
thinking of AVERAGE(A1,A2,A3), which does indeed fail with #DIV/0! if
all 3 cells are blank.
J
Jim S
Wow what a response, thanks. Let me take the first reference in the formula
and explain, ESP1!F24 is a cell that also has a formula in its own cell that
asks for an average of 4 other cells C24, D24, and E24 on the same
worksheet, one of those cells has data inserted and the others dont but I
still get the #DIV/0! and I wanted this formula to reflect the existing data
from C even though the other cells are still without data, thats the reason
for using the average formula. I had put the /9 because it wasnt giving me
an average but a sum total even though it said average.
--
Jim Salyer
Area Supervisor
Home 505-474-4863
Mobile 505-670-4138
Fax 505-474-4540
Errata....
Oops, my bad! I was thinking of __my__ form of AVERAGE().
AVERAGE(A1+...+A9) works just fine if all cells in the range are blank
(or zero). There is no problem with AVERAGE() if all the arguments
sum to zero. If any cell is non-numeric, the expression will returns
#VALUE!, and so does AVERAGE().
and explain, ESP1!F24 is a cell that also has a formula in its own cell that
asks for an average of 4 other cells C24, D24, and E24 on the same
worksheet, one of those cells has data inserted and the others dont but I
still get the #DIV/0! and I wanted this formula to reflect the existing data
from C even though the other cells are still without data, thats the reason
for using the average formula. I had put the /9 because it wasnt giving me
an average but a sum total even though it said average.
--
Jim Salyer
Area Supervisor
Home 505-474-4863
Mobile 505-670-4138
Fax 505-474-4540
Errata....
First of all, you probably want:
AVERAGE(ESP1!F24,ESP2!F24,LV!F24,LA!F24,RAT!F24,SF1!F24,SF2!F24,SF3!
F24,TAOS!F24)
[....]
AVERAGE() probably returned #DIV/0! because AVERAGE() thinks that all
the cells are blank or contain non-numeric values (e.g. text).
Oops, my bad! I was thinking of __my__ form of AVERAGE().
AVERAGE(A1+...+A9) works just fine if all cells in the range are blank
(or zero). There is no problem with AVERAGE() if all the arguments
sum to zero. If any cell is non-numeric, the expression will returns
#VALUE!, and so does AVERAGE().
G
Guest
Substitute your cell references and you will get an average of those that
contain data. Be sure to press Ctrl/Shift/Enter since this is an array
formula.
=AVERAGE(IF(J9:J20<>0, L9:L20,""))
--
Best wishes,
Jim
contain data. Be sure to press Ctrl/Shift/Enter since this is an array
formula.
=AVERAGE(IF(J9:J20<>0, L9:L20,""))
--
Best wishes,
Jim
Jim S said:Wow what a response, thanks. Let me take the first reference in the formula
and explain, ESP1!F24 is a cell that also has a formula in its own cell that
asks for an average of 4 other cells C24, D24, and E24 on the same
worksheet, one of those cells has data inserted and the others dont but I
still get the #DIV/0! and I wanted this formula to reflect the existing data
from C even though the other cells are still without data, thats the reason
for using the average formula. I had put the /9 because it wasnt giving me
an average but a sum total even though it said average.
--
Jim Salyer
Area Supervisor
Home 505-474-4863
Mobile 505-670-4138
Fax 505-474-4540
Errata....
First of all, you probably want:
AVERAGE(ESP1!F24,ESP2!F24,LV!F24,LA!F24,RAT!F24,SF1!F24,SF2!F24,SF3!
F24,TAOÂS!F24)
[....]
AVERAGE() probably returned #DIV/0! because AVERAGE() thinks that all
the cells are blank or contain non-numeric values (e.g. text).
Oops, my bad! I was thinking of __my__ form of AVERAGE().
AVERAGE(A1+...+A9) works just fine if all cells in the range are blank
(or zero). There is no problem with AVERAGE() if all the arguments
sum to zero. If any cell is non-numeric, the expression will returns
#VALUE!, and so does AVERAGE().