Form to e-mail returning #DIV/0!

[Anonymous]

Hello

I'm new to flows and struggling with the one I have built

i've built a flow to trigger when a new form response, it delay's a minute to allow sharepoint to write the form information to the excel, then pulls the row of the triggered response and e-mail's the person who submitted the form.

The flow works however I have added some additional columns to the table to calculate the Mets and Non Mets of the results and calculate the %, however the flow returns #DIV/0!. if I rerun the flow it returns the number as it should. I have tried to put a wait until then added an additional column with a formula to show a yes or a no for if the outcome is greater than zero which works in excel, but still  even though the flow appears to see the yes, continues but yet still shows #DIV/0!. Every time I rerun the flow the data comes through correct, but on the triggered flow I get the error. where am I going wrong? any help would greatly be appreciated as I feel I am getting balder by the hour 😛 

My Flow looks like this

The additions columns I have added to the table are these and the calculations all work, the check part I added afterwards to try to get the flow to see yes before it continues

(BW)met - =COUNTIF(I167:BV167, "Met")

(BX)not met - =COUNTIF(I167:BV167, "Not Met")

(BY)max - =SUM(BW167+BX167)

(BZ)total - =(BW167/BY167)*100

(CA)result - =ROUND(BZ167, 0)

(CA)check - =IF(BZ167>0,"yes","no")

[Anonymous]

I have tried to rebuild the flow from scratch, removing the "for every" function, and it's still doing the same. When I submit the form I get the e-mail with #DIV/0! results

if I re run the flow I get a number back, it's as if the flow isn't reading the row i've grabbed? and actually using a cached version? rather than actually grabbing the row (after the additional columns not part of the form perform the calculations)

[v-duann-msft]

Hi @Anonymous 

Thank you for posting.

Based on your description, you are able to run flow successfully when resubmit the flow. But for the first time, you always got ‘#DIV/0!’ error. If I misunderstood, please kindly let me know.

If that’s the case, there is nothing to do with flow part as you are able to get correct result when you rerun the flow.

The #DIV/0 error arises for the same reason as the #NUM error, namely:  the algorithm reaches some internal limits before finding an adequate solution.  The algorithm is simply stumbling over the #DIV/0 error internally before it has a chance to report the #NUM error.

However, other factors might influence the Excel error.

1."Too many" cash flows, especially for Excel IRR.

2.Too many sign changes.  The more sign changes, the more possible solutions there might be.  The algorithm does best when there is only one or two sign changes, for example a sequence of negative cash flows followed by a sequence of positive cash flows, or vice versa.

3.You neglected to have at least one negative and one positive cash flow.  That should result in a #NUM error, not a #DIV/0 error.

4.You have an "absurd" sequence of cash flows, like a mix of very large and very tiny numbers.

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other-mso_2007/irr-formula-div0-error-message/f2e57334-8760-e011-8dfc-68b599b31bf5

https://exceljet.net/formula/how-to-fix-the-div0-error

In your scenario, I recommend you go to office support. They know much more about excel mechanism.

Contact Us - Microsoft Support

Hope the content above may help you.

Thanks

Anna

[Anonymous]

Thanks for your reply Anna

I would say it was an excel problem if i was seeing the #DIV/0! in excel itself but i'm not. the basic formula's which i've used (as pasted above) none return an error. The error is only seen when I ask flows to pull a row from a form. When I first started it would not work without having a delay in after trigger on new form, because sharepoint hadn't updated the table fast enough for flows to see it, it could not find the row with the id it was looking for. With the 1 minute delay flows see's the correct row but is returning #DIV/0! for the result of my output formula. If i rerun the flow, the information in the table is already there, so it pulls it correctly and gives the number rather than the #DIV/0! error. I've tried setting the delay to 5 mins and 10 mins, but it makes no difference. I did read in another post that someone experienced something similar and the only workaround was to read the row and put the row into another spreadsheet (not linked to the form) and it would read the information fine.

[Anonymous]

I have now copied all the content from the the get row to another table, and put the formula's on the second table (table not linked to the form) and it still does the same (I edited the formula to show a blank iferror)

[Anonymous]

When sharepoint adds the output from forms to the table, the formula's do as they are meant too. Even with a delay when forms "get a row" is used on that table it pulls the result of the formula as if all the other data is missing as if it is grabbing the row before sharepoint adds it, which is impossible as its looking for the ID which is triggered from the response. The flow works as it should every time after that on that specific run, not a new run (likely because the data already exists, and it is simply looking for that row ID) every single time the flow is ran from a new response the formula cells return errors. I have been reading about checking in and out?

its not as if I can perform the formula equations to calculate the % in flows itself as the row number changes every time (use an expression) nor can I find away to add the formula's to the row at the same time the form adds the rest of the data to the table (I may be just trying to over complicate things)

If my table was sat with errors where my formula's are I'd agree with it being an excel issue, the formula will return a DIV/0! if the rest of the fields are empty because it is trying to divide by 0, but as the data is present when the flow reads the table that rules that out.

[Anonymous · Accepted solution]

I have set the delay to 10 minutes which allows "sharepoint app" to finish and stop editing my table, and the flow ran successfully. It is showing to be a limitation of flows