If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Yearoveryear percentage of change
I am trying to find the percentage of change in customers. This wouldn't be a
problem with the formula =IF(C35=0,D35,(D35C35)/C35). However, the problem arises when one of the cells is 0. So in this case let's say D35 is 35 and C35 is 0. This returns a percent of increase of 3500%. Here is a snapshot of the data I am having problems with Thru 0908 2005 AVG 2006 AVG 2007 AVG 2008 AVG (prod introduced) 0 0 35 36 I am then using all of the percentages of change found to determine and average change for the 4 years. The 3500% throughs the calcuation off. Any suggestions? 
Ads 
#2




Yearoveryear percentage of change
=IF(C35=0,"",(D35C35)/C35)
This leaves the cell blank. If you later use the =AVERAGE() function, you will find that AVERAGE ignores these blanks  Gary''s Student  gsnu200810 "lstreet" wrote: > I am trying to find the percentage of change in customers. This wouldn't be a > problem with the formula =IF(C35=0,D35,(D35C35)/C35). However, the problem > arises when one of the cells is 0. So in this case let's say D35 is 35 and > C35 is 0. This returns a percent of increase of 3500%. Here is a snapshot of > the data I am having problems with > > Thru 0908 > 2005 AVG 2006 AVG 2007 AVG 2008 AVG > (prod introduced) > 0 0 35 36 > > I am then using all of the percentages of change found to determine and > average change for the 4 years. The 3500% throughs the calcuation off. > > Any suggestions? > > 
#3




Yearoveryear percentage of change
Nice, this seeems to work nicely. One more thing however is that there are
times that it returns the dreaded #DIV/0!. How can I avoid this? "Gary''s Student" wrote: > =IF(C35=0,"",(D35C35)/C35) > > This leaves the cell blank. If you later use the =AVERAGE() function, you > will find that AVERAGE ignores these blanks >  > Gary''s Student  gsnu200810 > > > "lstreet" wrote: > > > I am trying to find the percentage of change in customers. This wouldn't be a > > problem with the formula =IF(C35=0,D35,(D35C35)/C35). However, the problem > > arises when one of the cells is 0. So in this case let's say D35 is 35 and > > C35 is 0. This returns a percent of increase of 3500%. Here is a snapshot of > > the data I am having problems with > > > > Thru 0908 > > 2005 AVG 2006 AVG 2007 AVG 2008 AVG > > (prod introduced) > > 0 0 35 36 > > > > I am then using all of the percentages of change found to determine and > > average change for the 4 years. The 3500% throughs the calcuation off. > > > > Any suggestions? > > > > 
#4




Yearoveryear percentage of change
Can't see any way that this formula could return that error. You are dividing
by C35 only if C35 IS NOT equal to zero. lstreet wrote: > Nice, this seeems to work nicely. One more thing however is that there are > times that it returns the dreaded #DIV/0!. How can I avoid this? > > "Gary''s Student" wrote: > >> =IF(C35=0,"",(D35C35)/C35) >> >> This leaves the cell blank. If you later use the =AVERAGE() function, you >> will find that AVERAGE ignores these blanks >>  >> Gary''s Student  gsnu200810 >> >> >> "lstreet" wrote: >> >>> I am trying to find the percentage of change in customers. This wouldn't be a >>> problem with the formula =IF(C35=0,D35,(D35C35)/C35). However, the problem >>> arises when one of the cells is 0. So in this case let's say D35 is 35 and >>> C35 is 0. This returns a percent of increase of 3500%. Here is a snapshot of >>> the data I am having problems with >>> >>> Thru 0908 >>> 2005 AVG 2006 AVG 2007 AVG 2008 AVG >>> (prod introduced) >>> 0 0 35 36 >>> >>> I am then using all of the percentages of change found to determine and >>> average change for the 4 years. The 3500% throughs the calcuation off. >>> >>> Any suggestions? >>> >>> 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Formula to Calculate Compounded Percentage Per Year  Dermot  Excel Discussion (Misc queries)  16  April 24th 08 10:39 PM 
need formula 4 %change on both neg and pos movemnt year to year co  suesolotel  Excel Discussion (Misc queries)  4  January 13th 08 12:19 AM 
Combination Graph with current year and prior year sales  JanW  Charts and Charting in Excel  2  April 5th 07 09:20 PM 
How do I calculate year vs. year sales in a percentage?  johngalt  Excel Discussion (Misc queries)  2  January 10th 07 06:23 PM 
change the year in a calendar template to different year  George  Excel Discussion (Misc queries)  1  July 19th 06 07:34 PM 