Complex 'IF' scenario

Hi Admin,

I need to do a percentage variance this year vs last year. However, there is a very complex requirement which I cannot complete it purely using 'IF' function. The comparison is on an expense which can be positive and negative by nature. Assuming last year number is 100, current year number is 150. Here are the scenario:
1. C/L: If c =0, then show 'N'A
2. C/L: If c = +ve, L = -ve, the ratio = (C-L)/-L = (150-(-100))/-(-100) = 250%. Negative -100 --> 150 = improve 250%
3. C/L: If c = +ve, L = +ve, the ratio = (C/L)-1 = 150/100-1 = 50%
4. C/L: If c = -ve, L = +ve, the ratio = ((C-L)/L) = (-150-100)/100 = -250%. Similar reason as #2
5. C/L: If c = -ve, L = -ve:
a. C -ve > L -ve (C is more negative), the ratio = -((C/L)-1) = -((-150/-100)-1) = -50%. Even though both are negative, but this year nagative is bigger and thus the ration should be negative
b. C- ve < L -ve (L is more negative), the ration = -C/L+1 = -(-99/-100)+1= 1%This year is showing improvement vs last year even though this year is negative
6. Everything is the same as 2 - 5 except if the ration is exceeding +200% or -200%, it should show '++' or '- -'.

So in general, we would like to show a meaningfull ration for 2 year figures on the monthly management report. Is there anyway to do it properly??

Thanks Special-K. I learned a new thing from you using 'Sign' command. However, I don't quite understand the meaning of this portion of the formula '-(C/L-(C<L)+(L<C))'. There is no need to use 'IF' function in this formula but it can still do the calculation based on condition?
Anyway, thanks for your help.

In addition, as I don't want to show crazy 9999999% in the ratio, how can I show something meaningful if the ratio exceeds a ratio say 300%?