Recent

Author Topic: Registering formula in FPSpreadsheet  (Read 1744 times)

SeregaKR

  • New member
  • *
  • Posts: 35
Registering formula in FPSpreadsheet
« on: December 22, 2018, 06:25:33 pm »
Hello.
I'm trying to add MATCH function to fpspreadsheet. I read the instruction in the wiki, but I have one question.

MATCH in Excel looks like this: MATCH( <value that you want to find>, <cell range>, <flag>).
So I have to write something like this:
Code: Pascal  [Select]
  1. RegisterFunction('MATCH', 'I', 'F'<---, 64,   @fpsMATCH);  
  2.  
How can I pass the information that second parameter is a cell range?

wp

  • Hero Member
  • *****
  • Posts: 5652
Re: Registering formula in FPSpreadsheet
« Reply #1 on: December 22, 2018, 07:22:01 pm »
Did you try 'F+' or 'R'?

You should also have a look at the registration of other statistical functions in fpsfunc, RegisterStdBuiltins, e.g.
Code: Pascal  [Select]
  1.     AddFunction(cat, 'COUNTBLANK','I', 'R',    INT_EXCEL_SHEET_FUNC_COUNTBLANK, @fpsCOUNTBLANK);
  2.     AddFunction(cat, 'MAX',       'F', 'F+',   INT_EXCEL_SHEET_FUNC_MAX,        @fpsMAX);

I notice that this part is not very well-documented. I should spend some time on it.

There's one thing I should note here: the formula registration of fpspreadsheet does not work for "array formulas", which are not supported at all. But I think MATCH is not one of them.

P.S.
If you would like to share your code I'd be happy to include MATCH in fpspreadsheet.
« Last Edit: December 22, 2018, 07:36:38 pm by wp »
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

SeregaKR

  • New member
  • *
  • Posts: 35
Re: Registering formula in FPSpreadsheet
« Reply #2 on: December 24, 2018, 06:14:33 am »
I will try. But there is a catch in the match function. It look like this:
Code: Pascal  [Select]
  1. =MATCH(lookup_value, lookup_array, [match_type])

Depending on match_type the function returns either first matched index (match_type = 0), maximum matched index (match_type = 1) or maximum and minimum index (match_type = -1). Also it checks if the values in the lookup_array are sorted accordingly.

So I think I will implement it, but I'm not sure how long it will take me.

wp

  • Hero Member
  • *****
  • Posts: 5652
Re: Registering formula in FPSpreadsheet
« Reply #3 on: December 24, 2018, 10:43:09 am »
Consider these items, too:
  • lookup_array: This term is rather diffuse. Play with Excel to find out whether this must be a contiguous 1-dimensional array (e.g. A1:A20, or A1:G1), or whether a non-contiguous array is accepted, too (e.g. A1:20;A30:A40, or A1:20;B3:B10), or whether even a 2-dimensional array (A1:C20) is allowed. What happens if a contiguous 1-dimensional array is required but a 2-dimensional array is used as a parameter? Error result, or is just the 1st column evaluated silently?
  • Returned index: Is this 0-based or 1-based? For example, if the lookup_array is B2:B10, and the found item is in the 1st cell of the array, B2, is the index 0 or 1? If a 2D array is allowed, does the index increase along the row or along the column?
  • What is returned if the array is not sorted?
  • According to https://www.techonthenet.com/excel/formulas/match.php, you must also consider the case that the lookup_value contains wildcard characters '*' or '?'.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

wp

  • Hero Member
  • *****
  • Posts: 5652
Re: Registering formula in FPSpreadsheet
« Reply #4 on: December 25, 2018, 12:10:35 am »
In the new revision on CCR, the formula MATCH is implemented. To answer my questions in the previous post:
  • lookup_array is enforced to be a 1D range
  • returned index is 1 based in accordance with Excel
  • unsorted array: my code always seeks the array from end to start and returns the first matching index found. Excel, however,
    seems to seek from start to end and thus will find different values if the array is unsorted.
  • Wildcard character '*' is not supported. It will be as soon as FPC support this wilchard in the function FindPart.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10