Could anyone help me with a formula that would compare Col B & Col C and
return the information in Col D?


Col B Col C Col D
950.91 950.91 0%
0 0 0
0 330.41 100%
1.96 11.05 463.8%

On Apr 9, 3:14 pm, JN wrote:
Could anyone help me with a formula that would compare Col B & Col C and
return the information in Col D?
Col B Col C Col D
950.91 950.91 0%
0 0 0
0 330.41 100%
1.96 11.05 463.8%

In D1 and copy down:

=if(B1=0, 100%, C1/B1 -1)

Format the cell as Percentage with the desired number of decimal
places.

Not everyone would agree that if B1 is zero, 100% is the right
answer. But that is what you asked for.

I'm sorry, I wasn't being very clear. I need 1 formula that would cover all
3 scenarios -

1) if Col B & C are equal, then return 0
2) if Col B = 0, then return 100% regardless of value of Col C
3) ABS value of Col B & Col C

"joeu2004" wrote:

On Apr 9, 3:14 pm, JN wrote:
Could anyone help me with a formula that would compare Col B & Col C and
return the information in Col D?
Col B Col C Col D
950.91 950.91 0%
0 0 0
0 330.41 100%
1.96 11.05 463.8%

In D1 and copy down:

=if(B1=0, 100%, C1/B1 -1)

Format the cell as Percentage with the desired number of decimal
places.

Not everyone would agree that if B1 is zero, 100% is the right
answer. But that is what you asked for.

This formula works fine until I run into 0 & then I get the #DIV/0! error:

=IF(B3=C3,"0% ",C3-B3)/ABS(B3)


"JN" wrote:

I'm sorry, I wasn't being very clear. I need 1 formula that would cover all
3 scenarios -

1) if Col B & C are equal, then return 0
2) if Col B = 0, then return 100% regardless of value of Col C
3) ABS value of Col B & Col C

"joeu2004" wrote:

On Apr 9, 3:14 pm, JN wrote:
Could anyone help me with a formula that would compare Col B & Col C and
return the information in Col D?
Col B Col C Col D
950.91 950.91 0%
0 0 0
0 330.41 100%
1.96 11.05 463.8%

In D1 and copy down:

=if(B1=0, 100%, C1/B1 -1)

Format the cell as Percentage with the desired number of decimal
places.

Not everyone would agree that if B1 is zero, 100% is the right
answer. But that is what you asked for.

On Apr 9, 4:38 pm, JN wrote:
I'm sorry, I wasn't being very clear. I need 1 formula that would cover all
3 scenarios -
1) if Col B & C are equal, then return 0

No, my mistake. Your examples were quite clear. I overlooked this
case. Try the following:

=if(B1=0, if(C1=0, 0%, 100%), C1/B1 - 1)

Again, format as Percentage with the desired number of decimal places.

3) ABS value of Col B & Col C

This requirement does not make sense to me; and it does not seem to
jibe with any of your examples. I wonder if it is simply an over-
specification.

Or are you trying to cover cases where either B or C, or where both B
and C, are negative?

If so, please provide examples with the percentage change that you
would like to see.

I was trying to calculate the difference between 2 #s as a percentage and was
using the above formula with the IF statement

HOWEVER you provided me with perfection and I'm very, very happy! Thank you
so much!

"joeu2004" wrote:

On Apr 9, 4:38 pm, JN wrote:
I'm sorry, I wasn't being very clear. I need 1 formula that would cover all
3 scenarios -
1) if Col B & C are equal, then return 0

No, my mistake. Your examples were quite clear. I overlooked this
case. Try the following:

=if(B1=0, if(C1=0, 0%, 100%), C1/B1 - 1)

Again, format as Percentage with the desired number of decimal places.

3) ABS value of Col B & Col C

This requirement does not make sense to me; and it does not seem to
jibe with any of your examples. I wonder if it is simply an over-
specification.

Or are you trying to cover cases where either B or C, or where both B
and C, are negative?

If so, please provide examples with the percentage change that you
would like to see.

On Apr 10, 7:22 am, JN wrote:
"joeu2004" wrote:
On Apr 9, 4:38 pm, JN wrote:
Try the following:
=if(B1=0, if(C1=0, 0%, 100%), C1/B1 - 1)

I was trying to calculate the difference between 2 #s as a percentage and was
using the above formula with the IF statement

HOWEVER you provided me with perfection and I'm very, very happy! Thank you
so much!

You're welcome! Glad to hear that works well for you. FYI, here is
another expression of the same solution. Use whichever one is easier
for you to use:

=if(B1=0, (C1<0)*100%, C1/B1 - 1)