Rounding numbers so that they are divisable by 3

[Shazza]

1 B C
2 Original figure Annual Increase
divisible by 3
3 2.25%
£ 15,441.00 £15,788.4225

I want C3 to read £15789.00 as it is the nearest number to £15788.4225 that
is divisible by 3
The formula i used in C3 is =B3+($C$2*B3)
How can i amend this formula to ensure that my figure is divisable by 3

 

[Bob Phillips]

=ROUND((B3+($C$2*B3))/3,0)*3

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H]

Maybe

=MROUND(B3+($C$2*B3),3)

Mike

 

[Joel]

Your original code could be changed
from
B3+($C$2*B3)
to
B3*(1+$C$2)

This is the formula
=INT(B3*(1+$C$2)+CHOOSE(MOD(INT(B3*(1+$C$2)),3)+1,0,2,1))

Taking the INT of the number will get rid of all the decimal places. Taking
the Mod base 3 of the number gets you the remainder when dividing by 3.

Choose uses inexing starting at 1 and you don't want to add anything if the
number is exactly divisible by three

Remainder Add
0 0
1 2
2 1

Adding 1 to the remainder you get
Remainder + 1 Add
1 0
2 2
3 1

which is what I put in the CHOOSE part of the formula

 

[Sandy Mann]

What didn't you like about yesterday's answers?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk