Excel 2007 : Formatting negative numbers

**littlemy111** · 11-10-2011, 02:15 PM

I am very new to using excel and am trying to create a spreadsheet to keep track of my Christmas spending.

I have a table with names, items bought, cost, cost last year, and difference between the 2 years.

I want to work out the difference between the cost this year and last year and so have used =IMSUB(C2,F2) which gives a result. If the result is negative then obviously that is shown using the negative sign. I would like to be able to show the positive sign as well though and for the life of me cannot work out how to do that.

I also want to format the cells so they show a £ but nothing I do in the formatting makes any difference to the way the results look.

Lastly I want to add up the results of the difference column but as some of the numbers are negative the results don't' appear to work.

Any help or suggestions greatly received!

Thanks

**arthurbr** · 11-10-2011, 04:00 PM

Hi and welcome to the board
I don't understand why you use IMSUB which is the difference between complex numbers??? I suspect your problems originate there

=C2-F2 or sthg similar would do

You can use custom format £+#,###;£-#,###

**scottylad2** · 11-10-2011, 05:07 PM

This years price in B2 and last years in A2 and ion C2 this formula dragges down =IF(B2-A2<=0,B2-A2,"+"&B2-A2) I went as far as C14 and to calculate the result try =SUMPRODUCT((VALUE(C2:C14)))

**littlemy111** · 11-10-2011, 05:28 PM

Thanks Arthurbr! Can't believe it was so simple! Worked a treat!

**scottylad2** · 11-10-2011, 05:34 PM

Edit - hadn't spotted the custom format

**arthurbr** · 11-11-2011, 04:48 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save