Exact Multiply in Excel

  • Please see the excel sheet.


    Please note that I have entered random between formula that may change values in your system.


    I have entered =RANDBETWEEN(100,999)/99 in cell C8 and have placed comma style to 2
    The value of randbetween I got is 8.52.


    Now if we manually multiply 8.52 x 1000 we should get 8520,
    whereas Excel calculate it as 8515.


    I would like excel to calculate it as 8520 only.


    I tried the rounding formula like =ROUND((C8*E8),-1), but that did not help as I have many other data where -1 does not give correct result.

  • Re: Exact Multiply in Excel


    You should round the original formulas:


    =ROUND(RANDBETWEEN(100,999)/99, 2)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: Exact Multiply in Excel


    I'd guess that your initial result was actually 8.515, but the cell was formatted to only show 2dp. So it rounds it for display (8.515 rounded nearest to 2dp is 8.52) but still retains the full precision 8.515 within the cell.


    As Rory pointed out, rounding the original formula actually converts the cell contents to 8.52 rather than just for display purposes.


    So, it was always multiplying accurately, it's just you weren't aware of the true input to the multiplication.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!