Registering formula in FPSpreadsheet
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
SeregaKR · 2018-12-22 17:25

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
RegisterFunction('MATCH', 'I', 'F'<---, 64,   @fpsMATCH);    How can I
pass the information that second parameter is a cell range?

----------------------------------------------------------------------
wp · 2018-12-22 18:22

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
AddFunction(cat, 'COUNTBLANK','I', 'R',  
 INT_EXCEL_SHEET_FUNC_COUNTBLANK, @fpsCOUNTBLANK);    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.

----------------------------------------------------------------------
SeregaKR · 2018-12-24 05:14

I will try. But there is a catch in the match function. It look like
this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
=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 · 2018-12-24 09:43

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 '?'.

----------------------------------------------------------------------
wp · 2018-12-24 23:10

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 rangereturned index is 1 based in
accordance with Excelunsorted 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.