I need a function that rounds a number up to the nearest 6" increment

In other words, if the number to be rounded is 19" the value returned would
be 24". If the number is 20" the value returned would also be 24".

To complicate matters, if the number to be rounded up is less than or equal
to 2" then the the returned value needs to be then 2nd nearest 6" increment.

In other words, if the number to be rounded is 17" or 16" then the returned
value needs to be 24". 15" would round to 18", as would 14, 13, and 12. 10
and 11 would also round to 18".

Make sense? Hope so.

Thanks

Give this a try to round cells in B1

=IF(MOD(B1, 6)=4, B1+12-MOD(B1, 6), B1+6-MOD(B1, 6))
--
HTH...

Jim Thomlinson


"GreenMonster" wrote:

I need a function that rounds a number up to the nearest 6" increment

In other words, if the number to be rounded is 19" the value returned would
be 24". If the number is 20" the value returned would also be 24".

To complicate matters, if the number to be rounded up is less than or equal
to 2" then the the returned value needs to be then 2nd nearest 6" increment.

In other words, if the number to be rounded is 17" or 16" then the returned
value needs to be 24". 15" would round to 18", as would 14, 13, and 12. 10
and 11 would also round to 18".

Make sense? Hope so.

Thanks

=ROUNDUP(A2/6,0)*6

or

=CEILING(A2,6)

--
HTH

Bob Phillips

"GreenMonster" wrote in message
...
I need a function that rounds a number up to the nearest 6" increment

In other words, if the number to be rounded is 19" the value returned
would
be 24". If the number is 20" the value returned would also be 24".

To complicate matters, if the number to be rounded up is less than or
equal
to 2" then the the returned value needs to be then 2nd nearest 6"
increment.

In other words, if the number to be rounded is 17" or 16" then the
returned
value needs to be 24". 15" would round to 18", as would 14, 13, and 12.
10
and 11 would also round to 18".

Make sense? Hope so.

Thanks

I must admit to not having understood that other part, but it seems you did.

Bob

"Jim Thomlinson" wrote in message
...
Give this a try to round cells in B1

=IF(MOD(B1, 6)=4, B1+12-MOD(B1, 6), B1+6-MOD(B1, 6))
--
HTH...

Jim Thomlinson

yep, jim's seems to work fine

--


Gary


"Bob Phillips" wrote in message
...
I must admit to not having understood that other part, but it seems you
did.

Bob

"Jim Thomlinson" wrote in message
...
Give this a try to round cells in B1

=IF(MOD(B1, 6)=4, B1+12-MOD(B1, 6), B1+6-MOD(B1, 6))
--
HTH...

Jim Thomlinson