Share via

How do I get excel to use the rounded number rather than the "real" number?

Anonymous
2019-07-17T13:19:42+00:00

So I have some cells which are the average of the same cells from three other sheets. These are then rounded using the Decrease Decimal button to single figures. I then want to multiply these numbers by the contents of the next column, however it's using the original numbers (e.g. 1.6667) rather than the Decreased Decimal figure (2), so I'm getting results which are completely wrong.

How do I get excel to use the rounded number in a formula rather than the actual one?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2019-07-17T14:17:17+00:00

    Hi Ewarb,

    It's depend on you. 

    The fact is, no matter you decrease or increase decimal, the real value is always saved in the cell, which means you only change the appearance.

    Only use Formula =Round() could get the value of a round number.

    In conclusion, ROUND changes the value while format cell changes appearance. 

    In my opinion, I suggest you keeping the real value in original cells and use ROUND for calculation in the other cells. 

    Regards,

    Eric

    2 people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-07-17T13:30:29+00:00

    Hi Eward,

    Please replace all the cell to Round(Cell,digits) in your formula.

    For example:

    modify =A1+A2  to =ROUND(A1,0)+ROUND(A2,0)

    Regards,

    Eric

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2019-07-18T15:54:53+00:00

    Hi There ...

    If that what you want for whole sheet then there is simple solution.

    1. Click as highlighted to select all cells of the sheet.
    2. Press -> CTRL + 1 
    3. It will open Format Cell window.

    1. Select Number and change decimal to 0.

    1. Click OK and you should be all set ...!!!

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2019-07-17T14:02:51+00:00

    Thanks Eric

    Should I put the ROUND in the cells which are currently Decimal Decreased?

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2019-07-18T14:46:14+00:00

    Hi Ewarb,

    If you need further help, you can post your latest condition.

    Regards,

    Eric

    0 comments