Forum Discussion
Help with formula to convert numbers to negative
Can anyone help with formula to convert numbers in cell to negative ones for the following scenario:
IF cell=''text'' then convert to negative
it also has to be nested with already exsiting formula.
Hope that makes sense.
Thanks in advance.
9 Replies
- Can you explain the relationship between "Target 2" and "Target 2 Pips"?
With that relationship specified or ignored the solution might follow. I fail to see the problem. You already have a formula that determines the content of the 'ExitPips' (whatever they might be) that applies when
ExitReason="S"
so all you need is a minus sign before the result.
I would also convert your data to an Excel Table so your formula would read
= IF([@ExitReason]="T1",[@Target1Pips],IF([@ExitReason]="T1&T2",[@Target1Pips]+[@Target2Pips],IF([@ExitReason]="S",-[@MissingPips],"")))where 'MissingPips' is the Field Name I have applied to the column you reference but do not show in the picture. Better would be to use the newer SWITCH function
= SWITCH( [@ExitReason], "T1", [@Target1Pips], "T1&T2", [@Target1Pips]+[@Target2Pips], "S", -[@MissingPips] )My understanding:
If ExitPips > 0 and ExitReason = "S" then ExitPips = -ExitPips else ExitPips = ExitPips
Do you mean convert keeping the number in the same cell. When VBA. If to put result into another cell, it could be
=IF(B1="text", -ABS(A1), A1)- Yes I did mean convert the number in the same cell
If so when only with VBA programming, but that's not my territory.
- Hi,
Pls try to multiple your true/false condition with -1.
if this is not your requirement than, pls share some sample data with desired result.Am tryin to make column U convert its value to negative if column S states "S" or if its in red.
Ive tried to add at the end of exsisting formula =IF(S3:S10001="S",*-1 but it didnt seem to work.
If you are looking for a vba code, then you could try this. Right click on your worksheet tab, select view code, and paste this code into the code window that appears. Then, save the workbook as an *.xlsm (macro enabled workbook).
Private Sub Worksheet_Change(ByVal Target As Range) Dim exitReasonRng As Range: Set exitReasonRng = Me.Range("S:S") Dim exitPipsRng As Range: Set exitPipsRng = Me.Range("U:U") On Error GoTo ErrHandler Application.EnableEvents = False If Not Intersect(Target.EntireRow, Union(exitReasonRng, exitPipsRng)) Is Nothing Then If UCase(Intersect(Target.EntireRow, exitReasonRng).Value) = "S" Then With Intersect(Target.EntireRow, exitPipsRng) If IsNumeric(.Value) And Len(.Value) > 0 Then .Value = -Abs(.Value) End If End With End If End If ExitProc: Application.EnableEvents = True Exit Sub ErrHandler: MsgBox "Error " & Err.Number & ": " & Err.Description Resume ExitProc End Sub
