### Bookstore

 Computer Math and Games in Pascal (preview) Lazarus Handbook

### Author Topic: Add "ROUNDUP" formula and correct "ROUNDDOWN" formula  (Read 415 times)

#### veb86

• New Member
• Posts: 27
##### Add "ROUNDUP" formula and correct "ROUNDDOWN" formula
« on: May 28, 2024, 01:43:17 pm »
Hello
I added rounding up. I provide the code here and in the file:
Code: Pascal  [Select][+][-]
1. INT_EXCEL_SHEET_FUNC_ROUNDUP    = 212;  // not available in BIFF2
2.
3. AddFunction(cat, 'ROUNDUP',   'F', 'FF',   INT_EXCEL_SHEET_FUNC_ROUNDUP,    @fpsROUNDUP);
4.
5. function MyRoundUp(const AValue: Double; const Digits: TRoundToRange): Double;
6. var
7.   RV,FValue,tmp,integral: Double;
8. begin
9.
10.   FValue := ABS(AValue);
11.   RV := IntPower(10, Digits);
12.   tmp := FValue * RV;
13.   integral := Int(tmp);
14.   if ((Frac(tmp)/RV)<1e-12) then
15.      tmp := integral
16.   else
17.      tmp := integral +1;
18.   Result := ABS(tmp/RV)*sign(AValue);
19. end;
20.
21. { The Excel ROUNDUP function returns a number rounded UP to a given number
22.   of decimal places. Unlike standard rounding, where only numbers less than 5
23.   are rounded UP, ROUNDUP rounds all numbers up. }
24. procedure fpsROUNDUP(var Result: TsExpressionResult; const Args: TsExprParameterArray);
25. var
26.   x: TsExprFloat;
27.   n: Integer;
28. begin
29.   x := ArgToFloat(Args[1]);
30.   if IsNaN(x) then
31.     Result := ErrorResult(errWrongType)
32.   else begin
33.     n := Round(x);
34.     x := ArgToFloat(Args[0]);
35.     if IsNaN(x) then
36.       Result := ErrorResult(errWrongType)
37.     else
38.       Result := FloatResult(MyRoundUp(x, n));
39.   end;
40. end;
41.

Correct rounding down:
Now this is the code
Code: Pascal  [Select][+][-]
1. AddFunction(cat, 'ROUNDDOWN', 'F', 'F',   INT_EXCEL_SHEET_FUNC_ROUNDDOWN,  @fpsROUNDDOWN); << this formula was not read
2.
Need this code:
Code: Pascal  [Select][+][-]
1. AddFunction(cat, 'ROUNDDOWN', 'F', 'FF',   INT_EXCEL_SHEET_FUNC_ROUNDDOWN,  @fpsROUNDDOWN);

Now this is the code
Code: Pascal  [Select][+][-]
1. function MyRoundDown(const AValue: Double; const Digits: TRoundToRange): Double;
2. var
3.   RV: Double;
4. begin
5.   RV := IntPower(10, Digits);
6.   Result := Trunc(AValue / RV) * RV;
7. end;
8.
9.
10. { The Excel ROUNDDOWN function returns a number rounded down to a given number
11.   of decimal places. Unlike standard rounding, where only numbers less than 5
12.   are rounded down, ROUNDDOWN rounds all numbers down. }
13. procedure fpsROUNDDOWN(var Result: TsExpressionResult; const Args: TsExprParameterArray);
14. var
15.   x: TsExprFloat;
16.   n: Integer;
17. begin
18.   x := ArgToFloat(Args[1]);
19.   if IsNaN(x) then
20.     Result := ErrorResult(errWrongType)
21.   else begin
22.     n := Round(x);
23.     x := ArgToFloat(Args[0]);
24.     if IsNaN(x) then
25.       Result := ErrorResult(errWrongType)
26.     else
27.       Result := FloatResult(MyRoundDown(x, -n));
28.   end;
29. end;
30.
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][+][-]
1. function MyRoundDown(const AValue: Double; const Digits: TRoundToRange): Double;
2. var
3.   RV,FValue,tmp,integral: Double;
4. begin
5.
6.   FValue := ABS(AValue);
7.   RV := IntPower(10, Digits);
8.   tmp := FValue * RV;
9.   integral := Int(tmp);
10.   if (((1-Frac(tmp))/RV)<1e-12) then
11.      tmp := integral +1
12.   else
13.      tmp := integral;
14.   Result := ABS(tmp/RV)*sign(AValue);
15. end;
16.
17. procedure fpsROUNDDOWN(var Result: TsExpressionResult; const Args: TsExprParameterArray);
18. var
19.   x: TsExprFloat;
20.   n: Integer;
21. begin
22.   x := ArgToFloat(Args[1]);
23.   if IsNaN(x) then
24.     Result := ErrorResult(errWrongType)
25.   else begin
26.     n := Round(x);
27.     x := ArgToFloat(Args[0]);
28.     if IsNaN(x) then
29.       Result := ErrorResult(errWrongType)
30.     else
31.       Result := FloatResult(MyRoundDown(x, n));  << n - without minus
32.   end;
33. end;
34.
35.

I checked the results of the function, everything matches

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

#### wp

• Hero Member
• Posts: 12069
##### Re: Add "ROUNDUP" formula and correct "ROUNDDOWN" formula
« Reply #1 on: May 28, 2024, 05:13:18 pm »
Thank you. Added to the svn version.