Bug. MATCH and SUMIFS does not work correctly with text

Forum > FPSpreadsheet

(1/6) > >>

veb86:
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- b.Options := [boReadFormulas]; b.ReadFromFile('d:\test1\4.xlsx', sfOOXML); sh := b.GetFirstWorksheet; sh.CopyCell(1,5,2,5,b.GetFirstWorksheet); sh.CopyCell(2,5,3,5,b.GetFirstWorksheet); sh.CopyCell(3,5,4,5,b.GetFirstWorksheet); sh.CopyCell(1,8,2,8,b.GetFirstWorksheet); sh.CopyCell(2,8,3,8,b.GetFirstWorksheet); sh.CopyCell(3,8,4,8,b.GetFirstWorksheet); b.CalcFormulas; b.WriteToFile('d:\test1\444111111.xlsx', sfOOXML, true);

veb86:
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:
The updated ccr version yields the same results as when I copy the cells directly in Excel.

wp:

--- Quote from: veb86 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)

--- End quote ---
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:

--- Quote from: wp on July 12, 2024, 06:53:17 pm ---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.

--- End quote ---
Understood. Calculations corrected, everything worked

--- Quote from: wp 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.

--- End quote ---

MATCH MATCH is not working properly.
The formula does not correctly accept the 3rd argument.
Match_typeBehavior1 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

Navigation

[0] Message Index

[#] Next page