Help.....Formula Required

[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

 

[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

 

[Domenic]

=CEILING(A1,10)-A1

Hope this helps!

 

[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

 

[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

 

[Bob Phillips]

Fiona,

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

 

[Fiona O'Grady]

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

Fiona

 

[Bob Phillips]

This early ... on a Sunday <vbg>?

Bob