Adding formula "IFS". I need help
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
veb86 · 2024-06-21 07:22

I can't add a formula.
I don't register it correctly, because there is no formula in the
saved file, there is only a value.
Please help me register the IFS formula correctly.

Website with a description of how the formula works:
https://support.microsoft.com/en-us/office/ifs-
function-36329a26-37b2-467c-972b-4a39bd951d45

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     //
Logical    cat := bcLogical;      AddFunction(cat, 'IFS',       'B',
'B?+',  INT_EXCEL_SHEET_FUNC_UNKNOWN,   @fpsIFS);       
I won't be able to check her work:
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 procedure
fpsIFS(var Result: TsExpressionResult; const Args:
TsExprParameterArray);// IFS( condition, value_if_true, [condition],
[value_if_true], [condition], [value_if_true] )var  i:integer;begin 
if (Length(Args) mod 2 <> 0) and (Length(Args) < 2) then  begin   
Result := ErrorResult(errArgError);    exit;  end;   Result :=
ErrorResult(errArgError);  i:=0;  while(i <= Length(Args)) do begin  
if ArgToBoolean(Args[i]) then    begin      Result := Args[i+1];     
break;    end;    inc(i, 2);  end;end;

----------------------------------------------------------------------
vk_barnaul · 2024-06-22 05:22

if (Length(Args) mod 2 <> 0) and (Length(Args) < 2) then
=>
if (Length(Args) mod 2 <> 0) or (Length(Args) < 2) then

----------------------------------------------------------------------
wp · 2024-06-22 08:55

Sorry, I cannot implement this function ATM, but you'll get it done
certainly by yourself:

Your usage of the Args array strikes my eyes: Args is a zero-based
array, the maximum index therefore is Length(Args)-1. But your "i
loop" runs in the range "while i <= Length(Args)"- the '=' certainly
is wrong here. And the next complication is that all arguments come in
pairs, i.e. even for i=Length(Args)-1 you'll run over the array limits
when you do "Result :=Args[i+1]"

In total my (probably incomplete) proposal to fix the issue is

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 procedure
fpsIFS(var Result: TsExpressionResult; const Args:
TsExprParameterArray);// IFS( condition, value_if_true, [condition],
[value_if_true], [condition], [value_if_true] )var  i:integer;begin 
Result := ErrorResult(errArgError);  if (Length(Args) mod 2 <> 0 then
 // We always need pairs of args    exit;                          
 // --> If not, exit with argument eror  i:=0;  while(i <
Length(Args)-1) do begin    if ArgToBoolean(Args[i]) then    begin   
 Result := Args[i+1];      break;    end; // What if ArgToBoolean is
false?     inc(i, 2);  end;end;   
Please post you final working implementation so that I can add it to
fps.

----------------------------------------------------------------------
veb86 · 2024-06-22 14:18

Quote from: wp on June 22, 2024, 10:55:16 amSorry, I cannot implement
this function ATM, but you'll get it done certainly by yourself:.
Sorry, but I don't understand what ATM.
I will set up the formula and check it.  The problem is that it is not
registered.   The library does not see that the IFS formula is being
used.  I can't register it correctly
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  //
Logical    cat := bcLogical;      AddFunction(cat, 'IFS',       'B',
'B?+',  INT_EXCEL_SHEET_FUNC_UNKNOWN,   @fpsIFS);

----------------------------------------------------------------------
wp · 2024-06-22 18:50

Noticed that IFS requires at least Excel 2019, and I only have 2016. A
ods test file can be opened by LibreOffice Calc, nevertheless.

Committed above implementation with your registration line

Trying to open the ods test file with Spready (simple spreadsheet demo
for fps), however, fails. Peeking into the ods file I get the
impression that the function should be named 'COM.MICROSOFT.IFS'
rather than 'IFS' -- this way the ods test file is readable by
Spready. However, very probably Excel might reject a formula with that
name...

Difficult

If you have a new-enough Excel version in which IFS is implemented,
submit it here so that I can look at how the formula is named.

----------------------------------------------------------------------
veb86 · 2024-06-23 06:47

Quote from: wp on June 22, 2024, 08:50:49 pmIf you have a new-enough
Excel version in which IFS is implemented, submit it here so that I
can look at how the formula is named.
I have EXCEL2019.

In cell A1, I added the IFS formula.

I thought it would be easier to add. If the addition is complex, then
in this case I can get by with the IF formula. It’s just that the IFS
looks neater in my case.

----------------------------------------------------------------------
wp · 2024-06-23 10:11

Thank you. It seems that Excel writes "_xlfn.IFS" to the file, and
LibreOffice writes "COM.MICROSOFT.IFS", and the user expects to write
"IFS". Guess there is some transformation table with is missing. Maybe
I have an idea about it in the next days....