Add "ROUNDUP" formula and correct "ROUNDDOWN" formula
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
veb86 · 2024-05-28 11:43

Hello
I added rounding up. I provide the code here and in the file:
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
INT_EXCEL_SHEET_FUNC_ROUNDUP    = 212;  // not available in
BIFF2 AddFunction(cat, 'ROUNDUP',   'F', 'FF',  
INT_EXCEL_SHEET_FUNC_ROUNDUP,    @fpsROUNDUP);  function
MyRoundUp(const AValue: Double; const Digits: TRoundToRange):
Double;var  RV,FValue,tmp,integral: Double;begin   FValue :=
ABS(AValue);  RV := IntPower(10, Digits);  tmp := FValue * RV; 
integral := Int(tmp);  if ((Frac(tmp)/RV)<1e-12) then     tmp :=
integral  else     tmp := integral +1;  Result :=
ABS(tmp/RV)*sign(AValue);end;    { The Excel ROUNDUP function returns
a number rounded UP to a given number  of decimal places. Unlike
standard rounding, where only numbers less than 5  are rounded UP,
ROUNDUP rounds all numbers up. }procedure fpsROUNDUP(var Result:
TsExpressionResult; const Args: TsExprParameterArray);var  x:
TsExprFloat;  n: Integer;begin  x := ArgToFloat(Args[1]);  if IsNaN(x)
then    Result := ErrorResult(errWrongType)  else begin    n :=
Round(x);    x := ArgToFloat(Args[0]);    if IsNaN(x) then      Result
:= ErrorResult(errWrongType)    else      Result :=
FloatResult(MyRoundUp(x, n));  end;end; 
Correct rounding down:
Now this is the code
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, 'ROUNDDOWN', 'F', 'F',  
INT_EXCEL_SHEET_FUNC_ROUNDDOWN,  @fpsROUNDDOWN); << this formula was
not read Need this code:
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, 'ROUNDDOWN', 'F', 'FF',  
INT_EXCEL_SHEET_FUNC_ROUNDDOWN,  @fpsROUNDDOWN);
Now this is the code
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 function
MyRoundDown(const AValue: Double; const Digits: TRoundToRange):
Double;var  RV: Double;begin  RV := IntPower(10, Digits);  Result :=
Trunc(AValue / RV) * RV;end;  { The Excel ROUNDDOWN function returns a
number rounded down to a given number  of decimal places. Unlike
standard rounding, where only numbers less than 5  are rounded down,
ROUNDDOWN rounds all numbers down. }procedure fpsROUNDDOWN(var Result:
TsExpressionResult; const Args: TsExprParameterArray);var  x:
TsExprFloat;  n: Integer;begin  x := ArgToFloat(Args[1]);  if IsNaN(x)
then    Result := ErrorResult(errWrongType)  else begin    n :=
Round(x);    x := ArgToFloat(Args[0]);    if IsNaN(x) then      Result
:= ErrorResult(errWrongType)    else      Result :=
FloatResult(MyRoundDown(x, -n));  end;end;   ROUNDDOWN(1.15,2) >>
1.14, but it should have been 1.15

I corrected the code, you need to do 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 function
MyRoundDown(const AValue: Double; const Digits: TRoundToRange):
Double;var  RV,FValue,tmp,integral: Double;begin   FValue :=
ABS(AValue);  RV := IntPower(10, Digits);  tmp := FValue * RV; 
integral := Int(tmp);  if (((1-Frac(tmp))/RV)<1e-12) then     tmp :=
integral +1  else     tmp := integral;  Result :=
ABS(tmp/RV)*sign(AValue);end;   procedure fpsROUNDDOWN(var Result:
TsExpressionResult; const Args: TsExprParameterArray);var  x:
TsExprFloat;  n: Integer;begin  x := ArgToFloat(Args[1]);  if IsNaN(x)
then    Result := ErrorResult(errWrongType)  else begin    n :=
Round(x);    x := ArgToFloat(Args[0]);    if IsNaN(x) then      Result
:= ErrorResult(errWrongType)    else      Result :=
FloatResult(MyRoundDown(x, n));  << n - without minus  end;end; 
I checked the results of the function, everything matches

Please add to the project. For convenience, I am adding the file pas

----------------------------------------------------------------------
wp · 2024-05-28 15:13

Thank you. Added to the svn version.