Division % error

[marc747]

I am using this Formula (=SUM(A3-B3)/B3) to get the +/- % but every
time I have a 0 in either of the cells I get an error, how can I get
the correct calculation without errors.
Thanks

 

[Peo Sjoblom]

=IF(B3=0,0,(A3-B3)/B3)

no need to use sum here

--


Regards,


Peo Sjoblom

 

[Earl Kiosterud]

First of all, you didn't say what error you get, but it's likely a #DIV/0! error when B3 is
zero. Secondly, you don't really need the SUM() function. Your formula could have been
=(A3-B3)/B3. Try this:

=IF(B3<>0,(A3-B3)/B3,"eh?")

Replace "eh?" with whatever you want to see when B3 is zero. It could be 0, "" (for what
looks like nothing), etc.

 

[marc747]

Thanks, Can you look below and see how I can Formulate so that I can
get the correct calaulation, I have a minus in cell "B3" and when I
and trying to get a percentage change I am getting "-1909.50% this has
to be a + number because first it is "-210" and then from "-210" we
have "3799.95 so the % has to be an increase.

A B C
3799.95 -210 = -1909.50% " =(A3-B3)/B3"

Thanks

 

[Earl Kiosterud]

=IF(B3<>0,(B3-A3)/B3,"eh?")

Eh?

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
Thanks, Can you look below and see how I can Formulate so that I can
get the correct calaulation, I have a minus in cell "B3" and when I
and trying to get a percentage change I am getting "-1909.50% this has
to be a + number because first it is "-210" and then from "-210" we
have "3799.95 so the % has to be an increase.

A B C
3799.95 -210 = -1909.50% " =(A3-B3)/B3"

Thanks

 

[joeu2004]

I have a minus in cell "B3" and when I and trying
to get a percentage change I am getting "-1909.50%

Are you familiar the phrase "garbage in, garbage out"?

Your computation is correct, but the result might be meaningless
because you might not be using the correct computation for the
situation.

Unfortunately, you are not being very clear about the situation. So
it is difficult to infer the best solution for you.

First, you need to understand that the formula (A3-B3)/B3 gives you
the percentage change from B3 to A3. For example, if something cost
$100 (B3) last year and it costs $150 (A3) this year, the percentage
change is 50%.

Is that really what you have in A3 and B3? Did the value start out at
-210, and it is now 3799.95(!)? I doubt it.

Conversely, if you want to compute the percentage change from A3 to
B3, you would compute (B3-A3)/A3. But I doubt that fits your
situation either.

Moreover, both formulas make sense only if the sign of A3 and B3 is
the same; that is, they are both positive, or they are both negative,
and the denominator is not zero.

My guess is: 3799.95 represents some value, and -210 represents the
change. That is, the value went from 3799.95 to 3589.95.

If that's the case, the formula that you want is simply =B3/A3,
formatted as Percentage. That will work when B3 is positive (increase
in value), too.

Hope that helps. If not, please specify what the numbers in A3 and B3
represent.


----- original posting -----

 

[marc747]

Thanks, I am thinking maybe this is the way excel calculates!!!!

But I would like to try and see maybe there is a way to do it
correctly. I am giving you an example of how my numbers are!

(A) $3799.95 is sales for June 2008
(B) $-210 is sales for June 2007 (in this case it was a return item)
So in this case I would like to know in June 2008 how many +/- % is
it.

Thanks.

 

[joeu2004]

Thanks, I am thinking maybe this is the way excel
calculates!!!!

Excel does not calculate any particular "way". You tell Excel how you
want something calculated, and Excel does it. Whether or not the
calculation makes sense is entirely up to you.

(A) $3799.95 is sales for June 2008
(B) $-210 is sales for June 2007 [....]
So in this case I would like to know in June 2008
how many +/- % is it.

Okay, you do indeed have the right idea. If the more-recent number
(2008) is in A3 and the older number (2007) is in B3, I think the
following formula will give you the result you desire:

=IF(B3=0, SIGN(A3), (A3-B3)/ABS(B3))

If the old number was zero, any positive change will be treated as
100%, and any negative change will be treated as -100%. This is an
arbitrary choice. There is no right answer.

Otherwise:

1. A change from -50 to 0 will be a 100% gain, and from -50 to 50 will
be a 200% change. Likewise for 50 to 100 and 50 to 150.

2. A change from 50 to 0 will be a 100% loss (100%), and from 50 to
-50 will be a 200% loss (-200%). Likewise for -50 to -100 and -50 to
-150.

In your case, a change from -210 to 3800 about a 1910% gain.

 

[joeu2004]

Errata....

If the old number was zero, any positive change will
be treated as 100%, and any negative change will be
treated as -100%. This is an arbitrary choice.
There is no right answer.

I mean there is no right __mathematical__ answer. I don't know about
GAAP in the US or your jurisdiction.


----- original posting ------

Thanks, I am thinking maybe this is the way excel
calculates!!!!

Excel does not calculate any particular "way".  You tell Excel how you
want something calculated, and Excel does it.  Whether or not the
calculation makes sense is entirely up to you.

(A) $3799.95 is sales for June 2008
(B) $-210 is sales for June 2007 [....]
So in this case I would like to know in June 2008
how many +/- % is it.

Okay, you do indeed have the right idea.  If the more-recent number
(2008) is in A3 and the older number (2007) is in B3, I think the
following formula will give you the result you desire:

=IF(B3=0, SIGN(A3), (A3-B3)/ABS(B3))

If the old number was zero, any positive change will be treated as
100%, and any negative change will be treated as -100%.  This is an
arbitrary choice.  There is no right answer.

Otherwise:

1. A change from -50 to 0 will be a 100% gain, and from -50 to 50 will
be a 200% change.  Likewise for 50 to 100 and 50 to 150.

2. A change from 50 to 0 will be a 100% loss (100%), and from 50 to
-50 will be a 200% loss (-200%).  Likewise for -50 to -100 and -50 to
-150.

In your case, a change from -210 to 3800 about a 1910% gain.

 

[marc747]

wow!!! impressed, works great.
can you tell me what the "SIGN" and the "ABS" do in this formula.

thanks,

Thanks, I am thinking maybe this is the way excel
calculates!!!!

Excel does not calculate any particular "way".  You tell Excel how you
want something calculated, and Excel does it.  Whether or not the
calculation makes sense is entirely up to you.

(A) $3799.95 is sales for June 2008
(B) $-210 is sales for June 2007 [....]
So in this case I would like to know in June 2008
how many +/- % is it.

Okay, you do indeed have the right idea.  If the more-recent number
(2008) is in A3 and the older number (2007) is in B3, I think the
following formula will give you the result you desire:

=IF(B3=0, SIGN(A3), (A3-B3)/ABS(B3))

If the old number was zero, any positive change will be treated as
100%, and any negative change will be treated as -100%.  This is an
arbitrary choice.  There is no right answer.

Otherwise:

1. A change from -50 to 0 will be a 100% gain, and from -50 to 50 will
be a 200% change.  Likewise for 50 to 100 and 50 to 150.

2. A change from 50 to 0 will be a 100% loss (100%), and from 50 to
-50 will be a 200% loss (-200%).  Likewise for -50 to -100 and -50 to
-150.

In your case, a change from -210 to 3800 about a 1910% gain.

 

[joeu2004]

can you tell me what the "SIGN" and the "ABS"
do in this formula.

SIGN() returns -1, 0 or 1 for negative, zero and positive numbers.
Note that 1 is 100%.

ABS() returns the magnitude or "absolute value" of a number; that is,
the number without its sign. For example, ABS(-100) is 100, and
ABS(100) is also 100.

The formula works the way we think of percentage change intuitively.
The gain or loss is determined by the direction of change. The
percentage is determined by the ratio of change over the magnitude of
the base value.

2. A change from 50 to 0 will be a 100% loss (100%),
and from 50 to -50 will be a 200% loss (-200%).

Obvious typo: I should have written "(-100%)".


----- original posting -----

wow!!!  impressed, works great.
can you tell me what the "SIGN" and the "ABS" do in this formula.

thanks,

On Jul 18, 9:50 pm, (e-mail address removed) wrote:

Excel does not calculate any particular "way".  You tell Excel how you
want something calculated, and Excel does it.  Whether or not the
calculation makes sense is entirely up to you.

(A) $3799.95 is sales for June 2008
(B) $-210 is sales for June 2007 [....]
So in this case I would like to know in June 2008
how many +/- % is it.

Okay, you do indeed have the right idea.  If the more-recent number
(2008) is in A3 and the older number (2007) is in B3, I think the
following formula will give you the result you desire:

=IF(B3=0, SIGN(A3), (A3-B3)/ABS(B3))

If the old number was zero, any positive change will be treated as
100%, and any negative change will be treated as -100%.  This is an
arbitrary choice.  There is no right answer.

1. A change from -50 to 0 will be a 100% gain, and from -50 to 50 will
be a 200% change.  Likewise for 50 to 100 and 50 to 150.

2. A change from 50 to 0 will be a 100% loss (100%), and from 50 to
-50 will be a 200% loss (-200%).  Likewise for -50 to -100 and -50 to
-150.

In your case, a change from -210 to 3800 about a 1910% gain.- Hide quoted text -

- Show quoted text -