If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
#DIV/0! How do I remove this answer?!?!?
Sorry Rebekah.
I might have thought of another simple solution: If the values that are supposed to be in the cells are numerical, then set the default pen colour for that cell the same as the fill colour (for example, black pen on black background. Then using "Condtional Formatting" instruct Excel to place any value greater than zero in white 'ink'. In that way the errors will not show as they will be the same colour as the background, and any value higjher than 0 will show as white (or whatever colour you choose.) "Rebekah" wrote: Popey, this doesn't work... It gets rid of the green arrow from the cornor but doesn't remove the #DIV/1! text and replace with a zero or blank cell... "Popey" wrote: I don't know if I am reading this right, but you do not want the "#DIV/0!" to show? Why not simply select all the errors in that column, open the drop down menu that should appear immediately to the left of the error, and select "ignore error"? This will make all those annoying error messages in the selected column disappear. It's what I do, and it works effectively. Saves all that messing about with altering/adding complex formulas for the same result. "Rebekah" wrote: I have multiple columns of data in another sheet and am using this formula (entered as an array) to provide an average based on conditions, one column reads "2a", and another reads "38" etc. i thought that by by entering the IF formula to include "0", this would count all the data and return a zero value for a blank or zero value. (I certainly hope this is making sense to somebody!!!) =AVERAGE(IF('Summary Days'!$D$3:$D$65536="2A",(IF('Summary Days'!$K$3:$K$65536=38,(IF('Summary Days'!$O$3:$O$1500"0",'Summary Days'!$O$3:$O$1500)))))) This formula works if in all columns there is a value above zero, but returns "#DIV/0!" if not. This would be ok if my table wasn't presenting data for a fixed period.... Please help!!! |
#12
|
|||
|
|||
#DIV/0! How do I remove this answer?!?!?
Max wrote...
One way Try, array-entered: =IF(ISERROR(AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A") *('Summary Days'!$K$3:$K$1500=38) *('Summary Days'!$O$3:$O$15000),'Summary Days'!$O$3:$O$1500))), "",AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A") *('Summary Days'!$K$3:$K$1500=38) *('Summary Days'!$O$3:$O$15000),'Summary Days'!$O$3:$O$1500))) .... Too inclusive. The OP's formula would only have returned #DIV/0! if there had been no cells matching the criteria. That's easily tested using COUNT. =IF(COUNT(('Summary Days'!$D$3:$D$1500="2A") *('Summary Days'!$K$3:$K$1500=38) *ISNUMBER('Summary Days'!$O$3:$O$1500)), AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A") *('Summary Days'!$K$3:$K$1500=38) 'Summary Days'!$O$3:$O$1500)),"") This has the advantage of propagating error values in these ranges, if any, as well as not masking errors arising from nonconforming ranges. |
#13
|
|||
|
|||
#DIV/0! How do I remove this answer?!?!?
How about just hidding the error response by conditionally formatting the
text white? I run into this often, and have found this solution really good (most of the time). Formula for the conditional format is like: =ISERROR(A2) Then just set the text color to white in the "format" area. |
#14
|
|||
|
|||
#DIV/0! How do I remove this answer?!?!?
Hi
Still having problems... Sorry to bother you again! Max's result worked as long as the result was supposed to be zero, not if the result was 1 or above... I have tried the following as you suggested, but this again is throwing back an error message. I have tried entering the formula with parenthesis around the last section (in case you omitted this in error) but it still won't work? =IF(COUNT(('Summary Days'!$D$3:$D$1500="2A")*('Summary Days'$K$3:$K$1500=38)*ISNUMBER('Summary Days'!$O$3:$O$1500)),AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A")*('Summary Days'!$K$3:$K$1500=38)'Summary Days'!$O$3:$O$1500)),"") Any more suggestions?!?!?! Beks "Harlan Grove" wrote: Max wrote... One way Try, array-entered: =IF(ISERROR(AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A") *('Summary Days'!$K$3:$K$1500=38) *('Summary Days'!$O$3:$O$15000),'Summary Days'!$O$3:$O$1500))), "",AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A") *('Summary Days'!$K$3:$K$1500=38) *('Summary Days'!$O$3:$O$15000),'Summary Days'!$O$3:$O$1500))) .... Too inclusive. The OP's formula would only have returned #DIV/0! if there had been no cells matching the criteria. That's easily tested using COUNT. =IF(COUNT(('Summary Days'!$D$3:$D$1500="2A") *('Summary Days'!$K$3:$K$1500=38) *ISNUMBER('Summary Days'!$O$3:$O$1500)), AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A") *('Summary Days'!$K$3:$K$1500=38) 'Summary Days'!$O$3:$O$1500)),"") This has the advantage of propagating error values in these ranges, if any, as well as not masking errors arising from nonconforming ranges. |
#15
|
|||
|
|||
#DIV/0! How do I remove this answer?!?!?
Two IFs, only one comma. I think Harlan may have missed a bit when he was
copying the formula. I haven't been through the formula in detail, but comparing it with the earlier version I wonder whether it may be missing the part that says *('Summary Days'!$O$3:$O$15000), towards the end ? -- David Biddulph "Rebekah" wrote in message ... Hi Still having problems... Sorry to bother you again! Max's result worked as long as the result was supposed to be zero, not if the result was 1 or above... I have tried the following as you suggested, but this again is throwing back an error message. I have tried entering the formula with parenthesis around the last section (in case you omitted this in error) but it still won't work? =IF(COUNT(('Summary Days'!$D$3:$D$1500="2A")*('Summary Days'$K$3:$K$1500=38)*ISNUMBER('Summary Days'!$O$3:$O$1500)),AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A")*('Summary Days'!$K$3:$K$1500=38)'Summary Days'!$O$3:$O$1500)),"") Any more suggestions?!?!?! Beks "Harlan Grove" wrote: Max wrote... One way Try, array-entered: =IF(ISERROR(AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A") *('Summary Days'!$K$3:$K$1500=38) *('Summary Days'!$O$3:$O$15000),'Summary Days'!$O$3:$O$1500))), "",AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A") *('Summary Days'!$K$3:$K$1500=38) *('Summary Days'!$O$3:$O$15000),'Summary Days'!$O$3:$O$1500))) .... Too inclusive. The OP's formula would only have returned #DIV/0! if there had been no cells matching the criteria. That's easily tested using COUNT. =IF(COUNT(('Summary Days'!$D$3:$D$1500="2A") *('Summary Days'!$K$3:$K$1500=38) *ISNUMBER('Summary Days'!$O$3:$O$1500)), AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A") *('Summary Days'!$K$3:$K$1500=38) 'Summary Days'!$O$3:$O$1500)),"") This has the advantage of propagating error values in these ranges, if any, as well as not masking errors arising from nonconforming ranges. |
#16
|
|||
|
|||
#DIV/0! How do I remove this answer?!?!?
thank you so much for everyone's help with this, i think I have fixed it with
a very untidy formula, although it appears to work, so I am not complaining!!!! "David Biddulph" wrote: Two IFs, only one comma. I think Harlan may have missed a bit when he was copying the formula. I haven't been through the formula in detail, but comparing it with the earlier version I wonder whether it may be missing the part that says *('Summary Days'!$O$3:$O$15000), towards the end ? -- David Biddulph "Rebekah" wrote in message ... Hi Still having problems... Sorry to bother you again! Max's result worked as long as the result was supposed to be zero, not if the result was 1 or above... I have tried the following as you suggested, but this again is throwing back an error message. I have tried entering the formula with parenthesis around the last section (in case you omitted this in error) but it still won't work? =IF(COUNT(('Summary Days'!$D$3:$D$1500="2A")*('Summary Days'$K$3:$K$1500=38)*ISNUMBER('Summary Days'!$O$3:$O$1500)),AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A")*('Summary Days'!$K$3:$K$1500=38)'Summary Days'!$O$3:$O$1500)),"") Any more suggestions?!?!?! Beks "Harlan Grove" wrote: Max wrote... One way Try, array-entered: =IF(ISERROR(AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A") *('Summary Days'!$K$3:$K$1500=38) *('Summary Days'!$O$3:$O$15000),'Summary Days'!$O$3:$O$1500))), "",AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A") *('Summary Days'!$K$3:$K$1500=38) *('Summary Days'!$O$3:$O$15000),'Summary Days'!$O$3:$O$1500))) .... Too inclusive. The OP's formula would only have returned #DIV/0! if there had been no cells matching the criteria. That's easily tested using COUNT. =IF(COUNT(('Summary Days'!$D$3:$D$1500="2A") *('Summary Days'!$K$3:$K$1500=38) *ISNUMBER('Summary Days'!$O$3:$O$1500)), AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A") *('Summary Days'!$K$3:$K$1500=38) 'Summary Days'!$O$3:$O$1500)),"") This has the advantage of propagating error values in these ranges, if any, as well as not masking errors arising from nonconforming ranges. |
|
Thread Tools | |
Display Modes | |
|
|