 Help with formula to convert numbers to negative

Occasional Contributor

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.

9 Replies

Re: Help with formula to convert numbers to negative

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.

Re: Help with formula to convert numbers to negative

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)

Re: Help with formula to convert numbers to negative 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.

Re: Help with formula to convert numbers to negative

Yes I did mean convert the number in the same cell

Re: Help with formula to convert numbers to negative

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

Re: Help with formula to convert numbers to negative

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.

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

Re: Help with formula to convert numbers to negative

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

Re: Help with formula to convert numbers to negative

My understanding:

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

Re: Help with formula to convert numbers to negative

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