Simple calc help request

[The Wolf]

What calc to I use to calculate the percentage difference from two cells?

A1=1000 B1=1100

I want C1 to display +%10 and so forth either positive percent change or
negative.
tia

 

[Bob Greenblatt]

What calc to I use to calculate the percentage difference from two cells?

A1=1000 B1=1100

I want C1 to display +%10 and so forth either positive percent change or
negative.
tia

Wolf,

Try this in C1 =(B1-A1)/A1

Format the cell as a percentage.

 

[The Wolf]

Wolf,

Try this in C1 =(B1-A1)/A1

Format the cell as a percentage.

Worked like a charm, thanks! The parentheses was what I couldn't remember.

Is there a way to make a negative percent red and positive black?
--
==========================================================================
"When a broad table is to be made, and the edges of planks do not fit, the
artist takes a little from both, and makes a good joint. In like manner
here, both sides must part with some of their demands," Benjamin Franklin
(1706-1790)
==========================================================================

 

[JE McGimpsey]

Worked like a charm, thanks! The parentheses was what I couldn't remember.

Is there a way to make a negative percent red and positive black?

Couple of ways:

1) Custom format

Format/Cells/Number/Custom [Black]0.0%;[Red]0.0%;0;@

2) Conditional Format

Format/Conditional Formatting...

CF1: Cell value is less than 0
Format1: click Format and choose Red from the dropdown on the Font tab

Note: the first method produces a %age without the negative sign (add it
back in if you want). The second will keep whatever number format is
already in the cell, so if you have a negative sign and don't want it,
you'll have to use Custom Formatting to get rid of it (and then you
might as well use Custom Format from the beginning).


FWIW, you can calculate your percentage a little more efficiently
(though perhaps less transparently) as

=1-(B1/A1)

which only has to lookup the value in A1 once. Make sure you don't blink
during the millisecond you save (but if there's a lot of them, the
milliseconds add up).

 

[The Wolf]

Worked like a charm, thanks! The parentheses was what I couldn't remember.

Is there a way to make a negative percent red and positive black?

Couple of ways:

1) Custom format

Format/Cells/Number/Custom [Black]0.0%;[Red]0.0%;0;@

2) Conditional Format

Format/Conditional Formatting...

CF1: Cell value is less than 0
Format1: click Format and choose Red from the dropdown on the Font tab

Note: the first method produces a %age without the negative sign (add it
back in if you want). The second will keep whatever number format is
already in the cell, so if you have a negative sign and don't want it,
you'll have to use Custom Formatting to get rid of it (and then you
might as well use Custom Format from the beginning).


FWIW, you can calculate your percentage a little more efficiently
(though perhaps less transparently) as

=1-(B1/A1)

which only has to lookup the value in A1 once. Make sure you don't blink
during the millisecond you save (but if there's a lot of them, the
milliseconds add up).

Thank you!

 

[The Wolf]

Worked like a charm, thanks! The parentheses was what I couldn't remember.

Is there a way to make a negative percent red and positive black?

Couple of ways:

1) Custom format

Format/Cells/Number/Custom [Black]0.0%;[Red]0.0%;0;@

2) Conditional Format

Format/Conditional Formatting...

CF1: Cell value is less than 0
Format1: click Format and choose Red from the dropdown on the Font tab

Note: the first method produces a %age without the negative sign (add it
back in if you want). The second will keep whatever number format is
already in the cell, so if you have a negative sign and don't want it,
you'll have to use Custom Formatting to get rid of it (and then you
might as well use Custom Format from the beginning).


FWIW, you can calculate your percentage a little more efficiently
(though perhaps less transparently) as

=1-(B1/A1)

which only has to lookup the value in A1 once. Make sure you don't blink
during the millisecond you save (but if there's a lot of them, the
milliseconds add up).

The second calc returns slightly different results. %2.80 instead of %2.88

Also, is there a way that it will display blank cell if one of the cells is
blank. Right now it shows %100

 

[JE McGimpsey]

The second calc returns slightly different results. %2.80 instead of %2.88

Also, is there a way that it will display blank cell if one of the cells is
blank. Right now it shows %100

Hmm... =1-(B1/A1) and =(A1-B1)/A1 are mathematically equivalent, they
can't return the results you're describing. Are you sure that the
parentheses are correct?

If you have cell format set for 1 decimal place, you may get 2.9% vs.
2.88% for 2 decimal places - XL rounds the display to suit the number of
places. But it should never round down to 2.80%.

To display a blank cell:

=IF(COUNT(A1:B1)<2,"",1-(B1/A1))

To make sure you don't get a #DIV/0 error if A1 = 0:

=IF(AND(A1<>0,B1<>""),1-(B1/A1),"")

 

[The Wolf]

Hmm... =1-(B1/A1) and =(A1-B1)/A1 are mathematically equivalent, they
can't return the results you're describing. Are you sure that the
parentheses are correct?

If you have cell format set for 1 decimal place, you may get 2.9% vs.
2.88% for 2 decimal places - XL rounds the display to suit the number of
places. But it should never round down to 2.80%.

To display a blank cell:

=IF(COUNT(A1:B1)<2,"",1-(B1/A1))

To make sure you don't get a #DIV/0 error if A1 = 0:

=IF(AND(A1<>0,B1<>""),1-(B1/A1),"")

Thanks, I appreciate your reply.