Percentage function

  • Thread starter Thread starter Rachel Bixby
  • Start date Start date
R

Rachel Bixby

I am trying to create a document that if you enter a number into a certain
cell the same cell will auto-populate with what 4% is of the # you entered.

Example:
1) Enter 1,000 into cell
2) The same cell auto populates with 4% of 1,000 which would of course be 40

Can someone please explain to me how to get the formula right and where all
of the information should be entered? Thanks in advance :) !
 
Perhaps better to use an adjacent column for this?
Assume source numbers will be entered in A2 down
Input the percentage in B1, eg: 4%
Then put in B2:=IF(A2="","",A2*B$1)
Copy down as far as required
 
You cannot enter data into a cell and have a calculation done that changes
that cell. Formulas in Excel change other cells. You cannot have a formula
in a cell and enter data into the same cell. The data would replace the
formula.

Tyro
 
You could use event code to change the value when you enter it but then you have
no "paper trail" for error-checking.

I like Max's idea of a helper column so's you can see and make corrections if
needed.

If you want the change-in-cell event code for interest, here it is.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
If .Value <> "" Then
.Value = .Value * 0.04
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Adjust range to suit.


Gord Dibben MS Excel MVP

On Mon, 7 Jan 2008 15:23:01 -0800, Rachel Bixby <Rachel
 
An alternative, if you want the new number in A1, is to put the 4% in a
spare cell, copy, then select A1 (and any other cells you want multiplying),
and edit/ paste special/ multiply. You can now delete the 4% from the spare
cell if you wish to.
 
That works well if you want to change a lot of cells. I thought the OP
wanted to know the formula to compute 4% of something.
 
Back
Top