I need help with a #DIVIDE BY ZERO issue

I wouldn't recommend sharing sheets in the public online Community. Let's try a few more troubleshooting steps here in this thread, and if we still can't sort this out, I would also recommend reaching out to Smartsheet Support as Jaykel said above.

In your original post you noted that your formula worked, originally. You were just looking to add an IFERROR for when there's a 0. To find out where this new error could be happening, try breaking down each statement.

Make sure each of these work separately:

TEST 1

=([Energy & GHG assessment Completed in cycle]@row/[Energy & GHG assessment Expected in cycle]@row ) * 0,2

TEST 2

If the formula above works, add the IFERROR:

=IFERROR((([Energy & GHG assessment Completed in cycle]@row/[Energy & GHG assessment Expected in cycle]@row ) * 0,2); 0)

TEST 3

If that works, try the second half of your formula, separately.

=([E&GHG Completed Tasks (end reporting period)]@row/[E&GHG Expected tasks (end reporting period)]@row) * 0,8)

TEST 4

Then add the IFERROR:

=IFERROR((([E&GHG Completed Tasks (end reporting period)]@row/[E&GHG Expected tasks (end reporting period)]@row) * 0,8); 0)

TEST 5

If Test 2 and Test 4 work, then simply add them together:

=IFERROR((([Energy & GHG assessment Completed in cycle]@row/[Energy & GHG assessment Expected in cycle]@row ) * 0,2) ;0) + IFERROR((([E&GHG Completed Tasks (end reporting period)]@row/[E&GHG Expected tasks (end reporting period)]@row) * 0,8); 0)

FINAL TEST

If that works, then add in the final IFERROR around the entire formula:

=IFERROR(IFERROR((([Energy & GHG assessment Completed in cycle]@row/[Energy & GHG assessment Expected in cycle]@row ) * 0,2); 0) + IFERROR((([E&GHG Completed Tasks (end reporting period)]@row/[E&GHG Expected tasks (end reporting period)]@row) * 0,8); 0); 0)

At what point do you receive the error? What error is it?

All comments

Jaykel Torres

Hey @Kris Miller,

In order to return a value should the formula result in an error, the IFERROR Function can be incorporated. The IFERROR function will return a specified value should the formula return an error. I believe your formula should look similar to the format below:

=IFERROR(IFERROR(((([Energy & GHG assessment Completed in cycle]81 / [Energy & GHG assessment Expected in cycle]81) * 0,2)), 0) + IFERROR(((([E&GHG Completed Tasks (end reporting period)]81 / [E&GHG Expected tasks (end reporting period)]81) * 0,8)), 0), 0)

I hope this helps!

Jaykel

Hi Jaykel,

Thanks so much for your help. It think that is the right formula but for some reason when I copy and paste it or even retype and rework it it comes up as #UNPARSEABLE.

Perhaps I can share the sheet with you and you can see if you have better luck?

So Sorry 0 but I am just not good enough to fix this on my own!

Jaykel Torres

Hey @Kris Miller,

I did not notice this before but it appears the initial formula had some syntax errors where .8 & .2 was written with a comma (,8 & ,2) instead of periods. After changing these into decimal values, the formula will return the correct results. Always refer to our Help Article: Formula Error Messages for details and instructions should a formula return an error.

If you are still receiving formula errors, I do recommend reaching out to Smartsheet Technical Support by creating a case here: https://help.smartsheet.com/contact/smartsheetapp

I hope this helps!

Jaykel

Hi Jaykel,

I changed the comma's to periods and it is still not working.

I will try reaching out to Technical Support.

Thanks,

Would you be able to post a screen capture of how this formula is working in your sheet, and what it looks like with @Jaykel T. 's formula open? (Cell double clicked to see what it looks like before the error). Please block any sensitive data in the screen capture.

Thanks!

Genevieve

Hi Genevieve,

Thank you - yes. Here you go.

It looks like there are just a few too many parenthesis ( in your formula.

If this is your original:

=((([Energy & GHG assessment Completed in cycle]@row / [Energy & GHG assessment Expected in cycle]@row) * 0,2)) + ((([E&GHG Completed Tasks (end reporting period)]@row / [E&GHG Expected tasks (end reporting period)]@row) * 0,8))

You actually only need these parenthesis in there:

=(([Energy & GHG assessment Completed in cycle]@row / [Energy & GHG assessment Expected in cycle]@row) * 0,2) + (([E&GHG Completed Tasks (end reporting period)]@row / [E&GHG Expected tasks (end reporting period)]@row) * 0,8)

Summarized:

=((Completed / Expected) * 0,2) + ((Completed / Expected) * 0,8)

The extra parenthesis can confuse the IFERROR functions wrapped around.

Try this structure instead:

=IFERROR(IFERROR((([Energy & GHG assessment Completed in cycle]@row / [Energy & GHG assessment Expected in cycle]@row) * 0,2), 0) + IFERROR((([E&GHG Completed Tasks (end reporting period)]@row / [E&GHG Expected tasks (end reporting period)]@row) * 0,8), 0), 0)

In my regional settings, as in Jaykel's, we have periods so we would need to use 0.2 in order for the formula to work, however based on your screen capture it looks like your language settings are set to read commas as periods, so this should work for you.

Let us know if you're still getting an error!

Cheers,

Genevieve

Hi Genevieve,

Still no luck. I copied yours exactly only replacing the @row with the relevant row number.

Is there anything else I could try?

Kris

Pasted here:

=IFERROR(IFERROR((([Energy & GHG assessment Completed in cycle]81 / [Energy & GHG assessment Expected in cycle]81) * 0,2), 0) + IFERROR((([E&GHG Completed Tasks (end reporting period)]81 / [E&GHG Expected tasks (end reporting period)]81) * 0,8), 0), 0)

I would recommend keeping the @row function instead of the row number. This will allow you to set it as a Column Formula, and it will help your sheet calculate faster as it won't need to review the entire sheet to find the correct row (it will calculate based on its own row).

Are you still receiving the same type of error (unparseable)?

If so, instead of copying/pasting my formula, can you try building it in Smartsheet itself so you can click on the cell in the correct column and auto-populate the column name? This error can occur if one character is incorrect compared to your column names.

I also noted that since my personal settings are different, I use commas in my formulas. It looks like you use semi-colons instead. Try this structure with commas instead of periods and semi-colons instead of commas:

=IFERROR(IFERROR((([Energy & GHG assessment Completed in cycle]@row / [Energy & GHG assessment Expected in cycle]@row) * 0,2); 0) + IFERROR((([E&GHG Completed Tasks (end reporting period)]@row / [E&GHG Expected tasks (end reporting period)]@row) * 0,8); 0); 0)

Hi Genevieve,

Ok I have built it as directed and still getting @UNPARSEABLE as a result.

I get the feeling that I won't be able to solve this one...

I have tried both of these: with comma's and with periods. Both with semi-colons.

=IFERROR(IFERROR((((([Energy & GHG assessment Completed in cycle]@row/[Energy & GHG assessment Expected in cycle]@row*0,2);0)+IFERROR((([E&GHG Completed Tasks (end reporting period)]@row/[E&GHG Expected tasks (end reporting period)]@row)*0,8);0);0)

=IFERROR(IFERROR((((([Energy & GHG assessment Completed in cycle]@row/[Energy & GHG assessment Expected in cycle]@row*0.2);0)+IFERROR((([E&GHG Completed Tasks (end reporting period)]@row/[E&GHG Expected tasks (end reporting period)]@row)*0.8);0);0)

It's either with 0.2 and commas, or 0,2 and with semi-colons.

Note, in the formulas you have above, I still see the extra parentheses. You'll need to remove these as well. 🙂

Try these two combinations to see what works with your personal settings:

=IFERROR(IFERROR((([Energy & GHG assessment Completed in cycle]@row/[Energy & GHG assessment Expected in cycle]@row ) * 0,2);0)+IFERROR((([E&GHG Completed Tasks (end reporting period)]@row/[E&GHG Expected tasks (end reporting period)]@row)*0,8);0);0)

=IFERROR(IFERROR((([Energy & GHG assessment Completed in cycle]@row/[Energy & GHG assessment Expected in cycle]@row ) * 0.2), 0) + IFERROR((([E&GHG Completed Tasks (end reporting period)]@row/[E&GHG Expected tasks (end reporting period)]@row) * 0.8), 0), 0)

Did either of these work?

Hi Genevieve,

I tried both yes, no luck. Are you able to look at it if I give you access to the sheet?

Make sure each of these work separately:

TEST 1

=([Energy & GHG assessment Completed in cycle]@row/[Energy & GHG assessment Expected in cycle]@row ) * 0,2

TEST 2

If the formula above works, add the IFERROR:

=IFERROR((([Energy & GHG assessment Completed in cycle]@row/[Energy & GHG assessment Expected in cycle]@row ) * 0,2); 0)

TEST 3

If that works, try the second half of your formula, separately.

=([E&GHG Completed Tasks (end reporting period)]@row/[E&GHG Expected tasks (end reporting period)]@row) * 0,8)

TEST 4

Then add the IFERROR:

=IFERROR((([E&GHG Completed Tasks (end reporting period)]@row/[E&GHG Expected tasks (end reporting period)]@row) * 0,8); 0)

TEST 5

If Test 2 and Test 4 work, then simply add them together:

FINAL TEST

If that works, then add in the final IFERROR around the entire formula:

At what point do you receive the error? What error is it?

Genevieve!!!

You are a total rockstar!

This worked PERFECTLY and the whole thing is producing the correct outcome.

THANK YOU!😁