HOW TO REMOVE #DIVIDE BY ZERO AND MAKE CELL BLANK

Mariniel Bautista

I am trying to average a customer feedback section of my sheet.  But if the cells where the data to be averaged will come from, the result section shows #Divide by Zero.  I use the formula =AVG([column1:column2]).  What's the formula I need to use for this?  I saw in the other community discussion that I can use IFERROR but I don't know how to place it in the formula.

Please refer to below screenshot.

Thanks in advance for the answer.

Andrée Starå

Hi Mariniel,

Try something like this.

=IFERROR(AVG(ColumnA1:ColumnB1); "")

The same version but with the below changes for your and others convenience.

=IFERROR(AVG(ColumnA1:ColumnB1), "")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Mariniel Bautista

It worked!!! Thanks so much Andree!

Mariniel Bautista

Hi again Andree.  I got another question.  Will IFERROR applies for the formula below (formula to measure handling time)?  Instead of #DIVIDE BY ZERO, #INVALID DATA TYPE is the one that appears if source cells are blank.  I want it to appear blank if source cells are blank too.  Hope you can help me with this. Thanks.

 

=INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) + ":" + IF(((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) - INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60) < 10, "0") + ((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) - INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60)

Andrée Starå

Excellent!

Happy to help!

Andrée Starå

Try this:

I added the IFERROR in the beginning and the "" in the end to make it blank if there are any errors. I also change the row reference to @row so you don't have to think about the row numbers.

=IFERROR(INT((((IF(LEFT(EndTime@row, FIND(":", EndTime@row) - 1) = "12", IF(OR(FIND("a", EndTime@row) > 0, FIND("p", EndTime@row) > 0), 0, 12), VALUE(LEFT(EndTime@row, FIND(":", EndTime@row) - 1))) + IF(FIND("p", EndTime@row) > 0, 12)) * 60 + VALUE(MID(EndTime@row, FIND(":", EndTime@row) + 1, 2))) - ((IF(LEFT(StartTime@row, FIND(":", StartTime@row) - 1) = "12", IF(OR(FIND("a", StartTime@row) > 0, FIND("p", StartTime@row) > 0), 0, 12), VALUE(LEFT(StartTime@row, FIND(":", StartTime@row) - 1))) + IF(FIND("p", StartTime@row) > 0, 12)) * 60 + VALUE(MID(StartTime@row, FIND(":", StartTime@row) + 1, 2)))) / 60) + ":" + IF(((((IF(LEFT(EndTime@row, FIND(":", EndTime@row) - 1) = "12", IF(OR(FIND("a", EndTime@row) > 0, FIND("p", EndTime@row) > 0), 0, 12), VALUE(LEFT(EndTime@row, FIND(":", EndTime@row) - 1))) + IF(FIND("p", EndTime@row) > 0, 12)) * 60 + VALUE(MID(EndTime@row, FIND(":", EndTime@row) + 1, 2))) - ((IF(LEFT(StartTime@row, FIND(":", StartTime@row) - 1) = "12", IF(OR(FIND("a", StartTime@row) > 0, FIND("p", StartTime@row) > 0), 0, 12), VALUE(LEFT(StartTime@row, FIND(":", StartTime@row) - 1))) + IF(FIND("p", StartTime@row) > 0, 12)) * 60 + VALUE(MID(StartTime@row, FIND(":", StartTime@row) + 1, 2)))) - INT((((IF(LEFT(EndTime@row, FIND(":", EndTime@row) - 1) = "12", IF(OR(FIND("a", EndTime@row) > 0, FIND("p", EndTime@row) > 0), 0, 12), VALUE(LEFT(EndTime@row, FIND(":", EndTime@row) - 1))) + IF(FIND("p", EndTime@row) > 0, 12)) * 60 + VALUE(MID(EndTime@row, FIND(":", EndTime@row) + 1, 2))) - ((IF(LEFT(StartTime@row, FIND(":", StartTime@row) - 1) = "12", IF(OR(FIND("a", StartTime@row) > 0, FIND("p", StartTime@row) > 0), 0, 12), VALUE(LEFT(StartTime@row, FIND(":", StartTime@row) - 1))) + IF(FIND("p", StartTime@row) > 0, 12)) * 60 + VALUE(MID(StartTime@row, FIND(":", StartTime@row) + 1, 2)))) / 60) * 60) < 10, "0") + ((((IF(LEFT(EndTime@row, FIND(":", EndTime@row) - 1) = "12", IF(OR(FIND("a", EndTime@row) > 0, FIND("p", EndTime@row) > 0), 0, 12), VALUE(LEFT(EndTime@row, FIND(":", EndTime@row) - 1))) + IF(FIND("p", EndTime@row) > 0, 12)) * 60 + VALUE(MID(EndTime@row, FIND(":", EndTime@row) + 1, 2))) - ((IF(LEFT(StartTime@row, FIND(":", StartTime@row) - 1) = "12", IF(OR(FIND("a", StartTime@row) > 0, FIND("p", StartTime@row) > 0), 0, 12), VALUE(LEFT(StartTime@row, FIND(":", StartTime@row) - 1))) + IF(FIND("p", StartTime@row) > 0, 12)) * 60 + VALUE(MID(StartTime@row, FIND(":", StartTime@row) + 1, 2)))) - INT((((IF(LEFT(EndTime@row, FIND(":", EndTime@row) - 1) = "12", IF(OR(FIND("a", EndTime@row) > 0, FIND("p", EndTime@row) > 0), 0, 12), VALUE(LEFT(EndTime@row, FIND(":", EndTime@row) - 1))) + IF(FIND("p", EndTime@row) > 0, 12)) * 60 + VALUE(MID(EndTime@row, FIND(":", EndTime@row) + 1, 2))) - ((IF(LEFT(StartTime@row, FIND(":", StartTime@row) - 1) = "12", IF(OR(FIND("a", StartTime@row) > 0, FIND("p", StartTime@row) > 0), 0, 12), VALUE(LEFT(StartTime@row, FIND(":", StartTime@row) - 1))) + IF(FIND("p", StartTime@row) > 0, 12)) * 60 + VALUE(MID(StartTime@row, FIND(":", StartTime@row) + 1, 2)))) / 60) * 60), "")

Did it work?