Formula help?

  • Thread starter Thread starter Lovelock, David
  • Start date Start date
L

Lovelock, David

I need to write a formula in excel that say's:
IF E17 = 5
Then C24 should be C24/2
Else C24.

Currently C24 is a calculation for material cost, IF a particular
option is picked I want to cut that cost in half, if the option is not
picked leave the cost alone. Does anyone have a suggestion as to how to
do this. I assume I should put this formula in a cell other than C24
since C24 is already a calculated field.
 
Hi

A cell can have either a value or a formula. So in another cell, say D24:
=IF(E17=5,C24/2,C24)
 
David,

You cannot do it with worksheet formulae as if the formula goes in C24, the
value in C24 gets replaced, if it goes elsewhere you can't change C24.

What you can do is use VBA event code.

Put this in the worksheet code module (right-click sheet name tab, select
View Code)

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("C24")) Is Nothing Then
If Range("E17").Value = 5 Then
Target.Value = Target.Value / 2
End If
End If

ws_exit:
Application.EnableEvents = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
David,

A slightly improved version that will allow E17 to be set to 5 after C24 is
entered and reduce C24 by half, or multiplies C24 by 2 if E17 is changed
from 5

Dim oldValue

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("C24")) Is Nothing Then
If Range("E17").Value = 5 Then
Target.Value = Target.Value / 2
End If
ElseIf Not Intersect(Target, Range("E17")) Is Nothing Then
If Target.Value = 5 Then
Range("C24").Value = Range("C24").Value / 2
ElseIf oldValue = 5 Then
Range("C24").Value = Range("C24").Value * 2
End If
End If

oldValue = Range("E17")

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob Phillips said:
David,

You cannot do it with worksheet formulae as if the formula goes in C24, the
value in C24 gets replaced, if it goes elsewhere you can't change C24.

What you can do is use VBA event code.

Put this in the worksheet code module (right-click sheet name tab, select
View Code)

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("C24")) Is Nothing Then
If Range("E17").Value = 5 Then
Target.Value = Target.Value / 2
End If
End If

ws_exit:
Application.EnableEvents = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

message news:[email protected]...
Click to expand...
 
David,
You can use an IF statement in cell C24.

=IF(E17=5,yourformula/2,yourformula)

Good Luck,
Mark Graesser
(e-mail address removed)

----- Lovelock" <Lovelock>, "David > wrote: -----

I need to write a formula in excel that say's:
IF E17 = 5
Then C24 should be C24/2
Else C24.

Currently C24 is a calculation for material cost, IF a particular
option is picked I want to cut that cost in half, if the option is not
picked leave the cost alone. Does anyone have a suggestion as to how to
do this. I assume I should put this formula in a cell other than C24
since C24 is already a calculated field.
 
You say that C24 is a calculation. That means that you have a formula in
that cell. Modify that formula to include the IF that Harald gave you.
HTH Otto
 
Back
Top