Excel 2007 : Fomula showing as a positive rather than a negative

**docrobo** · 03-15-2012, 11:31 AM

I currently have the following formula =((CP79-CP77)/CP77) This is to calculate -£962.76(in red text) and -£3,557.96(in red text) which equals 269.56%. The answer is correct but it should be in red text and be a minus figure. Can anyone suggrst what I may have done wrong?

**Fotis1991** · 03-15-2012, 11:36 AM

Hi

Pls, change your Tread title, according to the forum rules.

**Fotis1991** · 03-15-2012, 12:30 PM

Hi

So the result for this function, is -270.

So as is not not formatting as a number but as a percentage, the format is not red.

So in this cell, use Conditional formatting.

Condition is: Cell valu<0. >>Red color. OK.

Hope to helps you.

**docrobo** · 03-15-2012, 12:57 PM

The cell is already formated to turn red if it is a minus number. What I need the formula to do is to show my answer 269.56% as -269.56%.

**MrShorty** · 03-15-2012, 01:28 PM

Assuming -3557.96 is in cp79 and -962.76 is in cp77, the formula is correctly calculating +2.6956. Using the absolute value function might be appropriate =(cp79-cp77)/abs(cp77)

**docrobo** · 03-15-2012, 01:46 PM

Hi and thank you MrShorty, unfortunately it did not work so I changed it to =((CP79-CP77)/ABS(CP77)). The answer is now showing as a minus number but the font is not in red and the cell colour is now white not the blue i originally had.

**MrShorty** · 03-15-2012, 02:20 PM

Simply entering the formula should not have changed any of the formatting. Check your formatting/conditional formatting and make sure the proper formats are still applied to that cell.

**Fotis1991** · 03-15-2012, 02:39 PM

Hi

Take a look to the example.

**docrobo** · 03-15-2012, 02:53 PM

Hi again and thank you both for your replies. I am going to complete an attachment for you to peruse and to comment on. I will post it soon.

**docrobo** · 03-17-2012, 03:02 PM

Hi again, after various variations I have at last got the correct formula which is =((CP79+-CP77)/ABS(CP77)) May I thank both of you for your help, it is very much appreciated.