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

 

[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]...

 

[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?

Ga

 

[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 :cool