J
Jana
I am trying to show the percentage difference between two numbers and also
show the overage in red....Help!
show the overage in red....Help!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
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.
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.
B
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?
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?
G
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
(formatted as a %) is the percent difference.
A positive value will mean a percent increase and a negative value will be a
percent decrease
J
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.
One more question though, the formula works unless 1 of the numbers is 0,
then I get a "#DIV/0!" in the cell.
J
Jana
Yes Positive or Negative
Bernard Liengme said: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?
B
Bernard Liengme
=IF(A1<>0,(B1-A1)/A1,NA())
M
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.
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.
M
mellaur
ur msg is true. however, using that formula, which i am, shows negative
percents as positives and vice versa. i need to know how to get them to show
properly and cannot find anything.
percents as positives and vice versa. i need to know how to get them to show
properly and cannot find anything.
J
joeu2004
ur msg is true. however, using that formula, which i am, shows negative
percents as positives and vice versa. i need to know how to get them to show
properly and cannot find anything.
If the two numbers are in A1 and B1, and you are currently computing
A1/B1 - 1 and you feel the results are wrong, try B1/A1 - 1.
Note: You might be computing (A1-B1)/B1 instead of A1/B1 - 1. They
are equivalent. Likewise, (B1-A1)/A1 is equivalent to B1/A1 - 1.
Both formulas are equally "proper". It all depends on your point of
view. The first formula (A1/B1 - 1) tells you the percentage change
from B1 to A1. The second formula (B1/A1 - 1) tells you the
percentage change from A1 to B1.