Get rid of #DIV/0! without repeating denominator?

G

Go Bucks!!!

I have some very long denominator formulas and these formulas are repeated
many times. Is there an alternate method to avoid the #DIV/O! error WITHOUT
repeating the denominator?


=IF((denominator formula)=0,"",(numerator formula/denominator formula).

One of my actual formulas broken down is...


=IF

DENOMINATOR...

(SUMPRODUCT(('Call Activity'!$E:$E=$D16)*('Call Activity'!$C:$C="CHEM
LIME")*(ISNUMBER(MATCH('Call Activity'!$Q:$Q,{0,1},0))

=0,"",

NUMERATOR...

(SUMPRODUCT(('Call Activity'!$E:$E=$D16)*('Call Activity'!$C:$C="CHEM
LIME")*('Call Activity'!$Q:$Q=1))

/

DENOMINATOR...

(SUMPRODUCT(('Call Activity'!$E:$E=$D16)*('Call Activity'!$C:$C="CHEM
LIME")*(ISNUMBER(MATCH('Call Activity'!$Q:$Q,{0,1},0)))))))


Thanks,
 
T

T. Valko

I you're using Excel 2007 it's pretty easy:

=IFERROR(your_formula(...),"")

If the formula does not return #DIV/0! what is the typical range of results
you expect? Would a result of 0 be OK instead of blank?
 
J

JoeU2004

Go Bucks!!! said:
I have some very long denominator formulas and these formulas are repeated
many times. Is there an alternate method to avoid the #DIV/O! error
WITHOUT
repeating the denominator?

That depends on the revision of Excel that you want to design for. Excel
2007 has an IFERROR function. Otherwise, I would compute the denominator in
a helper cell, which can be hidden.


----- original message -----
 
R

Ron@Buy

Is the cause of the error #DIV/0 always caused by the same cell of column?
As a suggestion, you could preceed your formula with something like
=IF(D16=0,"",your formula) or =IF(SUM(C:C)=0,"",your formula)
 
A

Ashish Mathur

Hi,

If you want to conceal the error value, you may make use of conditional
formatting. In Format > Conditional formatting > Formula Is, type the
following formula

=iserror($A4). This assumes that the first cell is $A4

Now click on Format and colour the font to white. Click on OK twice. Now
copy the cell and paste special conditional formatting in the cells below

Please note that this technique will only colour the font to white I.e. the
value in the cell will still be an error.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
G

Go Bucks!!!

The IFERROR works for me, but this is a very clever approach Ashish.
Instead of spending a lot of time trying to fix a problem - format it away.
I love it.

Thanks!
 
G

Go Bucks!!!

Hi T,

Its just a simple average formula. I wanted to perform the same work as the
IF(denominator = 0,""), but I didnt want to have to write out the whole
denominator formula. This is exactly what I was looking for. Zero or Blank
works great.

Thanks!!!
 
C

Chris Oosthuizen

Your advice gave me an idea to replicate the new formula provided in 2007 in older version of Excel.

Copy the following code into one of the worksheet modules to get the same capabilities as the function provided in 2007
:
Function IFERROR(Formula1a, Alternate1a)

' Formula1a is the formula to test for errors
' Alternate1a is the variable returned if an error is
' detected

' iferror Macro
'
temp1a = Formula1a

If IsError(temp1a) Then
iferror = Alternate1a
Else
iferror = Formula1a
End If

'
End Function



T. Valko wrote:

Re: Get rid of #DIV/0! without repeating denominator?
02-Oct-09

I you are using Excel 2007 it is pretty easy

=IFERROR(your_formula(...),""

If the formula does not return #DIV/0! what is the typical range of result
you expect? Would a result of 0 be OK instead of blank

-
Bif
Microsoft Excel MVP

Previous Posts In This Thread:

Get rid of #DIV/0! without repeating denominator?
I have some very long denominator formulas and these formulas are repeate
many times. Is there an alternate method to avoid the #DIV/O! error WITHOU
repeating the denominator

=IF((denominator formula)=0,"",(numerator formula/denominator formula)

One of my actual formulas broken down is..

=I

DENOMINATOR..

(SUMPRODUCT(('Call Activity'!$E:$E=$D16)*('Call Activity'!$C:$C="CHE
LIME")*(ISNUMBER(MATCH('Call Activity'!$Q:$Q,{0,1},0)

=0,""

NUMERATOR..

(SUMPRODUCT(('Call Activity'!$E:$E=$D16)*('Call Activity'!$C:$C="CHE
LIME")*('Call Activity'!$Q:$Q=1)



DENOMINATOR..

(SUMPRODUCT(('Call Activity'!$E:$E=$D16)*('Call Activity'!$C:$C="CHE
LIME")*(ISNUMBER(MATCH('Call Activity'!$Q:$Q,{0,1},0))))))

Thanks,

Re: Get rid of #DIV/0! without repeating denominator?
I you are using Excel 2007 it is pretty easy

=IFERROR(your_formula(...),""

If the formula does not return #DIV/0! what is the typical range of result
you expect? Would a result of 0 be OK instead of blank

-
Bif
Microsoft Excel MVP

"Go Bucks!!!
That depends on the revision of Excel that you want to design for. Exce
2007 has an IFERROR function. Otherwise, I would compute the denominator i
a helper cell, which can be hidden

----- original message -----

Is the cause of the error #DIV/0 always caused by the same cell of column?
Is the cause of the error #DIV/0 always caused by the same cell of column
As a suggestion, you could preceed your formula with something lik
=IF(D16=0,"",your formula) or =IF(SUM(C:C)=0,"",your formula

:

Hi,If you want to conceal the error value, you may make use of conditional
Hi

If you want to conceal the error value, you may make use of conditiona
formatting. In Format > Conditional formatting > Formula Is, type th
following formul

=iserror($A4). This assumes that the first cell is $A

Now click on Format and colour the font to white. Click on OK twice. No
copy the cell and paste special conditional formatting in the cells belo

Please note that this technique will only colour the font to white I.e. th
value in the cell will still be an error

-
Regards

Ashish Mathu
Microsoft Excel MV
www.ashishmathur.com

The IFERROR works for me, but this is a very clever approach Ashish.
The IFERROR works for me, but this is a very clever approach Ashish
Instead of spending a lot of time trying to fix a problem - format it away
I love it

Thanks


:

Hi T,Its just a simple average formula.
Hi T

Its just a simple average formula. I wanted to perform the same work as th
IF(denominator = 0,""), but I didnt want to have to write out the whol
denominator formula. This is exactly what I was looking for. Zero or Blan
works great.

Thanks!!!


:

Glad you liked it-- Regards,Ashish MathurMicrosoft Excel MVPwww.ashishmathur.
Glad you liked it

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

You're welcome. Thanks for the feedback!-- BiffMicrosoft Excel MVP"Go Bucks!!!
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Submitted via EggHeadCafe - Software Developer Portal of Choice
Parallel Programming in C# 4.0: A Short Synopsis
http://www.eggheadcafe.com/tutorial...4-b0f20bdc955a/parallel-programming-in-c.aspx
 
C

Chris Oosthuizen

Your advice gave me an idea to replicate the new formula provided in 2007 in older version of Excel.

Copy the following code into one of the worksheet modules to get the same capabilities as the function provided in 2007
:
Function IFERROR(Formula1a, Alternate1a)

' Formula1a is the formula to test for errors
' Alternate1a is the variable returned if an error is
' detected

' iferror Macro
'
temp1a = Formula1a

If IsError(temp1a) Then
iferror = Alternate1a
Else
iferror = Formula1a
End If

'
End Function



T. Valko wrote:

Re: Get rid of #DIV/0! without repeating denominator?
02-Oct-09

I you are using Excel 2007 it is pretty easy

=IFERROR(your_formula(...),""

If the formula does not return #DIV/0! what is the typical range of result
you expect? Would a result of 0 be OK instead of blank

-
Bif
Microsoft Excel MVP

Previous Posts In This Thread:

Get rid of #DIV/0! without repeating denominator?
I have some very long denominator formulas and these formulas are repeate
many times. Is there an alternate method to avoid the #DIV/O! error WITHOU
repeating the denominator

=IF((denominator formula)=0,"",(numerator formula/denominator formula)

One of my actual formulas broken down is..

=I

DENOMINATOR..

(SUMPRODUCT(('Call Activity'!$E:$E=$D16)*('Call Activity'!$C:$C="CHE
LIME")*(ISNUMBER(MATCH('Call Activity'!$Q:$Q,{0,1},0)

=0,""

NUMERATOR..

(SUMPRODUCT(('Call Activity'!$E:$E=$D16)*('Call Activity'!$C:$C="CHE
LIME")*('Call Activity'!$Q:$Q=1)



DENOMINATOR..

(SUMPRODUCT(('Call Activity'!$E:$E=$D16)*('Call Activity'!$C:$C="CHE
LIME")*(ISNUMBER(MATCH('Call Activity'!$Q:$Q,{0,1},0))))))

Thanks,

Re: Get rid of #DIV/0! without repeating denominator?
I you are using Excel 2007 it is pretty easy

=IFERROR(your_formula(...),""

If the formula does not return #DIV/0! what is the typical range of result
you expect? Would a result of 0 be OK instead of blank

-
Bif
Microsoft Excel MVP

"Go Bucks!!!
That depends on the revision of Excel that you want to design for. Exce
2007 has an IFERROR function. Otherwise, I would compute the denominator i
a helper cell, which can be hidden

----- original message -----

Is the cause of the error #DIV/0 always caused by the same cell of column?
Is the cause of the error #DIV/0 always caused by the same cell of column
As a suggestion, you could preceed your formula with something lik
=IF(D16=0,"",your formula) or =IF(SUM(C:C)=0,"",your formula

:

Hi,If you want to conceal the error value, you may make use of conditional
Hi

If you want to conceal the error value, you may make use of conditiona
formatting. In Format > Conditional formatting > Formula Is, type th
following formul

=iserror($A4). This assumes that the first cell is $A

Now click on Format and colour the font to white. Click on OK twice. No
copy the cell and paste special conditional formatting in the cells belo

Please note that this technique will only colour the font to white I.e. th
value in the cell will still be an error

-
Regards

Ashish Mathu
Microsoft Excel MV
www.ashishmathur.com

The IFERROR works for me, but this is a very clever approach Ashish.
The IFERROR works for me, but this is a very clever approach Ashish
Instead of spending a lot of time trying to fix a problem - format it away
I love it

Thanks


:

Hi T,Its just a simple average formula.
Hi T

Its just a simple average formula. I wanted to perform the same work as th
IF(denominator = 0,""), but I didnt want to have to write out the whol
denominator formula. This is exactly what I was looking for. Zero or Blan
works great.

Thanks!!!


:

Glad you liked it-- Regards,Ashish MathurMicrosoft Excel MVPwww.ashishmathur.
Glad you liked it

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

You're welcome. Thanks for the feedback!-- BiffMicrosoft Excel MVP"Go Bucks!!!
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Get rid of #DIV/0! without repeating denominator? For older versions of Excel
Your advice gave me an idea to replicate the new formula provided in 2007 in older version of Excel.

Copy the following code into one of the worksheet modules to get the same capabilities as the function provided in 2007
:
Function IFERROR(Formula1a, Alternate1a)

' Formula1a is the formula to test for errors
' Alternate1a is the variable returned if an error is
' detected

' iferror Macro
'
temp1a = Formula1a

If IsError(temp1a) Then
iferror = Alternate1a
Else
iferror = Formula1a
End If

'
End Function


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Binding Beyond the Limitation of Name Scopes
http://www.eggheadcafe.com/tutorial...f-49faac8854c8/wpf-binding-beyond-the-li.aspx
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top