Function or formula ?

[tarheelrooster]

I have a cell D17 that is formatted tog give an answer when A4 is divided by
N4. I want to divide the answer of D17 by 1.73 when I put a 3 in cell P4. I
would like the 3 to stay in P4. I will from time to time put a 1 in P4 but I
do not want the 1 to change anything.

 

[Eduardo]

Hi,
I don;'t completely understand, if you put 1 in P4 you want D17 divided by
1.73 ?
to have D17 divided by 1.73 when 3 is entered use

=if(P4=3,D17/3)

the above formula will give you False if value in P4 is not 3

if this helps please click yes thanks

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "P4" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Target.Value = 3 Then

Me.Range("D17").Value = Me.Range("D17").Value / 1.73
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

 

[Brad]

If P4 will only have the values of 1 or 3

then simply change the formula of (A4/N4) to (A4/N4)/P4. When you divide
anything by one - you still get the original number - otherwise you will get
the number divided by three.

However if P4 can be blank - an easy fix would be to change the revised
equation above to (A4/N4)/if(P4=3,3,1)

 

[JoeU2004]

I have a cell D17 that is formatted tog give an answer when A4 is divided
by
N4. I want to divide the answer of D17 by 1.73 when I put a 3 in cell P4.
I
would like the 3 to stay in P4. I will from time to time put a 1 in P4
but I
do not want the 1 to change anything.

=if(P4 = 3, D17 / 1.73, D17)

 

[Brad]

One small correction to my formula
(A4/N4)/if(P4=3,3,1)
should be
(A4/N4)/if(P4=3,1.73,1)