I'm comparing results month on month but want to show it as a percentage - I
think that I'm overly confusing myself but I want to show whether it's a plus
or minus from the month before. It would also be lovely to show plus in
green and minus in red!

The formula you need is (this-month - last-month)/last-month as in
=(B1-A1)/A1
You could then use a custom format such as [Green]0%;[Red]-0% for the
colouring
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Bugalugs" wrote in message
...
I'm comparing results month on month but want to show it as a percentage -
I
think that I'm overly confusing myself but I want to show whether it's a
plus
or minus from the month before. It would also be lovely to show plus in
green and minus in red!

Thanks so much! Do I just tag the custom format onto the end of the % formula?

"Bernard Liengme" wrote:

The formula you need is (this-month - last-month)/last-month as in
=(B1-A1)/A1
You could then use a custom format such as [Green]0%;[Red]-0% for the
colouring
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Bugalugs" wrote in message
...
I'm comparing results month on month but want to show it as a percentage -
I
think that I'm overly confusing myself but I want to show whether it's a
plus
or minus from the month before. It would also be lovely to show plus in
green and minus in red!

No, you don't put it at the end of the formula, it's a "custom format", as
Bernard said. If you don't undestand custom formats, type the term "custom
format" into Excel help.
--
David Biddulph

"Bugalugs" wrote in message
...
Thanks so much! Do I just tag the custom format onto the end of the %
formula?

"Bernard Liengme" wrote:

The formula you need is (this-month - last-month)/last-month as in
=(B1-A1)/A1
You could then use a custom format such as [Green]0%;[Red]-0% for the
colouring
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Bugalugs" wrote in message
...
I'm comparing results month on month but want to show it as a
percentage -
I
think that I'm overly confusing myself but I want to show whether it's
a
plus
or minus from the month before. It would also be lovely to show plus
in
green and minus in red!

Fab - thanks so much for a prompt response David!

"David Biddulph" wrote:

No, you don't put it at the end of the formula, it's a "custom format", as
Bernard said. If you don't undestand custom formats, type the term "custom
format" into Excel help.
--
David Biddulph

"Bugalugs" wrote in message
...
Thanks so much! Do I just tag the custom format onto the end of the %
formula?

"Bernard Liengme" wrote:

The formula you need is (this-month - last-month)/last-month as in
=(B1-A1)/A1
You could then use a custom format such as [Green]0%;[Red]-0% for the
colouring
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Bugalugs" wrote in message
...
I'm comparing results month on month but want to show it as a
percentage -
I
think that I'm overly confusing myself but I want to show whether it's
a
plus
or minus from the month before. It would also be lovely to show plus
in
green and minus in red!

If your previous month's value is in A2 and the current month value is
in B2, use

=(B2-A2)/A2

Use the Percent format or, if you want the + sign for positive values,
use a custom number format as "+0%;-0%".

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Tue, 16 Dec 2008 09:28:04 -0800, Bugalugs
wrote:

I'm comparing results month on month but want to show it as a percentage - I
think that I'm overly confusing myself but I want to show whether it's a plus
or minus from the month before. It would also be lovely to show plus in
green and minus in red!

To calculate the percentage change between two months, you can use the following formula:

1. = (new value - old value) / old value

Let's say your old value is in cell A1 and your new value is in cell B1. You can enter the formula in cell C1 and then format the cell as a percentage. The result will be the percentage change between the two values.

To show the result as either a plus or minus, you can use conditional formatting. Here are the steps:

1. Select the cell with the percentage change (C1 in our example).
2. Go to the Home tab and click on Conditional Formatting.
3. Select "Highlight Cell Rules" and then "Less Than."
4. In the box next to "Less Than," enter 0 (zero) and select a red fill color.
5. Click "OK" to apply the formatting.
6. Repeat steps 3-5, but this time select "Greater Than" and enter 0 (zero) and select a green fill color.

Now, any negative percentage changes will be highlighted in red and any positive percentage changes will be highlighted in green.

I hope that helps!