View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Go Bucks!!![_2_] Go Bucks!!![_2_] is offline
external usenet poster
 
Posts: 28
Default Get rid of #DIV/0! without repeating denominator?

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!



"Ashish Mathur" wrote:

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

"Go Bucks!!!" wrote in message
...
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,