0

I am using roundup function in one column of cells in excel. In the next column of cells, i just need to subtract the values from the previous column. But when I use it in the next cell, I want it to take the cell value or the rounded up value but it is still taking the original value...How to avoid this?

Need some guidance to solve this.

Example:

I have put here an example. Fix it and delete this line when you're done, leaving the example

            | A      | B            | C
 -----------+--------+--------------+-----------------
 formulas:  |        | =ROUND(A1;3) | =IF(...;A1;B1)
 values:    | 0,5715 | 0,572        | 0,5715
 -----------+--------+--------------+-----------------

This is my situation:

Value for first row is =ROUND(O7*1000,1) and value for second row is =J8-J7.

The image is enter image description here

You can see that in the 3 row 2 column, the value should be 74, but it is 73. How to change this?

  • 2
    Couldn't you simulate the roundup by setting the appropriate number of decimals in the field format? Then you wouldn't need the rounding. Otherwise, you could put the answer in a hidden cell/column, and in the visible one place =ROUND(hidden_addr). You'd be free to use hidden_addr in the new column. – LSerni Oct 6 '13 at 20:45
  • @PortlandRunner I think u are getting it wrong. It is already rounded up. But when I use it in the next cell, I want it to take the cell value or the rounded up value but it is still taking the original value... – lakesh Oct 6 '13 at 21:01
  • 0.5715 roundsup to 0.571.. it doesn't seem to round up correctly... – lakesh Oct 6 '13 at 21:08
  • Sorry, then I have misunderstood. What do you mean with "the cell value or the rounded up value"? You want the new cell to assume a value instead of another, based on some condition? Might then not the error be in the condition itself? – LSerni Oct 6 '13 at 21:14
  • I've added a sort of an example which you can modify to illustrate what's happening. You can also cut it and paste twice, once with the results you're getting, the other with the results you expected. – LSerni Oct 6 '13 at 21:19
1

You are using =ROUND(O7*1000,1) but that will give you a number with 1 decimal place.

ie if cell O7 = 0.5715, your rounded number will be 571.5.

Your screenshot shows numbers formatted to omit the decimals, but I suspect that they are there hidden behind your 569, 498, 572... These additional decimals that then throw out your subtraction =J8-J7 in the second row.

You want whole numbers so you need to round with zero decimal places:

=ROUND(O7*1000,0)

Note: you do seem to use ROUND() and ROUNDUP() interchangeably in your post - these are two different functions. So you may want =ROUNDUP(O7*1000,0).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.