| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 . |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 . . |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Incorporate a condition in an IF((OR €¦.formula | Excel Discussion (Misc queries) | |||
| How do I incorporate a formula to calculate many months releases? | Excel Worksheet Functions | |||
| How do I incorporate letters as numbers in a formula? | Excel Discussion (Misc queries) | |||
| IF formula help - incorporate greater than & smaller than | Excel Worksheet Functions | |||
| Way to Incorporate Trim Into Match/Index Formula | Excel Worksheet Functions | |||
Linear Mode
