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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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
 
Back
Top