Recent

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

veb86

  • Jr. Member
  • **
  • Posts: 62
Bug. MATCH and SUMIFS does not work correctly with text
« on: July 11, 2024, 02:05:36 pm »
The search for matches in the text is performed incorrectly, resulting in errors.
I think the situation is approximately the same for MATCH and SUMIFS
You need to watch this.
my code:
Code: Pascal  [Select][+][-]
  1.     b.Options := [boReadFormulas];
  2.     b.ReadFromFile('d:\test1\4.xlsx', sfOOXML);
  3.     sh := b.GetFirstWorksheet;
  4.     sh.CopyCell(1,5,2,5,b.GetFirstWorksheet);
  5.     sh.CopyCell(2,5,3,5,b.GetFirstWorksheet);
  6.     sh.CopyCell(3,5,4,5,b.GetFirstWorksheet);
  7.     sh.CopyCell(1,8,2,8,b.GetFirstWorksheet);
  8.     sh.CopyCell(2,8,3,8,b.GetFirstWorksheet);
  9.     sh.CopyCell(3,8,4,8,b.GetFirstWorksheet);
  10.     b.CalcFormulas;
  11.     b.WriteToFile('d:\test1\444111111.xlsx', sfOOXML, true);
  12.  

veb86

  • Jr. Member
  • **
  • Posts: 62
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #1 on: July 11, 2024, 02:14:48 pm »
Now I noticed a problem in the operation of the column formula :'(
Try to calculate the page and look at the value of the cells (0,1)(0,2)(0,3)

Download this file, it takes into account MATCH and SUMIFS


wp

  • Hero Member
  • *****
  • Posts: 12459
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #2 on: July 12, 2024, 12:40:23 pm »
The updated ccr version yields the same results as when I copy the cells directly in Excel.

wp

  • Hero Member
  • *****
  • Posts: 12459
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #3 on: July 12, 2024, 06:53:17 pm »
Now I noticed a problem in the operation of the column formula :'(
Try to calculate the page and look at the value of the cells (0,1)(0,2)(0,3)
You mean the circular reference? This is because you have COL(A1) in cell A1. I know in Excel this works, but in fpspreadsheet it does not. Use COL(A2) instead.

veb86

  • Jr. Member
  • **
  • Posts: 62
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #4 on: July 15, 2024, 08:00:05 am »
You mean the circular reference? This is because you have COL(A1) in cell A1. I know in Excel this works, but in fpspreadsheet it does not. Use COL(A2) instead.
Understood. Calculations corrected, everything worked

The updated ccr version yields the same results as when I copy the cells directly in Excel.


MATCH
MATCH is not working properly.
The formula does not correctly accept the 3rd argument.
Match_typeBehavior
1 or omittedMATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
0MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
-1MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a

3rd argument is 0, exact FIRST match in range

Match now returns the following:
Cell(F2) = 1
Cell(F3) = 2
Cell(F4) = 3
Cell(F5) = 4

Match should be:
Cell(F2) = 1
Cell(F3) = 2
Cell(F4) = 1
Cell(F5) = 4


SUMIFS
SUMIFS in my example returns the value 0, although it should return the value:
Cell(I2) = 2
Cell(I3) = 1
Cell(I4) = 2
Cell(I5) = 1
I think that the search for the value is not happening correctly
« Last Edit: July 15, 2024, 08:08:34 am by veb86 »

wp

  • Hero Member
  • *****
  • Posts: 12459
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #5 on: July 15, 2024, 12:01:55 pm »
Unless you are talking of something else I cannot reproduce this. See attachment. File 41.xlsx is your initial file, file 41-wp was written by fpspreadsheet, file 41-excel was written by Excel 2016 after manually performing the copy operations mentioned in the project code. In both files 41-wp and 41-excel the result cells display the same content.
« Last Edit: July 15, 2024, 12:39:31 pm by wp »

veb86

  • Jr. Member
  • **
  • Posts: 62
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #6 on: July 15, 2024, 12:08:30 pm »
Unless you are talking of something else I cannot reproduce this. See attachment. File 41.xlsx is your initial file, file 41-wp was written by fpspreadsheet, file 41-excel was written by Excel 2016 after manually performing the copy operations mentioned in the project code. In both files 41-wp and 41-excel the result cells display the same content.

I don't see the attached file. You forgot to add it

wp

  • Hero Member
  • *****
  • Posts: 12459
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #7 on: July 15, 2024, 12:40:00 pm »
I don't see the attached file. You forgot to add it
Sorry, attached to previous post now.

veb86

  • Jr. Member
  • **
  • Posts: 62
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #8 on: July 15, 2024, 12:55:18 pm »
Unless you are talking of something else I cannot reproduce this. See attachment. File 41.xlsx is your initial file, file 41-wp was written by fpspreadsheet, file 41-excel was written by Excel 2016 after manually performing the copy operations mentioned in the project code. In both files 41-wp and 41-excel the result cells display the same content.

Excel 2019: file 41-wp
Watch a video.
When I click calculation, the value in cell F4 changes from 3 to 1. The correct value is 1
« Last Edit: July 15, 2024, 12:58:14 pm by veb86 »

wp

  • Hero Member
  • *****
  • Posts: 12459
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #9 on: July 15, 2024, 03:07:01 pm »
Try commit r9384

veb86

  • Jr. Member
  • **
  • Posts: 62
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #10 on: July 16, 2024, 07:25:27 am »
Try commit r9384
Everything is working now. I only checked the case when the 3rd argument is 0

veb86

  • Jr. Member
  • **
  • Posts: 62
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #11 on: July 16, 2024, 07:28:19 am »
SUMIFS BUG
SUMIFS now returns the following:
Cell(I2) = 0
Cell(I3) = 0
Cell(I4) = 0
Cell(I5) = 0

SUMIFS should be:
Cell(I2) = 2
Cell(I3) = 1
Cell(I4) = 2
Cell(I5) = 1

I think that the search for the value is not happening correctly

veb86

  • Jr. Member
  • **
  • Posts: 62
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #12 on: July 16, 2024, 08:17:20 am »
Please look at the bug with SUMIFS. I don't understand his reasons

wp

  • Hero Member
  • *****
  • Posts: 12459
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #13 on: July 17, 2024, 11:00:13 am »
SUMIFS BUG
SUMIFS now returns the following:
Cell(I2) = 0
Cell(I3) = 0
Cell(I4) = 0
Cell(I5) = 0

SUMIFS should be:
Cell(I2) = 2
Cell(I3) = 1
Cell(I4) = 2
Cell(I5) = 1

I think that the search for the value is not happening correctly
Cannot reproduce this. Here is an excerpt from the xlsx file written; in the <v> nodes you see the results calculated by fpspreadsheet for the formulas in the preceeding lines - it's 2-1-2-1:
Code: XML  [Select][+][-]
  1. ...
  2.       <c r="I2" s="0">
  3.         <f>SUMIFS($J$2:$J$5,$D$2:$D$5,D2)</f>
  4.         <v>2</v>
  5.       </c>
  6. ...
  7.       <c r="I3" s="0">
  8.         <f>SUMIFS($J$2:$J$5,$D$2:$D$5,D3)</f>
  9.         <v>1</v>
  10.       </c>  
  11. ...
  12.       <c r="I4" s="0">
  13.         <f>SUMIFS($J$2:$J$5,$D$2:$D$5,D4)</f>
  14.         <v>2</v>
  15.       </c>  
  16. ...
  17.       <c r="I5" s="0">
  18.         <f>SUMIFS($J$2:$J$5,$D$2:$D$5,D5)</f>
  19.         <v>1</v>
  20.       </c>
  21. ...

veb86

  • Jr. Member
  • **
  • Posts: 62
Re: Bug. MATCH and SUMIFS does not work correctly with text
« Reply #14 on: July 17, 2024, 11:14:57 am »
Cannot reproduce this.
Just rechecked. There is an error.
Resubmitting the test file

Code: Pascal  [Select][+][-]
  1.     b.Options := [boReadFormulas];
  2.     b.ReadFromFile('d:\test1\4.xlsx', sfOOXML);
  3.     sh := b.GetFirstWorksheet;        
  4.     sh.CopyCell(1,8,2,8,b.GetFirstWorksheet);
  5.     sh.CopyCell(2,8,3,8,b.GetFirstWorksheet);
  6.     sh.CopyCell(3,8,4,8,b.GetFirstWorksheet);
  7.     b.CalcFormulas;  
  8.     b.WriteToFile('d:\test1\444111111.xlsx', sfOOXML, true);
  9.  

Saved the following:
Code: Pascal  [Select][+][-]
  1. <c r="I2" s="0"><f>SUMIFS($J$2:$J$5,$D$2:$D$5,D2)</f><v>0</v></c>
  2. <c r="I3" s="0"><f>SUMIFS($J$2:$J$5,$D$2:$D$5,D3)</f><v>0</v></c>
  3. <c r="I4" s="0"><f>SUMIFS($J$2:$J$5,$D$2:$D$5,D4)</f><v>0</v></c>

 

TinyPortal © 2005-2018