How to remove error message from result cell when no data is present

[buck08]

I have the sheet protected and I'm finding that the analysts do not always use 4 bottles per sample. How do I keep from having the error sign show up when, for example when line 5 in attached is blank. Thanks!!

[Glenn Kennedy]

Replace the formula in I2, copied down, with:

=IFERROR(IF(AND(E2>=1,F2>=2),ROUND((H2*300)/(C2),0),IF(F2<2,"< "&ROUND((H2*300)/(C2),0),IF(E2<1,"> "&ROUND((H2*300)/(C2),0)))),"")

Whay is the formula in I6 referring to column J???

[davesexcel]

Can you use iferror() in the other cells?

Please Login or Register  to view this content.

[buck08]

Column J was set up as a helper column. I've been in the excel forum with a few topics trying to set this sheet up. I have rules on the sheet (in red) and if those rules aren't followed there's is either a < or > attached to the result and are considered estimated values. For each sample if one or more results follow the rules, those results are averaged while results that don't follow the rules are ignored. I have attached another updated sheet with different scenarios. I've added the if error but it removes the sign in my average cell.

[JeteMc]

If I understand then I would offer the following. If there isn't a Bottle # then there can't be any ml's used, initial DO, Final DO etc. so tie the calculation in column K to the existence of a bottle # in column B using the formula: =IF(B3="","",ROUND((I3*300)/(C3),0))
Next to preserve the convention of <, >, or no preceding sign, modify the formula in the 'Average' cells to read:

Please Login or Register  to view this content.

Let us know if you have any questions.