I need help with a #DIVIDE BY ZERO issue

Kris Miller

Hi team,

I have a formula where 4 columns figures are weighted in a 20/80 split:

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

If one of the scores results in 0 then the entire outcome results in an error message: #DIVIDE BY ZERO

I would like it to know that if the outcome is 0 that it should then accordingly allocate that towards the weighting. IE: if the 80% portion resultsin 0 then the answer should be 20% and vice versa. OR if both as 0 then it should result in 0% (not an error message).

I am not savvy enough to know how to do this though - Please help!

Kris

Genevieve P.

Hi @Kris Miller

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?

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

Kris Miller

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

Kris Miller

Hi Jaykel,

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

I will try reaching out to Technical Support.

Thanks,

Genevieve P.

Hi @Kris Miller

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

Kris Miller

Hi Genevieve,

Thank you - yes. Here you go.

Genevieve P.

Hi @Kris Miller

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

Kris Miller

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)

Genevieve P.

Hi @Kris Miller

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)

Kris Miller

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)

Genevieve P.

Hi @Kris Miller

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)

OR

=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?

Kris Miller

Hi Genevieve,

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

Genevieve P.

Hi @Kris Miller

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?

Kris Miller

Genevieve!!!

You are a total rockstar!

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

THANK YOU!😁

Genevieve P.

@Kris Miller

I'm so glad that works for you! 😊