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 for...

Formulas and Functions

PeterBartholomew1

Silver Contributor

Mar 06, 2021

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

SergeiBaklan

Diamond Contributor

Mar 06, 2021

PeterBartholomew1

My understanding:

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

Forum Discussion

Help with formula to convert numbers to negative

Resources