incorporate the remove #DIV/0! code into another formula to get bl
 

hi there, i have this formula below, and i somehow want to incorporate this
=IFERROR(B1/C1,"") into it with the purpose of having the cell display
nothing instead of #DIV/0! so that the column of data can then allow an index
and match formula to work as it won't if any of the cells have #DIV/0! in
them, any suggestion?

=(SUMIF($B$2:$B$2500,"=" & T58,$M$2:$M$2500)-SUMIF($B$2:$B$2500,"=" &
(T58+TIMEVALUE("1:00")),$M$2:$M$2500))/(SUMIF($B$2:$B$2500,"=" &
T58,$D$2:$D$2500)-SUMIF($B$2:$B$2500,"=" & (T58+
TIMEVALUE("1:00")),$D$2:$D$2500))


--
thanks

incorporate the remove #DIV/0! code into another formula to get bl
 

What version of Excel are you using?

The portion of the formula that will cause the #DIV/0! error is when this
evaluates to 0:

(SUMIF($B$2:$B$2500,"=" &T58,$D$2:$D$2500)-SUMIF($B$2:$B$2500,"=" &
(T58+TIMEVALUE("1:00")),$D$2:$D$2500))

That can result in 0 for 2 reasons:

Either there are no time entries that meet the criteria or,

There are time entries that meet the criteria but the values (if any) sum to
0.

incorporate the remove #DIV/0! code into another formula to ge
 

hi, i'm using excel2007 and at the moment there are no time entries, but
eventually they will fill up, i want to use an index and match formula
similar to this =INDEX(P43:P54,MATCH(MAX(R43:R54),R43:R54,0)) which is
dependent on there being no #DIV/0! errors, since it will be a while till the
cells fill up with data, i was wondering if the #DIV/0! can be removed so the
index and match formula will work?

thanks
--
thanks


"T. Valko" wrote:

What version of Excel are you using?

The portion of the formula that will cause the #DIV/0! error is when this
evaluates to 0:

(SUMIF($B$2:$B$2500,"=" &T58,$D$2:$D$2500)-SUMIF($B$2:$B$2500,"=" &
(T58+TIMEVALUE("1:00")),$D$2:$D$2500))

That can result in 0 for 2 reasons:

Either there are no time entries that meet the criteria or,

There are time entries that meet the criteria but the values (if any) sum to
0.

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
hi there, i have this formula below, and i somehow want to incorporate
this
=IFERROR(B1/C1,"") into it with the purpose of having the cell display
nothing instead of #DIV/0! so that the column of data can then allow an
index
and match formula to work as it won't if any of the cells have #DIV/0! in
them, any suggestion?

=(SUMIF($B$2:$B$2500,"=" & T58,$M$2:$M$2500)-SUMIF($B$2:$B$2500,"=" &
(T58+TIMEVALUE("1:00")),$M$2:$M$2500))/(SUMIF($B$2:$B$2500,"=" &
T58,$D$2:$D$2500)-SUMIF($B$2:$B$2500,"=" & (T58+
TIMEVALUE("1:00")),$D$2:$D$2500))


--
thanks


.

incorporate the remove #DIV/0! code into another formula to ge
 

i'm using excel2007

Ok, that makes things a lot easier!

Just put your formula inside the IFERROR function:

=IFERROR((SUMIF($B$2:$B$2500,"="&T58,$M$2:$M$2500 )-SUMIF($B$2:$B$2500,"="&(T58+TIMEVALUE("1:00")),$M $2:$M$2500))/(SUMIF($B$2:$B$2500,"="&T58,$D$2:$D$2500)-SUMIF($B$2:$B$2500,"="&(T58+TIMEVALUE("1:00")),$D $2:$D$2500)),"")

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
hi, i'm using excel2007 and at the moment there are no time entries, but
eventually they will fill up, i want to use an index and match formula
similar to this =INDEX(P43:P54,MATCH(MAX(R43:R54),R43:R54,0)) which is
dependent on there being no #DIV/0! errors, since it will be a while till
the
cells fill up with data, i was wondering if the #DIV/0! can be removed so
the
index and match formula will work?

thanks
--
thanks


"T. Valko" wrote:

What version of Excel are you using?

The portion of the formula that will cause the #DIV/0! error is when this
evaluates to 0:

(SUMIF($B$2:$B$2500,"=" &T58,$D$2:$D$2500)-SUMIF($B$2:$B$2500,"=" &
(T58+TIMEVALUE("1:00")),$D$2:$D$2500))

That can result in 0 for 2 reasons:

Either there are no time entries that meet the criteria or,

There are time entries that meet the criteria but the values (if any) sum
to
0.

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
hi there, i have this formula below, and i somehow want to incorporate
this
=IFERROR(B1/C1,"") into it with the purpose of having the cell display
nothing instead of #DIV/0! so that the column of data can then allow an
index
and match formula to work as it won't if any of the cells have #DIV/0!
in
them, any suggestion?

=(SUMIF($B$2:$B$2500,"=" & T58,$M$2:$M$2500)-SUMIF($B$2:$B$2500,"=" &
(T58+TIMEVALUE("1:00")),$M$2:$M$2500))/(SUMIF($B$2:$B$2500,"=" &
T58,$D$2:$D$2500)-SUMIF($B$2:$B$2500,"=" & (T58+
TIMEVALUE("1:00")),$D$2:$D$2500))


--
thanks


.

incorporate the remove #DIV/0! code into another formula to ge
 

that was so simple, lol, thank you for your help! cheers



"T. Valko" wrote:

i'm using excel2007

Ok, that makes things a lot easier!

Just put your formula inside the IFERROR function:

=IFERROR((SUMIF($B$2:$B$2500,"="&T58,$M$2:$M$2500 )-SUMIF($B$2:$B$2500,"="&(T58+TIMEVALUE("1:00")),$M $2:$M$2500))/(SUMIF($B$2:$B$2500,"="&T58,$D$2:$D$2500)-SUMIF($B$2:$B$2500,"="&(T58+TIMEVALUE("1:00")),$D $2:$D$2500)),"")

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
hi, i'm using excel2007 and at the moment there are no time entries, but
eventually they will fill up, i want to use an index and match formula
similar to this =INDEX(P43:P54,MATCH(MAX(R43:R54),R43:R54,0)) which is
dependent on there being no #DIV/0! errors, since it will be a while till
the
cells fill up with data, i was wondering if the #DIV/0! can be removed so
the
index and match formula will work?

thanks
--
thanks


"T. Valko" wrote:

What version of Excel are you using?

The portion of the formula that will cause the #DIV/0! error is when this
evaluates to 0:

(SUMIF($B$2:$B$2500,"=" &T58,$D$2:$D$2500)-SUMIF($B$2:$B$2500,"=" &
(T58+TIMEVALUE("1:00")),$D$2:$D$2500))

That can result in 0 for 2 reasons:

Either there are no time entries that meet the criteria or,

There are time entries that meet the criteria but the values (if any) sum
to
0.

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
hi there, i have this formula below, and i somehow want to incorporate
this
=IFERROR(B1/C1,"") into it with the purpose of having the cell display
nothing instead of #DIV/0! so that the column of data can then allow an
index
and match formula to work as it won't if any of the cells have #DIV/0!
in
them, any suggestion?

=(SUMIF($B$2:$B$2500,"=" & T58,$M$2:$M$2500)-SUMIF($B$2:$B$2500,"=" &
(T58+TIMEVALUE("1:00")),$M$2:$M$2500))/(SUMIF($B$2:$B$2500,"=" &
T58,$D$2:$D$2500)-SUMIF($B$2:$B$2500,"=" & (T58+
TIMEVALUE("1:00")),$D$2:$D$2500))


--
thanks


.



.

incorporate the remove #DIV/0! code into another formula to ge
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
that was so simple, lol, thank you for your help! cheers



"T. Valko" wrote:

i'm using excel2007

Ok, that makes things a lot easier!

Just put your formula inside the IFERROR function:

=IFERROR((SUMIF($B$2:$B$2500,"="&T58,$M$2:$M$2500 )-SUMIF($B$2:$B$2500,"="&(T58+TIMEVALUE("1:00")),$M $2:$M$2500))/(SUMIF($B$2:$B$2500,"="&T58,$D$2:$D$2500)-SUMIF($B$2:$B$2500,"="&(T58+TIMEVALUE("1:00")),$D $2:$D$2500)),"")

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
hi, i'm using excel2007 and at the moment there are no time entries,
but
eventually they will fill up, i want to use an index and match formula
similar to this =INDEX(P43:P54,MATCH(MAX(R43:R54),R43:R54,0)) which is
dependent on there being no #DIV/0! errors, since it will be a while
till
the
cells fill up with data, i was wondering if the #DIV/0! can be removed
so
the
index and match formula will work?

thanks
--
thanks


"T. Valko" wrote:

What version of Excel are you using?

The portion of the formula that will cause the #DIV/0! error is when
this
evaluates to 0:

(SUMIF($B$2:$B$2500,"=" &T58,$D$2:$D$2500)-SUMIF($B$2:$B$2500,"=" &
(T58+TIMEVALUE("1:00")),$D$2:$D$2500))

That can result in 0 for 2 reasons:

Either there are no time entries that meet the criteria or,

There are time entries that meet the criteria but the values (if any)
sum
to
0.

--
Biff
Microsoft Excel MVP


"Morgan" wrote in message
...
hi there, i have this formula below, and i somehow want to
incorporate
this
=IFERROR(B1/C1,"") into it with the purpose of having the cell
display
nothing instead of #DIV/0! so that the column of data can then allow
an
index
and match formula to work as it won't if any of the cells have
#DIV/0!
in
them, any suggestion?

=(SUMIF($B$2:$B$2500,"=" &
T58,$M$2:$M$2500)-SUMIF($B$2:$B$2500,"=" &
(T58+TIMEVALUE("1:00")),$M$2:$M$2500))/(SUMIF($B$2:$B$2500,"=" &
T58,$D$2:$D$2500)-SUMIF($B$2:$B$2500,"=" & (T58+
TIMEVALUE("1:00")),$D$2:$D$2500))


--
thanks


.



.