Year-over-year 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,(D35-C35)/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? |
Year-over-year percentage of change
=IF(C35=0,"",(D35-C35)/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,(D35-C35)/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? |
Year-over-year 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,"",(D35-C35)/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,(D35-C35)/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? |
Year-over-year 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,"",(D35-C35)/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,(D35-C35)/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? |
All times are GMT +1. The time now is 11:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com