ROUNDUP function issue

HI, I am using the evaluation version of Aspose.Cells for java.
I am an employee of Credit Agricole Corporate and Investisment Bank.
Whille testing, i found a bug about the excel function ROUNDUP.

In an excel sheet try to put the formula ROUNDUP(0.009357; 7)
excel gives the result 0.009357. The same formula with Aspose.Celles for java gives 0,0093571.

Can you please tell me when you can correct this bug ?
Thank you.

@namjun.pyun,

Thanks for the details.

After an initial test, I am able to reproduce the issue as you mentioned using the following sample code:
e.g
Sample code:

Workbook workbook = new Workbook();
        Worksheet worksheet = workbook.getWorksheets().get(0);
        Cell cell = worksheet.getCells().get("A1");
        cell.setFormula("=ROUNDUP(0.009357, 7)");
        workbook.calculateFormula();
        System.out.println(cell.getValue());

output:
0.0093571

But But I guess the issue might be more related to JAVA (JDK SDK) rather than with Aspose.Cells API. Anyways, I have logged a ticket with an id “CELLSJAVA-43232” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

Hi actually it is not exactly a java issue.
Here is the exact representation as double of 0.009357:
0.0093570000000000007.
Hence, as we round up, because of the 7 it will give +1 to the last digit. It means that ROUNDUP(0.009357, x) for any x should give the wrong result.
I suppose it is the same for ROUNDDOWN for number like 0.009352 which representation is 0.0093519999999999992, for x greater than 7 will give numbers like 0.009351999

To solve this, you should use temporary BigDecimal
and apply first to the double a round with MathContext context = new MathContext(15, RoundingMode.HALF_UP);

Then use the BigDecimla.rount(MathContext) with the correct MathContext

finally use the BigDecimal.doubleValue() to get the new Double

here is an example of code:

    MathContext context = new MathContext(15, RoundingMode.HALF_UP);
    BigDecimal with15Precision = new BigDecimal(0.009357, context);
    BigDecimal withRounding = with15Precision.round(new MathContext(4, RoundingMode.CEILING));
    double double15Precision = with15Precision.doubleValue();
    double doubleWithRouding = withRounding.doubleValue();

    System.out.println(double15Precision); // 0.009357
    System.out.println(doubleWithRouding) // 0.009357

The same idea should be applied to ROUNDDOWN

@namaman,

Thanks for sharing further details.

Since we have logged a ticket for your issue, so kindly spare us little to time to investigate and evaluate your issue thoroughly. We will surely figure it out.

Once we have any new information, we will share it with you.

@namaman,
For performance consideration, we are afraid BigDecimal is not the preferred solution. We prefer to solving the issue with double value check and calculation. Hopefully the Attached fix can solve all such kind of issues.

aspose-cells-20.6.5-java.zip (7.1 MB)

HI, I made a mistake in my email account while creating the account the first time and I forgot the password too.
Then I create another account with the the good email.
But I cannot download this zip file, since the owner is the other account.
How can I get this file ?

@namaman,

Please create a new thread and ask for latest fix of Aspose.Cells for Java for your issue, we will share the new fix in your newer thread.

the new thread is : ROUNDUP function issue bis
Thank you

@namaman,

I have shared the fix in your other thread.

The issues you have found earlier (filed as CELLSJAVA-43232) have been fixed in Aspose.Cells for Java 20.7. This message was posted using Bugs notification tool by Amjad_Sahi