Formula for IF condition to get below result in comparison column

[Desaisid]

For below table i require if condition in formula tool to get result in comparison column.

1. If Rates and Other CP Rates are same then in comparison column it will show as AD.

2. If we see negative signage in either side with same number then in comparison column it will show as match.

3. If either side is blank then in comparison column it will show as singlet.

4. If both sides are blank then in comparison column it will show as blank.

Rates	Other CP Rates	Comparison
100	100	AD
100	-100	match
100		singlet
	100	singlet
		blank

[aatalai]

@Desaisid try this

If [rates]=[other rates] then "AD"

elseif mod([rates])=mod([other rates]) then "match"

elseif isempty([other rates]) and isempty([rates]) then "blank"

elseif isempty([other rates]) or isempty([rates]) then"singlet"

Else "check" endif

[Desaisid]

Hi aatalai

Can you suggest an alternative as this line is not working :elseif mod([rates])=mod([other rates]) then "match"

[JohnMcFadyen]

The mod function was showing an error, I had a go at re-writing the if statement, this looks to work,

If isnull([Other CP Rates]) and isnull([Rates]) then "blank"

elseif [Rates] = [Other CP Rates] then "AD"

elseif abs([Rates]) = abs([Other CP Rates])
and
([Rates] < 0 or [Other CP Rates] < 0) then "match"

elseif isnull([Other CP Rates]) or isnull([Rates]) then"singlet"

Else "check" endif

 

[aatalai]

@JohnMcFadyen 

this line in your formula might not work  

([Rates] < 0 or [Other CP Rates] < 0) then "match"

as you might have a rate of -150 and other of -100 which would say match which from my understanding is not what @Desaisid was looking for.

In regards to the mod part you are right it should be abs

e.g.  elseif abs([rates])=abs([other rates]) then "match"

@Desaisid hope this helps

[nagakavyasri]

@Desaisid