A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

#DIV/0!



 
 
Thread Tools Display Modes
  #1  
Old March 8th, 2007, 01:46 AM posted to microsoft.public.excel.newusers
Jim S[_2_]
external usenet poster
 
Posts: 11
Default #DIV/0!

What does #DIV/0! mean when it shows up in a cell with a formula?

--
Jim S


  #2  
Old March 8th, 2007, 02:14 AM posted to microsoft.public.excel.newusers
Gord Dibben
external usenet poster
 
Posts: 20,252
Default #DIV/0!

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),(B10)),A1/B1,"")

which will make the cell look blank if B1 is blank or 0 or not a number.


Gord Dibben MS Excel MVP

On Wed, 7 Mar 2007 17:46:53 -0700, "Jim S" wrote:

What does #DIV/0! mean when it shows up in a cell with a formula?


  #3  
Old March 8th, 2007, 02:15 AM posted to microsoft.public.excel.newusers
CLR
external usenet poster
 
Posts: 323
Default #DIV/0!

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


"Jim S" wrote in message
...
What does #DIV/0! mean when it shows up in a cell with a formula?

--
Jim S




  #4  
Old March 8th, 2007, 02:16 AM posted to microsoft.public.excel.newusers
Chip Pearson
external usenet poster
 
Posts: 1,343
Default #DIV/0!

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




"Jim S" wrote in message
...
What does #DIV/0! mean when it shows up in a cell with a formula?

--
Jim S




  #5  
Old March 8th, 2007, 02:19 AM posted to microsoft.public.excel.newusers
Martin Fishlock
external usenet poster
 
Posts: 428
Default #DIV/0!

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Jim S" wrote:

What does #DIV/0! mean when it shows up in a cell with a formula?

--
Jim S



  #6  
Old March 9th, 2007, 01:04 AM posted to microsoft.public.excel.newusers
Jim S[_2_]
external usenet poster
 
Posts: 11
Default #DIV/0!

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+SF 1!F24+SF2!F24+SF3!F24+TAOS!F24)/9

--
Jim Salyer
Area Supervisor
Home 505-474-4863
Mobile 505-670-4138
Fax 505-474-4540
"Jim S" wrote in message
...
What does #DIV/0! mean when it shows up in a cell with a formula?

--
Jim S




  #7  
Old March 9th, 2007, 01:21 AM posted to microsoft.public.excel.newusers
Roger Govier
external usenet poster
 
Posts: 2,602
Default #DIV/0!

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!F 24+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+S F1!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" wrote in message
...
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+SF 1!F24+SF2!F24+SF3!F24+TAOS!F24)/9

--
Jim Salyer
Area Supervisor
Home 505-474-4863
Mobile 505-670-4138
Fax 505-474-4540
"Jim S" wrote in message
...
What does #DIV/0! mean when it shows up in a cell with a formula?

--
Jim S






  #8  
Old March 9th, 2007, 03:12 AM posted to microsoft.public.excel.newusers
joeu2004
external usenet poster
 
Posts: 1,748
Default #DIV/0!

On Mar 8, 4:04 pm, "Jim S" wrote:
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+SF 1!F24+
SF2!F24+SF3!F24+TAO*S!F24)/9


First of all, you probably want:

AVERAGE(ESP1!F24,ESP2!F24,LV!F24,LA!F24,RAT!F24,SF 1!F24,SF2!F24,SF3!
F24,TAO*S!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.

  #9  
Old March 9th, 2007, 03:54 AM posted to microsoft.public.excel.newusers
joeu2004
external usenet poster
 
Posts: 1,748
Default #DIV/0!

Errata....

On Mar 8, 6:12 pm, "joeu2004" wrote:
First of all, you probably want:
AVERAGE(ESP1!F24,ESP2!F24,LV!F24,LA!F24,RAT!F24,SF 1!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().

  #10  
Old March 9th, 2007, 03:56 AM posted to microsoft.public.excel.newusers
joeu2004
external usenet poster
 
Posts: 1,748
Default #DIV/0!

On Mar 8, 4:21 pm, "Roger Govier"
wrote:
=AVERAGE(ESP1!F24+ESP2!F24+LV!F24+LA!F24+RAT!F24+S F1!F24+
SF2!F24+SF3!F24+TA*OS!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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:13 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.