Excel formula question

Pipeandslippers20

Displaying "increase", "decrease" or "stable" depending on number value

If anybody can help me with this I'd be very grateful.

What I want to do is basically assign a flag (based on conditional formatting) according to a number value. Sounds simple.

I'm looking at birth rates, in 2010 there might be 100. In 2009 there were 99.

I want to do 3 things. If the percentage change in birth rate between the years is either 0, OR is between 0 and 1, then I want to say it's stable. If it goes up or down by more than a percentage I want it to say "Increase" or "Decrease". It's the between 0 and 1 range I'm having difficulty with.
The formula I have is along the lines of:

=IF(AND(AG9>=99,AG9<=101),"stable"),IF(AG9>101,"Increase",IF(AG9<99,"decrease"))

Not terribly difficult, but's it's driving me mad and I don't have much time..

Would be very grateful for any pointers

john87_2

how about:

>101 = increase
< 99 = decrease
otherwise = stable

=IF(AG9>101,"Increase",IF(AG9<99,"decrease","stable"))

(or change the 99 to 100 in the above)

Pipeandslippers20

I knew it was something simple. I was looking for something too complex, looking at a range.

Top work. Think I've stared at the screen too long.

john87 wrote: »

how about:

>101 = increase
< 99 = decrease
otherwise = stable

=IF(AG9>101,"Increase",IF(AG9<99,"decrease","stable"))

mambo69

cant you say if <99 decrease if >101 increase and if >99 stable ?

Pipeandslippers20

thanks for your help guys. Both would work. What I was trying to find was this one:

=IF(ABS(AG9-AF9)<=1,"Stable",IF(AG9>AF9,"Increase","Decrease"))

i.e. the ABS function