help with if and adding prefix formula

Question

I'm trying to create a formula where if each column is more than 0%, then add a prefix for either "LIBOR+" or "PRIME+" in front of the column in the new column. the below formula keeps giving me the error malformed if statement - can someone please help? also attached an example data below:

IF [Loan Libor Spread2] >0 THEN "LIBOR+" + [Loan Libor Spread2] else if [Loan Prime Spread2] >0 then "PRIME+" + [Loan Prime Spread2] elseif [Loan Fixed Spread2] >0 then [Loan Fixed Spread2] else "" endif

starting:

Loan Libor Spread2Loan Prime Spread2Loan Fixed Spread20.1%   1%   2%

Goal:

Interest RateLIBOR + 0.1%PRIME+1%2%

grazitti_sapna · Answer

@dxia0919, another way is to compare it with a string value.

IF [Loan Libor Spread2] >'0' THEN "LIBOR+" + [Loan Libor Spread2] elseif [Loan Prime Spread2] >'0' then "PRIME+" + [Loan Prime Spread2] elseif [Loan Fixed Spread2] >'0' then [Loan Fixed Spread2] else "" endif

I hope this helps!

Thanks!

ncrlelia · Answer

HI @dxia0919,

Agree with Luke, also I noticed that is an extra space in the ELSEIF in your IF statement

Cheers,

Lelia

Luke_C · Answer

Hi @dxia0919

You're getting the malformed if statement error because you're trying to perform math functions on strings. A simple workaround would be to wrap a tonumber() around each field. You also had an 'else if' instead of 'elseif'

IF ToNumber([Loan Libor Spread2])>0 THEN "LIBOR+" + [Loan Libor Spread2]

Elseif ToNumber([Loan Prime Spread2]) >0 then "PRIME+" + [Loan Prime Spread2]

elseif ToNumber([Loan Fixed Spread2]) >0 then [Loan Fixed Spread2]

else "" endif