Hi everyone,
I am trying to set a formula in Excel using this plugin to calculate the total based on conditions that meet the desired criteria. I am using the following formula to calculate the total:
=SUMIFS(D1:D4, A1:A4, E1, B1:B4, F1, C1:C4, G1)
When run in Excel, the formula works perfectly and correctly. However, when implemented into the program, the formula works but the calculation result is incorrect. It only checks the first and last conditions, and ignores the other conditions, which causes the calculation result to be incorrect. Below are the calculation results from the download by the program and from Excel.
I have already tried using both actions to set the formula, but the result is still the same.
Do you have any suggestions or ideas related to this issue? I have also attached the .oml file to help understand the issue.
Thank you.
Hi,
you can refer the below forum discussion. Hope it helps.
https://www.outsystems.com/forums/discussion/100361/advanced-excel-advanced-excel-calculate-worksheet-is-not-working-as-expected/
Thanks,
Sriyamini.J
Hi Sriyamini,
Thank you for your help, I appreciate it. I’ve read it before, and I think this issue is slightly different from the one you mentioned.
Hi @Stefanus Renaldo Wahyu Krismadita,
Please refer to this discussion.
https://www.outsystems.com/forums/discussion/71243/advanced-excel-formula-is-not-calculated-with-cell-write/
Hope this resolve your issue
Thanks
Hi Aditi,
Hi Stefano,
The reason it wasn't working as expected is that the formula was written incorrectly. It should be like this: "SUMIFS(D1:D4, A1:A4, E1, B1:B4, F1, C1:C4, G1)" . It doesn't need the equals sign.
I've updated your .oml file so you can take a look.
Hi Beatriz,
Thanks for your reply. I’ve tried your suggestion, and the result showing "#VALUE1" has worked and the formula is running fine. However, I apologize, it seems I didn’t provide enough details regarding the issue, which caused a misunderstanding.
So, the case I’m referring to is more about the calculation results from the formula I mentioned earlier. When the formula has three or more conditions, it only reads the first and last conditions (Column A and Column C), while the other conditions are ignored, causing the calculation result to be incorrect, as shown in the image below.
The result from the formula should actually look like this image below.
I hope this explanation better clarifies the issue I meant. Please let me know if you have any ideas or solutions for this issue. Thanks in advance.
Hi Stefanus,
I just downloaded the Excel file and this is the resultIs this not correct?
Yes, that's correct, but from the image below, when the Excel file is in Protected View after downloading, the result is still not accurate.
After clicking "Enable Editing," the result becomes correct as you mentioned.
Maybe you could try checking the .oml file that I previously attached. When running this action, the result reads as 16,000 instead of 14,000 as it should.
After some investigation this is what I found.
The reason you see a different value in protected view is because is because Excel disables certain features in this mode, including automatic formula calculation, to prevent potentially harmful content from executing. As a result, formulas may not recalculate when the file is opened in Protected View. Once you enable editing and switch to Normal View, Excel recalculates the formulas, which can lead to different results appearing.
I found this post in the Microsoft forum that mentions this issue.
Ah, I see, thank you for the solution to that issue. Now, regarding the reading of the calculation results in the .oml program I attached earlier. I created an Excel file, then filled in the data and formulas. After that, I performed the calculations and read the results. The result that was read was 16,000, as shown in the debugging image I attached earlier. However, the correct calculation result should be 14,000. Is this also due to the same issue, or is there something else?
Yes I believe this is related to the same issue. Since 1600 is the last calculated value, that's what appears in the debugger. Once you open the edit mode in excel the formulas are recalculated again.
Do you have any ideas/solutions to ensure that the logic calculates correctly when it runs?
Unfortunately, I don’t have a solution at the moment.
It's okay. Thank you for your help.
Regarding this issue, is there a possibility for an analysis and review to be conducted in the near future?
Thank you in advance.
There seems to be a bug in the base library related to this issue: https://github.com/JanKallman/EPPlus/issues/421
"This code from EPPlus/FormulaParsing/Excel/Functions/Math/SumIfs.cs:
IEnumerable matchIndexes = GetMatchIndexes(argRanges[0], criterias[0]);var enumerable = matchIndexes as IList ?? matchIndexes.ToList();for (var ix = 1; ix < argRanges.Count && enumerable.Any(); ix++){var indexes = GetMatchIndexes(argRanges[ix], criterias[ix]);matchIndexes = enumerable.Intersect(indexes);}var result = matchIndexes.Sum(index => sumRange[index]);
Looks like matchIndexes = enumerable.Intersect(indexes); will match first criteria with ix-criteria. This means, that when you have 4, for example, criteria ranges, only first and last will be applied. 2-nd and 3-rd criteria ranges will be ignored (this problem I have at the moment in my project)."
Hi Hanno,Thank you for your help and for sharing the information.