Help with formula to convert numbers to negative

[4lvis_bondarenko]

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.

[#2]

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.

[#3]

@4lvis_bondarenko 

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)

[#4]

@Rajender Singh 

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.

[#5]

Yes I did mean convert the number in the same cell

[#6]

@4lvis_bondarenko 

If so when only with VBA programming, but that's not my territory.

[#7]

@4lvis_bondarenko 

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] )

[#8]

Can you explain the relationship between "Target 2" and "Target 2 Pips"?
With that relationship specified or ignored the solution might follow.

[#9]

@Peter Bartholomew 

My understanding:

If ExitPips > 0 and ExitReason = "S"
then ExitPips = -ExitPips
else ExitPips = ExitPips

[#10]

@4lvis_bondarenko 

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