percentage greater or less than

[bbenaway]

Am needing a formula that will show weather one sum (column A)is greater than or less than the other sum (column B)by percentage. This value should show up in column C. I will then use conditional formating to colorize the numbers.

A B C
100 50 -50%

100 150 +50%

Again, thanks in advance, all of you have been life savers.

 

[mintjulep]

Seriously?

 

[BFOJ]

Using Excel: assumes data in A2 and B2, formula in cell C2

=IF(A2-B2,A2-B2,-A2+B2)

p.s. weather NE whether

 

[drawoh]

bbenaway,

=$A1>=$B1

This gives you a true/false logic value you can use for conditional formatting. The >= operator might be =>. The equation above works for me in Open Office.

This is pretty basic Excel. Try reading the help files.

JHG

 

[GlennUK]

Do not use BFOJ's formula ( no offence BFOJ, you must have misread the question ). To get a percentage change use this:

=(B2-A2)/A2

formatted as percentage.

But what if you had an original number of zero, which changes to be non-zero? What answer would you expect? A department that I used to work for had a policy of using 999% or -999% for changes from 0 to non-zero ( depending on sign ), and a maximum reported change of 999%, no matter what the calculation was ( either sign ). For that we used:

=IF(A2=0,SIGN(B2)*9.99,SIGN((B2-A2)/A2)*MIN(ABS((B2-A2)/A2),9.99))

... again formatted as percentage.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[BFOJ]

Thanks GlennUK, I did overlook the percentage requirement. My formula was for the difference being negative or positive. Thanks for pointing this out.

 

[BFOJ]

GlennUK,

How about =(B2-A2)/A2*100? Then bbenaway would get the exact results he wanted, then he could do the formatting.

 

[GlennUK]

BFOJ,

look at my first formula. It's the same as that, but without the "*100", as formatting as percentage takes care of that part.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[mintjulep]

Do you want something that does what you ask for in the text of your question, or something that duplicates your example? They are not the same.