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

  • Thread starter Thread starter Morgan
  • Start date Start date
M

Morgan

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))
 
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.
 
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
 
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)),"")
 
Back
Top