Formula VA

[a.menghini]

Hi Larsa,
I use xlsReadWriteII 5 ver. 5.20.57

I have a problem with Excel formula VA ( https://support.office.com/it-it/articl ... 16e8168cbd )

I get an error if you use the formula VA but if I replace the formula with SUM formula all is OK.

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var nVal : Currency;
    XLS : TXLSReadWriteII5;
begin
    XLS := TXLSReadWriteII5.Create(nil);
    try

       XLS[0].AsFloat[ 0, 1 ] := 500;    // A2
       XLS[0].AsFloat[ 0, 2 ] := 0.0083; // A3
       XLS[0].AsFloat[ 0, 3 ] := 20;     // A4
       // XLS[0].AsFormula[ 0, 4 ] := 'SUM( A2:A4 )';                    // <<<<<< NO ERROR
       XLS[0].AsFormula[ 0, 4 ] := 'VA( A3/12; 12*A4; A2; ; 0)';    // <<<<<< ERROR !
       XLS.Calculate;

       nVal := XLS[0].AsFloat[ 0, 4 ];

       ShowMessage( FormatCurr( '#,##0.00;-#,##0.00;#', nVal ) );

       xls.Filename := 'C:\Test.xlsx';
       xls.Write;

    finally
       FreeAndNil(XLS);
    end;
end;

I Try the formula with the same parameter in Excel and all is ok

Can you help me please ?

thanks

[a.menghini]

...I try to use the equivalent English formula PV ( https://support.office.com/en-us/articl ... 16e8168cbd )

but I get "Empty Stack" error

Can you help me please ?

thanks

Alberto

[larsa]

Hello

All parameters in the formulas must have a value. You have written the VA formula with the second last parameter empty "; ;". This is not supported.

[a.menghini]

...great ..many thanks for reply

Alberto