Recent

Author Topic: Bug. MATCH and SUMIFS does not work correctly with text  (Read 3767 times)

wp

  • Hero Member
  • *****
  • Posts: 12464
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #15 on: July 17, 2024, 11:42:17 am »
Still cannot reproduce. Isn't this file the same? What is different?

veb86

  • Jr. Member
  • **
  • Posts: 62
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #16 on: July 17, 2024, 12:20:14 pm »
The same file. Check, did you commit everything?
I don't understand why the bug is not reproducible.

wp

  • Hero Member
  • *****
  • Posts: 12464
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #17 on: July 17, 2024, 12:48:31 pm »
There is one uncommitted experimental change in my local repo related to circular reference formulas. I think it has no effect on this issue, but I committed it now for you.
« Last Edit: July 17, 2024, 10:07:11 pm by wp »

veb86

  • Jr. Member
  • **
  • Posts: 62
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #18 on: July 17, 2024, 01:05:50 pm »
There is one uncommitted experimental change in my local repo related to recursive formulas. I think it has no effect on this issue, but I committed it now for you.

Now it has stopped compiling, now I'll try to update the compiler. I updated it a year ago

veb86

  • Jr. Member
  • **
  • Posts: 62
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #19 on: July 17, 2024, 02:12:20 pm »
Compilation completed successfully. But the SUMIFS formula works with an error((((

Please look at the project in the archive and the picture. I don't understand how this can be


wp

  • Hero Member
  • *****
  • Posts: 12464
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #20 on: July 17, 2024, 05:51:09 pm »
But the SUMIFS formula works with an error((((
Please always specify what the error is. It would save me a lot of time if I knew what to look for.

Maybe, in this case you refer to the four COPY.Values which have the same cell value (2) - this is because you have the workbook.CalcFormulas afterwards, i.e. the cell values simply have not yet been calculated.

Or do you refer to the four CALC.Values which you see as zero - on my system they are listed correctly like in Excel as 2-1-2-1. Strange...

What is your operating system? Your Lazarus/FPC version? IDE is 32bit or 64bit?

veb86

  • Jr. Member
  • **
  • Posts: 62
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #21 on: July 17, 2024, 07:09:55 pm »

Or do you refer to the four CALC.Values which you see as zero - on my system they are listed correctly like in Excel as 2-1-2-1. Strange...

What is your operating system? Your Lazarus/FPC version? IDE is 32bit or 64bit?

I show that the copying is all correct.  Error after calculation.  I get 0 in all cells, but should get 2-1-2-1.  My operating system is Windows 10. Lazarus is a trunk (v3.99).  FPC - trunk.  Today I updated to the latest version.  IDE - 64 bit

wp

  • Hero Member
  • *****
  • Posts: 12464
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #22 on: July 17, 2024, 07:49:09 pm »
My operating system is Windows 10. Lazarus is a trunk (v3.99).  FPC - trunk.  Today I updated to the latest version.  IDE - 64 bit
Mine: Win 11, Laz/main (3.99), FPC 3.22, 32-bit IDE, but during these tests I also switched to the 64-bit IDE with the same constellation, to Laz/main + FPC/main (32-bit again), and to Linux Mint with Laz/main/FPC 3.2.2 64bit IDE - same observation: no 0-0-0-0, but 2-1-2-1. Very strange...

veb86

  • Jr. Member
  • **
  • Posts: 62
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #23 on: July 17, 2024, 09:43:26 pm »
I asked my programming mentor to run an example.  It has the same values ​​after calculation 0-0-0-0.
I appeal to the community. 
Maybe someone who uses this library can take a look at it.  What values ​​were obtained when performing the calculation?

wp

  • Hero Member
  • *****
  • Posts: 12464
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #24 on: July 17, 2024, 11:18:28 pm »
Simplify the file: Replace the "copy" cells by the formulas directly, in order to exclude bugs in the copy instructions. Replace the calculations of column D by their result directly ("A-B", etc). In the end, there should be a file with the SUMIFS formulas only.

If that still produces the 0-0-0-0 result, check function CompareStringWithWildcards in unit fpsfunc: the last instruction, before "end" must be "Result := true" - otherwise you don't have the current version.

The final option is to use the debugger to trace the instructions in TsFuncComparer.Execute (unit fpsfunc): Essentially here you have three loops, the outer one ("for r") runs over the rows of the range, the next one ("for c") runs over the columns of the range (but we have only one column), and the inner loop ("while matches...") runs over the criteria in the formula arguments. The variable "matches" is true initially, and with every cycle through the "while" loop the current argument set is compared to the test value by "CompareCell()". Once a mismatch is found "matches" becomes false and this cell does not contribute to the sum. But when all criteria are met "matches" still is true, and the current cell value of the r/c loop is added to the sum.

When SUMIFS results in 0, TsFuncComparer either is not entered at all or left very early, or the loops are not executed, or CompareCell results in false in every comparison. In the latter case, step into CompareCell and check the intermediate variables.

veb86

  • Jr. Member
  • **
  • Posts: 62
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #25 on: July 18, 2024, 07:41:38 am »
I updated the library today and everything worked!
I didn't change anything!
Thank you!

veb86

  • Jr. Member
  • **
  • Posts: 62
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #26 on: July 22, 2024, 07:43:55 am »
Hello. Please tell me what was the reason. Why was my calculation wrong, but yours was correct?

 

TinyPortal © 2005-2018