Forumla to change values on situation

[Ernest Lai]

Hi all,

I have a table of items with "value" "vat" and "total" Columns. I would
like to write a formulae to say

When value is less than 140.5 then change value to 85.5, vat to 14.96,
total to 100.46 Else no change.

so im guessings its something like:
X=1
If AX<140.5 then AX=85.5, BX=14.96, CX=100.46
Else AX=AX,BX=BX,CX=CX
Increment x

Or i could just copy and paste the If Then statment down all the columns and
forget the increment. Hopefully you will see what im trying to do.

Thanks in advance, any help is much appretiated as always.

Ernest Lai

 

[Bob Phillips]

Can't do that with a formula, need VBA.

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 < 140.5 Then
.Value = 85.5
.Offset(0, 1).Value = 0.1496
.Offset(0, 2).Value = 100.46
End If
End With
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.


I also think you calculations are wrong. By mine, with vat on 85.5 gives
98.29.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[JE McGimpsey]

Couple considerations:

1) A cell can have either a constant or a formula, not both.
2) A formula can't change a value in another cell.

If your vat column has something like the formula

Bx: =ROUND(Ax * 17.5%, 2)

and you total column has something like:

Cx: =Ax + Bx

then there's no need to change B & C when A is changed.

This macro will change any value in column A that is less than 140.5 to
85.5:

Public Sub LessThan140()
Dim rCell As Range
For Each rCell In Range("A2:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If IsNumeric(.Value) Then _
If .Value < 140.5 Then _
.Value = 85.5
End With
Next rCell
End Sub

Alternatively, if you need columns B & C to be constants, you can use

Public Sub LessThan140()
Dim rCell As Range
For Each rCell In Range("A2:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If IsNumeric(.Value) Then
If .Value < 140.5 Then
.Value = 85.5
.Offset(0, 1).Value = 14.96
.Offset(0, 2).Value = 100.46
End If
End If
End With
Next rCell
End Sub

If you're not familiar with macros, see

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Barb Reinhardt]

You'll have to use some additional columns because otherwise you'll have a
circular reference.

Let's assume the following:

Column A ... Entered Value
Column B Entered VAT
Column C Entered Total
Column D Calculated Value
Column E Calculated VAT
Column F Calculated Total

A2=85.5
D2 =IF(A2<140.5,85.5,A2)
E2=IF(A2<140.5,14.96,B2)
F2=D2+E2 OR =IF(A2<140.5,85.5,C2)

 

[Roger Govier]

Hi Ernest

You have 2 excellent solutions posted by Bob and JE.

I'm just curious to know why you are using 85.5 and 14.96 total 100.46 as
opposed to 85.11 and 14.89 total 100.00.

Regards

Roger Govier