Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forumla to change values on situation
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forumla to change values on situation
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) "Ernest Lai" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forumla to change values on situation
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 In article , "Ernest Lai" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forumla to change values on situation
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) "Ernest Lai" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Forumla to change values on situation
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 Ernest Lai wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero values in a log chart | Charts and Charting in Excel | |||
Find function | Excel Worksheet Functions | |||
Formulas not recalculating when values change on another sheet | Excel Worksheet Functions | |||
Change all values in an excel column at once? | Excel Discussion (Misc queries) | |||
How do I link two cells as to allow me to change the value in eit. | Excel Discussion (Misc queries) |