HOW TO REMOVE #DIVIDE BY ZERO AND MAKE CELL BLANK

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.

AVG problem.PNG

Find more posts tagged with

Formulas

Real Estate

Services

Project Management

Comments

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?

Sign in to join the conversation!

Quick Links

Trending discussions

seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…