Adding formula "IFS". I need help

[veb86]

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][+][-]

1.     // Logical
2.     cat := bcLogical;  
3.     AddFunction(cat, 'IFS',       'B', 'B?+',  INT_EXCEL_SHEET_FUNC_UNKNOWN,   @fpsIFS);      
4.  

I won't be able to check her work:

Code: Pascal  [Select][+][-]

1. procedure fpsIFS(var Result: TsExpressionResult; const Args: TsExprParameterArray);
2. // IFS( condition, value_if_true, [condition], [value_if_true], [condition], [value_if_true] )
3. var
4.   i:integer;
5. begin
6.   if (Length(Args) mod 2 <> 0) and (Length(Args) < 2) then
7.   begin
8.     Result := ErrorResult(errArgError);
9.     exit;
10.   end;
11.  
12.   Result := ErrorResult(errArgError);
13.   i:=0;
14.   while(i <= Length(Args)) do begin
15.     if ArgToBoolean(Args[i]) then
16.     begin
17.       Result := Args[i+1];
18.       break;
19.     end;
20.     inc(i, 2);
21.   end;
22. end;  
23.  

[vk_barnaul]

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

[wp]

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][+][-]

1. procedure fpsIFS(var Result: TsExpressionResult; const Args: TsExprParameterArray);
2. // IFS( condition, value_if_true, [condition], [value_if_true], [condition], [value_if_true] )
3. var
4.   i:integer;
5. begin
6.   Result := ErrorResult(errArgError);
7.   if (Length(Args) mod 2 <> 0 then   // We always need pairs of args
8.     exit;                            // --> If not, exit with argument eror
9.   i:=0;
10.   while(i < Length(Args)-1) do begin
11.     if ArgToBoolean(Args[i]) then
12.     begin
13.       Result := Args[i+1];
14.       break;
15.     end; // What if ArgToBoolean is false?
16.     inc(i, 2);
17.   end;
18. end;  
19.  

Please post you final working implementation so that I can add it to fps.

[veb86]

Sorry, 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][+][-]

1.  // Logical
2.     cat := bcLogical;  
3.     AddFunction(cat, 'IFS',       'B', 'B?+',  INT_EXCEL_SHEET_FUNC_UNKNOWN,   @fpsIFS);      
4.  

[wp]

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]

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.

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]

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