Home |
Search |
Today's Posts |
#1
|
|||
|
|||
when dragging formula how do i avoid #DIV/0!
When dragging a formula and a field has a nil value is there a way to
avoid the result showing as #DIV/0!. Can it just skip this or return a 0 value or leave blank. |
#2
|
|||
|
|||
when dragging formula how do i avoid #DIV/0!
Dividing by zero is undefined in math hence the error.
Use the If function to test for a zero divisor: Suppose you have a value in Column A and another value in Column B and in Column C you have the formula A5/B5. If B5 is empty - has a value of zero - or has some formula that results in a zero, then your formula in Column C will result in #DIV/0! Solve it by replacing the formula in Column C with this: =IF(B5=0,"",Ad/B5) What this does is checks to see if it is OK to do the calculation before it does it. And if it is NOT OK then it shows nothing - a blank - in the cell. That is what the double Quotes "" does. TTFN JMMach "derwood" wrote in message oups.com... When dragging a formula and a field has a nil value is there a way to avoid the result showing as #DIV/0!. Can it just skip this or return a 0 value or leave blank. |
#3
|
|||
|
|||
when dragging formula how do i avoid #DIV/0!
OPPS
My formula should have read: =IF(B5=0,"",A5/B5) Sorry about that TTFN JMMach "JMMach" wrote in message ... Dividing by zero is undefined in math hence the error. Use the If function to test for a zero divisor: Suppose you have a value in Column A and another value in Column B and in Column C you have the formula A5/B5. If B5 is empty - has a value of zero - or has some formula that results in a zero, then your formula in Column C will result in #DIV/0! Solve it by replacing the formula in Column C with this: =IF(B5=0,"",Ad/B5) What this does is checks to see if it is OK to do the calculation before it does it. And if it is NOT OK then it shows nothing - a blank - in the cell. That is what the double Quotes "" does. TTFN JMMach "derwood" wrote in message oups.com... When dragging a formula and a field has a nil value is there a way to avoid the result showing as #DIV/0!. Can it just skip this or return a 0 value or leave blank. |
#4
|
|||
|
|||
when dragging formula how do i avoid #DIV/0!
You can also test for an error by entering:
=IF(IsError(A5/B5),"",A5/B5) "JMMach" wrote in message ... Dividing by zero is undefined in math hence the error. Use the If function to test for a zero divisor: Suppose you have a value in Column A and another value in Column B and in Column C you have the formula A5/B5. If B5 is empty - has a value of zero - or has some formula that results in a zero, then your formula in Column C will result in #DIV/0! Solve it by replacing the formula in Column C with this: =IF(B5=0,"",Ad/B5) What this does is checks to see if it is OK to do the calculation before it does it. And if it is NOT OK then it shows nothing - a blank - in the cell. That is what the double Quotes "" does. TTFN JMMach "derwood" wrote in message oups.com... When dragging a formula and a field has a nil value is there a way to avoid the result showing as #DIV/0!. Can it just skip this or return a 0 value or leave blank. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide formula | Excel Worksheet Functions | |||
Formula works in some cells, doesn't in other | New Users to Excel | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) |