Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying date as a real number | Excel Discussion (Misc queries) | |||
Displaying a custom formatted number | Excel Discussion (Misc queries) | |||
Displaying ratio as number of times | Excel Worksheet Functions | |||
Displaying Last Number that is in a cell | Excel Discussion (Misc queries) | |||
Displaying decimals that go in to a given number | Excel Discussion (Misc queries) |