Is this Conditional Sum task

[fyrnetics]

I'm using Excel 97

Is there an easy route so that all entries in a {numbers} column, are converted to a figure that always divisible by "3" in this case
ie the outer quantities of this particular product.
Thanks a lot

[gazornenplat]

Hi TVT (tapping away in Surrey, myself),

Do you want a formula?

=INT([cellref]/3)*3 ... for lower
=INT(([cellref]+2)/3)*3 ... for higher

or do you want to convert data entry?

Gaz

[fyrnetics]

Thanks; yes it would be converting a data entry.

Hi TVT (tapping away in Surrey, myself),

Do you want a formula?

=INT([cellref]/3)*3 ... for lower
=INT(([cellref]+2)/3)*3 ... for higher

or do you want to convert data entry?

Gaz

[Bob Phillips]

or

=FLOOR(A1,3)
=CEILING(A1,3)

or even

=ROUND(A1/3,0)*3

for the nearest 3

--
HTH

Bob Phillips

"gazornenplat" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi TVT (tapping away in Surrey, myself),
>
> Do you want a formula?
>
> =INT([cellref]/3)*3 ... for lower
> =INT(([cellref]+2)/3)*3 ... for higher
>
> or do you want to convert data entry?
>
> Gaz
>
>
> --
> gazornenplat
>
>
> ------------------------------------------------------------------------
> gazornenplat's Profile:
http://www.excelforum.com/member.php...o&userid=24494
> View this thread: http://www.excelforum.com/showthread...hreadid=381182
>

[gazornenplat]

OK, then,

Press ALT+F11 to get to the VBE

Double click the sheet you want to add the functionality to

Cut and paste in

'--------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Select Case Target.Column
Case 1 To 3
If IsNumeric(Target) Then Target = 3 * (Target \ 3)
End Select
End Sub
'--------------------------------------------------------------------------------


Change the column numbers to whatever you want

If you need more help, give me a shout

Gaz

[fyrnetics]

Thanks all