# Microsoft Office Application Help - Excel Help forum > Excel General >  > [SOLVED] Show % Difference between two numbers

## Jana

I am trying to show the percentage difference between two numbers and also
show the overage in red....Help!

----------


## DaveO

Assuming cell A1 has your 1st value and B1 your 2nd.

Cell C1 should be =A1/B1

Then format that cell to be a % and it'll work.

For the average of the 2 number cell D1 should be =(A1+B1)/2

Then fomat font to br red.

HTH.

"Jana" wrote:

> I am trying to show the percentage difference between two numbers and also
> show the overage in red....Help!

----------


## Bernard Liengme

Numbers in A1 and B1
percentage difference =(B1-A1)/A1 or B1/A1 - 1
This is the change in B relative to A; for the change in A relative to B
just use (A1-B1)/B1
Percentage means different thing to different folks!

define 'overage'; do you mean the difference is positive or negative?

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jana" <Jana@discussions.microsoft.com> wrote in message
news:D3BFF4CE-1449-4B40-9FB9-A74F32C9D71C@microsoft.com...
>I am trying to show the percentage difference between two numbers and also
> show the overage in red....Help!

----------


## Gary''s Student

If the first value is in A1 and the second is in B1 then =(B1-A1)/A1
(formatted as a %) is the percent difference.

A positive value will mean a percent increase and a negative value will be a
percent decrease
--
Gary''s Student


"Jana" wrote:

> I am trying to show the percentage difference between two numbers and also
> show the overage in red....Help!

----------


## Jana

Thank you soooo much.

One more question though, the formula works unless 1 of the numbers is 0,
then I get a "#DIV/0!" in the cell.

"Gary''s Student" wrote:

> If the first value is in A1 and the second is in B1 then =(B1-A1)/A1
> (formatted as a %) is the percent difference.
>
> A positive value will mean a percent increase and a negative value will be a
> percent decrease
> --
> Gary''s Student
>
>
> "Jana" wrote:
>
> > I am trying to show the percentage difference between two numbers and also
> > show the overage in red....Help!

----------


## Jana

Yes Positive or Negative

"Bernard Liengme" wrote:

> Numbers in A1 and B1
> percentage difference =(B1-A1)/A1 or B1/A1 - 1
> This is the change in B relative to A; for the change in A relative to B
> just use (A1-B1)/B1
> Percentage means different thing to different folks!
>
> define 'overage'; do you mean the difference is positive or negative?
>
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Jana" <Jana@discussions.microsoft.com> wrote in message
> news:D3BFF4CE-1449-4B40-9FB9-A74F32C9D71C@microsoft.com...
> >I am trying to show the percentage difference between two numbers and also
> > show the overage in red....Help!
>
>
>

----------


## Bernard Liengme

=IF(A1<>0,(B1-A1)/A1,NA())


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jana" <Jana@discussions.microsoft.com> wrote in message
news:4CF180A5-7AC4-4473-A951-DA55E0A49ADC@microsoft.com...
> Thank you soooo much.
>
> One more question though, the formula works unless 1 of the numbers is 0,
> then I get a "#DIV/0!" in the cell.
>
> "Gary''s Student" wrote:
>
>> If the first value is in A1 and the second is in B1 then =(B1-A1)/A1
>> (formatted as a %) is the percent difference.
>>
>> A positive value will mean a percent increase and a negative value will
>> be a
>> percent decrease
>> --
>> Gary''s Student
>>
>>
>> "Jana" wrote:
>>
>> > I am trying to show the percentage difference between two numbers and
>> > also
>> > show the overage in red....Help!

----------


## mvl

Jana

I have to do this also...here's what I do....

I usually hilight one cell, then on the tool bar select Format, Conditional
Formatting, for Condition 1 select "Cell Value is" from the dropdown list,
select "less than", enter 1.  Click on Format, in the color selection, select
red.

Then I copy the cell, hilight all cells that applies and paste format.

Hope this helps.


"Jana" wrote:

> I am trying to show the percentage difference between two numbers and also
> show the overage in red....Help!

----------

