Help.....Formula Required

G

gareth67

If current contents off cell are a variable no from a previous equatio
(eg 107) HOW TO......... round up to nearest divisible of 10 (eg 110)
and – (minus) the lower figure (107) from the higher (110) and =
answer (3) to be displayed in SAME cell
 
A

Alex Delamain

Sorry just realised you asked for nearest multiple of 10. Roundup will
surprisingly, always round up so if you want the nearest multiple jus
use =ROUND() instead
 
B

Bob Phillips

If you really mean roundup, then

=ROUNDUP(A1,-1)-A1

however if you mean Round, then

=ROUND(A1,-1)-A1


The difference is that ROUNDP will return 9 if A1 contains 101, Round will
return -1
 
F

Fiona O'Grady

If you want the answer to appear in the same cell then you need to include
the original equation within your new equation.

For example my solution to having 107 in A4, with the answer 3 appearing
would be to put the formula

=10-MOD(A4,10) into the "answer" cell.

However if you want 3 to appear in cell A4 (where your original formula is),
and assuming that your orginal formula is =SUM(A1:A3), then your formula
should be

=10-MOD(SUM(A1:A3),10)

Fiona
 
B

Bob Phillips

Fiona,

That actually returns 10 if A1 contains 110, where I would read that the OP
wants 0 in this case.
 
F

Fiona O'Grady

Oops - sorry about that. Must remember not to try doing maths when under
the influence! :)

Fiona
 
Top