#div/0! how do i supress?

Hi vipa,

Could you please show the formula that returns the error to be surpressed?

Thx,
KL


"vipa2000" <[email protected]> wrote in message
news:[email protected]...
> On one of my worksheets I am creating a standard report that is doing
> quite a
> number of calculations. On occassions my data in certain areas will be
> null.
> For example cell H34 is returning the error because it has been looking
> for
> type 5 priorities and there are none. I am a fussy person and want to
> suppress the error message as it looks untidy. I have looked at a few
> threads
> and cannot quite find one that sorts this out. Thanks up front to all you
> patient people out there that take your time out to help others.
> --
> Regards vipa

Hi, Vipa. It's ever so helpful to provide an edited formula if we have your
original formula, but here's a shot at it:

=if(iserror(yourformula),"",yourformula)
*******************
~Anne Troy

www.OfficeArticles.com


"vipa2000" <[email protected]> wrote in message
news:[email protected]...
> On one of my worksheets I am creating a standard report that is doing
quite a
> number of calculations. On occassions my data in certain areas will be
null.
> For example cell H34 is returning the error because it has been looking
for
> type 5 priorities and there are none. I am a fussy person and want to
> suppress the error message as it looks untidy. I have looked at a few
threads
> and cannot quite find one that sorts this out. Thanks up front to all you
> patient people out there that take your time out to help others.
> --
> Regards vipa

Sorry KL and Anne, did not think in this instance it would be as critical.

cell h34 contains: =$H32/$H31

h32 =
=SUMPRODUCT((Sheet1!$C$2:$C$30000=5)*(Sheet1!$D$2:$D$30000="")*(MONTH(Sheet1!$I$2:$I$30000)=$F3)*(YEAR(Sheet1!$I$2:$I$30000)=$H3)*ISNUMBER(SEARCH({"*TECO*","*CLSD*"},Sheet1!$E$2:$E$30000)))

KL you might recognise it!

h31 = h9 which is the result of
=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=5),--(Sheet1!$D$2:$D$30000=""),--(MONTH(Sheet1!$I$2:$I$30000)=F3),--(YEAR(Sheet1!$I$2:$I$30000)=H3))

--
Regards vipa


"KL" wrote:

> Hi vipa,
>
> Could you please show the formula that returns the error to be surpressed?
>
> Thx,
> KL
>
>
> "vipa2000" <[email protected]> wrote in message
> news:[email protected]...
> > On one of my worksheets I am creating a standard report that is doing
> > quite a
> > number of calculations. On occassions my data in certain areas will be
> > null.
> > For example cell H34 is returning the error because it has been looking
> > for
> > type 5 priorities and there are none. I am a fussy person and want to
> > suppress the error message as it looks untidy. I have looked at a few
> > threads
> > and cannot quite find one that sorts this out. Thanks up front to all you
> > patient people out there that take your time out to help others.
> > --
> > Regards vipa
>
>
>

Hi Anne

please see respone to KL
--
Regards vipa


"Anne Troy" wrote:

> Hi, Vipa. It's ever so helpful to provide an edited formula if we have your
> original formula, but here's a shot at it:
>
> =if(iserror(yourformula),"",yourformula)
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
>
>
> "vipa2000" <[email protected]> wrote in message
> news:[email protected]...
> > On one of my worksheets I am creating a standard report that is doing
> quite a
> > number of calculations. On occassions my data in certain areas will be
> null.
> > For example cell H34 is returning the error because it has been looking
> for
> > type 5 priorities and there are none. I am a fussy person and want to
> > suppress the error message as it looks untidy. I have looked at a few
> threads
> > and cannot quite find one that sorts this out. Thanks up front to all you
> > patient people out there that take your time out to help others.
> > --
> > Regards vipa
>
>
>

Hi vipa,

Generally Anne's solution is the one to use. However, in this case it can be
slightly shorter:

=IF($H31,$H32/$H31,0)

Regards,
KL


"vipa2000" <[email protected]> wrote in message
news:[email protected]...
> Sorry KL and Anne, did not think in this instance it would be as critical.
>
> cell h34 contains: =$H32/$H31
>
> h32 =
> =SUMPRODUCT((Sheet1!$C$2:$C$30000=5)*(Sheet1!$D$2:$D$30000="")*(MONTH(Sheet1!$I$2:$I$30000)=$F3)*(YEAR(Sheet1!$I$2:$I$30000)=$H3)*ISNUMBER(SEARCH({"*TECO*","*CLSD*"},Sheet1!$E$2:$E$30000)))
>
> KL you might recognise it!
>
> h31 = h9 which is the result of
> =SUMPRODUCT(--(Sheet1!$C$2:$C$30000=5),--(Sheet1!$D$2:$D$30000=""),--(MONTH(Sheet1!$I$2:$I$30000)=F3),--(YEAR(Sheet1!$I$2:$I$30000)=H3))
>
> --
> Regards vipa
>
>
> "KL" wrote:
>
>> Hi vipa,
>>
>> Could you please show the formula that returns the error to be
>> surpressed?
>>
>> Thx,
>> KL
>>
>>
>> "vipa2000" <[email protected]> wrote in message
>> news:[email protected]...
>> > On one of my worksheets I am creating a standard report that is doing
>> > quite a
>> > number of calculations. On occassions my data in certain areas will be
>> > null.
>> > For example cell H34 is returning the error because it has been looking
>> > for
>> > type 5 priorities and there are none. I am a fussy person and want to
>> > suppress the error message as it looks untidy. I have looked at a few
>> > threads
>> > and cannot quite find one that sorts this out. Thanks up front to all
>> > you
>> > patient people out there that take your time out to help others.
>> > --
>> > Regards vipa
>>
>>
>>

Hi vipa,

Could you please show the formula that returns the error to be surpressed?

Thx,
KL


"vipa2000" <[email protected]> wrote in message
news:[email protected]...
> On one of my worksheets I am creating a standard report that is doing
> quite a
> number of calculations. On occassions my data in certain areas will be
> null.
> For example cell H34 is returning the error because it has been looking
> for
> type 5 priorities and there are none. I am a fussy person and want to
> suppress the error message as it looks untidy. I have looked at a few
> threads
> and cannot quite find one that sorts this out. Thanks up front to all you
> patient people out there that take your time out to help others.
> --
> Regards vipa

Hi, Vipa. It's ever so helpful to provide an edited formula if we have your
original formula, but here's a shot at it:

=if(iserror(yourformula),"",yourformula)
*******************
~Anne Troy

www.OfficeArticles.com


"vipa2000" <[email protected]> wrote in message
news:[email protected]...
> On one of my worksheets I am creating a standard report that is doing
quite a
> number of calculations. On occassions my data in certain areas will be
null.
> For example cell H34 is returning the error because it has been looking
for
> type 5 priorities and there are none. I am a fussy person and want to
> suppress the error message as it looks untidy. I have looked at a few
threads
> and cannot quite find one that sorts this out. Thanks up front to all you
> patient people out there that take your time out to help others.
> --
> Regards vipa

Sorry KL and Anne, did not think in this instance it would be as critical.

cell h34 contains: =$H32/$H31

h32 =
=SUMPRODUCT((Sheet1!$C$2:$C$30000=5)*(Sheet1!$D$2:$D$30000="")*(MONTH(Sheet1!$I$2:$I$30000)=$F3)*(YEAR(Sheet1!$I$2:$I$30000)=$H3)*ISNUMBER(SEARCH({"*TECO*","*CLSD*"},Sheet1!$E$2:$E$30000)))

KL you might recognise it!

h31 = h9 which is the result of
=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=5),--(Sheet1!$D$2:$D$30000=""),--(MONTH(Sheet1!$I$2:$I$30000)=F3),--(YEAR(Sheet1!$I$2:$I$30000)=H3))

--
Regards vipa


"KL" wrote:

> Hi vipa,
>
> Could you please show the formula that returns the error to be surpressed?
>
> Thx,
> KL
>
>
> "vipa2000" <[email protected]> wrote in message
> news:[email protected]...
> > On one of my worksheets I am creating a standard report that is doing
> > quite a
> > number of calculations. On occassions my data in certain areas will be
> > null.
> > For example cell H34 is returning the error because it has been looking
> > for
> > type 5 priorities and there are none. I am a fussy person and want to
> > suppress the error message as it looks untidy. I have looked at a few
> > threads
> > and cannot quite find one that sorts this out. Thanks up front to all you
> > patient people out there that take your time out to help others.
> > --
> > Regards vipa
>
>
>

Hi Anne

please see respone to KL
--
Regards vipa


"Anne Troy" wrote:

> Hi, Vipa. It's ever so helpful to provide an edited formula if we have your
> original formula, but here's a shot at it:
>
> =if(iserror(yourformula),"",yourformula)
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
>
>
> "vipa2000" <[email protected]> wrote in message
> news:[email protected]...
> > On one of my worksheets I am creating a standard report that is doing
> quite a
> > number of calculations. On occassions my data in certain areas will be
> null.
> > For example cell H34 is returning the error because it has been looking
> for
> > type 5 priorities and there are none. I am a fussy person and want to
> > suppress the error message as it looks untidy. I have looked at a few
> threads
> > and cannot quite find one that sorts this out. Thanks up front to all you
> > patient people out there that take your time out to help others.
> > --
> > Regards vipa
>
>
>

Hi vipa,

Generally Anne's solution is the one to use. However, in this case it can be
slightly shorter:

=IF($H31,$H32/$H31,0)

Regards,
KL


"vipa2000" <[email protected]> wrote in message
news:[email protected]...
> Sorry KL and Anne, did not think in this instance it would be as critical.
>
> cell h34 contains: =$H32/$H31
>
> h32 =
> =SUMPRODUCT((Sheet1!$C$2:$C$30000=5)*(Sheet1!$D$2:$D$30000="")*(MONTH(Sheet1!$I$2:$I$30000)=$F3)*(YEAR(Sheet1!$I$2:$I$30000)=$H3)*ISNUMBER(SEARCH({"*TECO*","*CLSD*"},Sheet1!$E$2:$E$30000)))
>
> KL you might recognise it!
>
> h31 = h9 which is the result of
> =SUMPRODUCT(--(Sheet1!$C$2:$C$30000=5),--(Sheet1!$D$2:$D$30000=""),--(MONTH(Sheet1!$I$2:$I$30000)=F3),--(YEAR(Sheet1!$I$2:$I$30000)=H3))
>
> --
> Regards vipa
>
>
> "KL" wrote:
>
>> Hi vipa,
>>
>> Could you please show the formula that returns the error to be
>> surpressed?
>>
>> Thx,
>> KL
>>
>>
>> "vipa2000" <[email protected]> wrote in message
>> news:[email protected]...
>> > On one of my worksheets I am creating a standard report that is doing
>> > quite a
>> > number of calculations. On occassions my data in certain areas will be
>> > null.
>> > For example cell H34 is returning the error because it has been looking
>> > for
>> > type 5 priorities and there are none. I am a fussy person and want to
>> > suppress the error message as it looks untidy. I have looked at a few
>> > threads
>> > and cannot quite find one that sorts this out. Thanks up front to all
>> > you
>> > patient people out there that take your time out to help others.
>> > --
>> > Regards vipa
>>
>>
>>

On one of my worksheets I am creating a standard report that is doing quite a
number of calculations. On occassions my data in certain areas will be null.
For example cell H34 is returning the error because it has been looking for
type 5 priorities and there are none. I am a fussy person and want to
suppress the error message as it looks untidy. I have looked at a few threads
and cannot quite find one that sorts this out. Thanks up front to all you
patient people out there that take your time out to help others.
--
Regards vipa

Hi vipa,

Could you please show the formula that returns the error to be surpressed?

Thx,
KL


"vipa2000" <[email protected]> wrote in message
news:[email protected]...
> On one of my worksheets I am creating a standard report that is doing
> quite a
> number of calculations. On occassions my data in certain areas will be
> null.
> For example cell H34 is returning the error because it has been looking
> for
> type 5 priorities and there are none. I am a fussy person and want to
> suppress the error message as it looks untidy. I have looked at a few
> threads
> and cannot quite find one that sorts this out. Thanks up front to all you
> patient people out there that take your time out to help others.
> --
> Regards vipa

Hi, Vipa. It's ever so helpful to provide an edited formula if we have your
original formula, but here's a shot at it:

=if(iserror(yourformula),"",yourformula)
*******************
~Anne Troy

www.OfficeArticles.com


"vipa2000" <[email protected]> wrote in message
news:[email protected]...
> On one of my worksheets I am creating a standard report that is doing
quite a
> number of calculations. On occassions my data in certain areas will be
null.
> For example cell H34 is returning the error because it has been looking
for
> type 5 priorities and there are none. I am a fussy person and want to
> suppress the error message as it looks untidy. I have looked at a few
threads
> and cannot quite find one that sorts this out. Thanks up front to all you
> patient people out there that take your time out to help others.
> --
> Regards vipa

Sorry KL and Anne, did not think in this instance it would be as critical.

cell h34 contains: =$H32/$H31

h32 =
=SUMPRODUCT((Sheet1!$C$2:$C$30000=5)*(Sheet1!$D$2:$D$30000="")*(MONTH(Sheet1!$I$2:$I$30000)=$F3)*(YEAR(Sheet1!$I$2:$I$30000)=$H3)*ISNUMBER(SEARCH({"*TECO*","*CLSD*"},Sheet1!$E$2:$E$30000)))

KL you might recognise it!

h31 = h9 which is the result of
=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=5),--(Sheet1!$D$2:$D$30000=""),--(MONTH(Sheet1!$I$2:$I$30000)=F3),--(YEAR(Sheet1!$I$2:$I$30000)=H3))

--
Regards vipa


"KL" wrote:

> Hi vipa,
>
> Could you please show the formula that returns the error to be surpressed?
>
> Thx,
> KL
>
>
> "vipa2000" <[email protected]> wrote in message
> news:[email protected]...
> > On one of my worksheets I am creating a standard report that is doing
> > quite a
> > number of calculations. On occassions my data in certain areas will be
> > null.
> > For example cell H34 is returning the error because it has been looking
> > for
> > type 5 priorities and there are none. I am a fussy person and want to
> > suppress the error message as it looks untidy. I have looked at a few
> > threads
> > and cannot quite find one that sorts this out. Thanks up front to all you
> > patient people out there that take your time out to help others.
> > --
> > Regards vipa
>
>
>

Hi Anne

please see respone to KL
--
Regards vipa


"Anne Troy" wrote:

> Hi, Vipa. It's ever so helpful to provide an edited formula if we have your
> original formula, but here's a shot at it:
>
> =if(iserror(yourformula),"",yourformula)
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
>
>
> "vipa2000" <[email protected]> wrote in message
> news:[email protected]...
> > On one of my worksheets I am creating a standard report that is doing
> quite a
> > number of calculations. On occassions my data in certain areas will be
> null.
> > For example cell H34 is returning the error because it has been looking
> for
> > type 5 priorities and there are none. I am a fussy person and want to
> > suppress the error message as it looks untidy. I have looked at a few
> threads
> > and cannot quite find one that sorts this out. Thanks up front to all you
> > patient people out there that take your time out to help others.
> > --
> > Regards vipa
>
>
>

Hi vipa,

Generally Anne's solution is the one to use. However, in this case it can be
slightly shorter:

=IF($H31,$H32/$H31,0)

Regards,
KL


"vipa2000" <[email protected]> wrote in message
news:[email protected]...
> Sorry KL and Anne, did not think in this instance it would be as critical.
>
> cell h34 contains: =$H32/$H31
>
> h32 =
> =SUMPRODUCT((Sheet1!$C$2:$C$30000=5)*(Sheet1!$D$2:$D$30000="")*(MONTH(Sheet1!$I$2:$I$30000)=$F3)*(YEAR(Sheet1!$I$2:$I$30000)=$H3)*ISNUMBER(SEARCH({"*TECO*","*CLSD*"},Sheet1!$E$2:$E$30000)))
>
> KL you might recognise it!
>
> h31 = h9 which is the result of
> =SUMPRODUCT(--(Sheet1!$C$2:$C$30000=5),--(Sheet1!$D$2:$D$30000=""),--(MONTH(Sheet1!$I$2:$I$30000)=F3),--(YEAR(Sheet1!$I$2:$I$30000)=H3))
>
> --
> Regards vipa
>
>
> "KL" wrote:
>
>> Hi vipa,
>>
>> Could you please show the formula that returns the error to be
>> surpressed?
>>
>> Thx,
>> KL
>>
>>
>> "vipa2000" <[email protected]> wrote in message
>> news:[email protected]...
>> > On one of my worksheets I am creating a standard report that is doing
>> > quite a
>> > number of calculations. On occassions my data in certain areas will be
>> > null.
>> > For example cell H34 is returning the error because it has been looking
>> > for
>> > type 5 priorities and there are none. I am a fussy person and want to
>> > suppress the error message as it looks untidy. I have looked at a few
>> > threads
>> > and cannot quite find one that sorts this out. Thanks up front to all
>> > you
>> > patient people out there that take your time out to help others.
>> > --
>> > Regards vipa
>>
>>
>>