Recent

Author Topic: Bug in the formula MATCH  (Read 676 times)

veb86

  • Jr. Member
  • **
  • Posts: 85
Bug in the formula MATCH
« on: January 17, 2025, 07:41:04 am »
See example.
The problem is that cell A1 was deleted, never used. In this case, calculation is not possible. This causes a calculation error.
Correctly, if a cell is missing, then the MATCH formula should return: #N/A

wp

  • Hero Member
  • *****
  • Posts: 12596
Re: Bug in the formula MATCH
« Reply #1 on: January 17, 2025, 06:38:31 pm »
I don't understand what "delete" means here: You "delete" the cell content, so that A1 is empty? Or you "delete" the cell itself (or the column) so that the following cells move to the left and the formula must adjusted?

Anyway: There is a suspicious code in function fpsMATCH (unit fpsfunc) because the case cell=nil is not handled. Add the following highlighted lines, and report back:
Code: Pascal  [Select][+][-]
  1.   case Args[0].ResultType of
  2.     [...]
  3.     rtCell:
  4.       begin
  5.         cell := ArgToCell(Args[0]);
  6.         if cell = nil then
  7.         begin
  8.           Result := ErrorResult(errWrongType);
  9.           exit;
  10.         end;
  11.         case cell^.ContentType of
  12.           cctUTF8String: searchString := cell^.UTF8StringValue;
  13.           cctNumber: numSearchValue := cell^.NumberValue;
  14.           cctDateTime: numSearchValue := cell^.DateTimeValue;
  15.           cctBool: numSearchValue := ord(cell^.BoolValue);
  16.           cctEmpty: begin Result := ErrorResult(errWrongType); exit; end;
  17.           cctError: begin Result := ErrorResult(errWrongType); exit; end;
  18.         end;
  19.       end;  

veb86

  • Jr. Member
  • **
  • Posts: 85
Re: Bug in the formula MATCH
« Reply #2 on: January 18, 2025, 02:21:09 pm »
I don't understand what "delete" means here: You "delete" the cell content, so that A1 is empty? Or you "delete" the cell itself (or the column) so that the following cells move to the left and the formula must adjusted?

I did the following:
1. Selected the cell whose value the MATH function will search for
2. Pressed the button DELETE

See example.
The problem is that cell A1 was deleted, never used. In this case, calculation is not possible. This causes a calculation error.
Correctly, if a cell is missing, then the MATCH formula should return: #N/A

It got better, but not the same
Error code returned: #VALUE
And it should return #N/A

#VALUE is Excel's way of saying, "There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing."

Returns the error value #N/A. #N/A is the error value that means "no value is available." Use NA to mark empty cells. By entering #N/A in cells where you are missing information, you can avoid the problem of unintentionally including empty cells in your calculations. (When a formula refers to a cell containing #N/A, the formula returns the #N/A error value.)

 

TinyPortal © 2005-2018