how do I create a spread sheet to automatically calculate when I

[wbinfo]

On a new spread sheet I am entering numbers and I would like them to be
multiplied and changed automatically. The formula is x*3 then less 40%.

Can anyone help me.

 

[JE McGimpsey]

One way:

If you are entering your numbers in column A, say, then in column B
enter:

B1: =(A1*3)*(1-40%)

or, better,

B1: =A1*3*60%

copy B1 down as far as necessary.

 

[Gord Dibben]

Do you want them changed in the same cell in which you type them?

You would then have no paper trail for checking errors in input.

John's suggestion about having a formula in a separate column is the safest
way to go.

If you want to change them as you enter paste this code into a sheet module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" Then
With Excel.Range("A" & n)
.Value = .Value * 3 * 0.6
End With
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben Excel MVP