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

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

no need to use sum here

--


Regards,


Peo Sjoblom

<marc747@excite.com> wrote in message
news:1b99c8b4-1906-4d94-a91d-900e4e8fbe87@v1g2000pra.googlegroups.com...
>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

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.

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
<marc747@excite.com> wrote in message
news:1b99c8b4-1906-4d94-a91d-900e4e8fbe87@v1g2000pra.googlegroups.com...
>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

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









On Jul 18, 2:51*pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> 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.
>
> --
> Regards from Virginia Beach,
>
> Earl Kiosterudwww.smokeylake.com
> -----------------------------------------------------------------------<marc...@excite.com> wrote in message
>
> news:1b99c8b4-1906-4d94-a91d-900e4e8fbe87@v1g2000pra.googlegroups.com...
>
>
>
> >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- Hide quoted text -
>
> - Show quoted text -

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

Eh?

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
<marc747@excite.com> wrote in message
news:962ed8d5-099c-4bf6-b5db-4f24ef3a41a3@u6g2000prc.googlegroups.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


On Jul 18, 2:51 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> 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.
>
> --
> Regards from Virginia Beach,
>
> Earl Kiosterudwww.smokeylake.com
> -----------------------------------------------------------------------<marc...@excite.com>
> wrote in message
>
> news:1b99c8b4-1906-4d94-a91d-900e4e8fbe87@v1g2000pra.googlegroups.com...

> >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- Hide quoted text -
>
> - Show quoted text -

On Jul 18, 5:28 pm, marc...@excite.com wrote:
> 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 -----

On Jul 18, 5:28*pm, marc...@excite.com wrote:
> 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
>
> On Jul 18, 2:51*pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
>
>
>
> > 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.
>
> > --
> > Regards from Virginia Beach,
>
> > Earl Kiosterudwww.smokeylake.com
> > -----------------------------------------------------------------------<mar*c...@excite.com> wrote in message
>
> >news:1b99c8b4-1906-4d94-a91d-900e4e8fbe87@v1g2000pra.googlegroups.com...
>
> > >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- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

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.







On Jul 18, 6:30*pm, joeu2004 <joeu2...@hotmail.com> wrote:
> On Jul 18, 5:28 pm, marc...@excite.com wrote:
>
> > 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 -----
>
> On Jul 18, 5:28*pm, marc...@excite.com wrote:
>
>
>
> > 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
>
> > On Jul 18, 2:51*pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
>
> > > 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.
>
> > > --
> > > Regards from Virginia Beach,
>
> > > Earl Kiosterudwww.smokeylake.com
> > > -----------------------------------------------------------------------<mar**c...@excite.com> wrote in message
>
> > >news:1b99c8b4-1906-4d94-a91d-900e4e8fbe87@v1g2000pra.googlegroups.com....
>
> > > >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- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

On Jul 18, 9:50*pm, marc...@excite.com wrote:
> 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.

Errata....

On Jul 18, 10:52 pm, I wrote:
> 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 ------

On Jul 18, 10:52*pm, joeu2004 <joeu2...@hotmail.com> wrote:
> On Jul 18, 9:50*pm, marc...@excite.com wrote:
>
> > 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.

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

thanks,





On Jul 18, 10:52*pm, joeu2004 <joeu2...@hotmail.com> wrote:
> On Jul 18, 9:50*pm, marc...@excite.com wrote:
>
> > 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.