Displaying a % as a number
 

Hi, First post. I have 2009 sales data in column O and 2008 sales data column
P and the the following formula in column Q = O1/P1 as have formatted it as a
% to show an increase or decease

When the sales data in column o is 0 and p is 0 the result is #DIV/0!
When the sales data in column o is 0 and p is 0 the result is a %

I want the workbook to display a % when column o is 0

If both 0 & P are 0 i want a 0 to be displayed

I appreciate you help

Thanks
Michael

Displaying a % as a number
 

Handle the error using ISERROR()

=IF(ISERROR(O1/P1),0,O1/P1)

If this post helps click Yes
---------------
Jacob Skaria


"michael walker" wrote:

Hi, First post. I have 2009 sales data in column O and 2008 sales data column
P and the the following formula in column Q = O1/P1 as have formatted it as a
% to show an increase or decease

When the sales data in column o is 0 and p is 0 the result is #DIV/0!
When the sales data in column o is 0 and p is 0 the result is a %

I want the workbook to display a % when column o is 0

If both 0 & P are 0 i want a 0 to be displayed

I appreciate you help

Thanks
Michael

Displaying a % as a number
 

=IF(AND(O1=0,P1=0),0,IF(O10,O1/P1,""))

and format accordingly

On 21 Maj, 09:07, michael walker
wrote:
Hi, First post. I have 2009 sales data in column O and 2008 sales data column
P and the the following formula in column Q = O1/P1 as have formatted it as a
% to show an increase or decease

When the sales data in column o is 0 and p is 0 the result is #DIV/0!
When the sales data in column o is 0 and p is 0 the result is a %

I want the workbook to display a % when column o is 0

If both 0 & P are 0 i want a 0 to be displayed

I appreciate you help *

Thanks
Michael

Displaying a % as a number
 

To avoid % sign for zeros..Use the same formula with ISERROR and custom
format the cell to

0.00%;-0.00%;0

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Handle the error using ISERROR()

=IF(ISERROR(O1/P1),0,O1/P1)

If this post helps click Yes
---------------
Jacob Skaria


"michael walker" wrote:

Hi, First post. I have 2009 sales data in column O and 2008 sales data column
P and the the following formula in column Q = O1/P1 as have formatted it as a
% to show an increase or decease

When the sales data in column o is 0 and p is 0 the result is #DIV/0!
When the sales data in column o is 0 and p is 0 the result is a %

I want the workbook to display a % when column o is 0

If both 0 & P are 0 i want a 0 to be displayed

I appreciate you help

Thanks
Michael

Displaying a % as a number
 

Thanks, but I have found when I enter amount in column o i still get the
#div?0! message.

eg

--
Thanks
Michael


"Jarek Kujawa" wrote:

=IF(AND(O1=0,P1=0),0,IF(O10,O1/P1,""))

and format accordingly

On 21 Maj, 09:07, michael walker
wrote:
Hi, First post. I have 2009 sales data in column O and 2008 sales data column
P and the the following formula in column Q = O1/P1 as have formatted it as a
% to show an increase or decease

When the sales data in column o is 0 and p is 0 the result is #DIV/0!
When the sales data in column o is 0 and p is 0 the result is a %

I want the workbook to display a % when column o is 0

If both 0 & P are 0 i want a 0 to be displayed

I appreciate you help

Thanks
Michael

Displaying a % as a number
 

=IF(AND(O1=0,P1=0),0,IF(P1<0,IF(O10,O1/P1,"")),"percentage change is
infinite")

You will realise, I hope, that if P1 and O1 are 2008 and 2009 sales data,
=O1/P1 does *not* show the percentage increase or decrease. If you want the
percentage increase or decrease you need =O1/P1-1.
--
David Biddulph

michael walker wrote:
Thanks, but I have found when I enter amount in column o i still get
the #div?0! message.

eg


=IF(AND(O1=0,P1=0),0,IF(O10,O1/P1,""))

and format accordingly

On 21 Maj, 09:07, michael walker
wrote:
Hi, First post. I have 2009 sales data in column O and 2008 sales
data column P and the the following formula in column Q = O1/P1 as
have formatted it as a % to show an increase or decease

When the sales data in column o is 0 and p is 0 the result is
#DIV/0!
When the sales data in column o is 0 and p is 0 the result is a %

I want the workbook to display a % when column o is 0

If both 0 & P are 0 i want a 0 to be displayed

I appreciate you help

Thanks
Michael