Percentage change day-on-day formula

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
65
Office Version
  1. 365
Platform
  1. Windows
Hi everyone

I'm having a bit of a brain fry day today. I want to calculate the day-on-day percentage increase/decrease (column C) of the number of activities as shown in column B. Of course I am hitting an issue when it comes to days where there has been zero % change - this is especially true of weekends, where it is very likely that there will be no activities. This is obviously causing me an issue with the formula I have used because you can't divide by 0. Can anyone help me out with a formula/workaround that will enable me to capture the percentage increase / decrease in activity day on day please? I'm using Excel 365. Many thanks in advance.


Operations_MI_Als_team.xlsx
ABC
4DateActivities recorded
5UnitCount (actual)%age change DoD
6
7SourceDaily MI
827/10/20201740
928/10/202019713.2
1029/10/202032062.4
1130/10/2020163-49.1
1231/10/20200-100.0
1301/11/20200#DIV/0!
1402/11/2020346#DIV/0!
1503/11/202042322.3
1604/11/202077182.3
1705/11/2020413-46.4
1806/11/202066260.3
1907/11/20201-99.8
2008/11/20200-100.0
All data - CEM (19Oct>31Dec)
Cell Formulas
RangeFormula
C9:C18,C20C9=(B9-B8)/B8*100
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Book1
ABC
7SourceDaily MI
827-10-20201740,0
928-10-202019713,2
1029-10-202032062,4
1130-10-2020163-49,1
1231-10-20200-100,0
131-11-202000,0
142-11-2020346346,0
153-11-202042322,3
164-11-202077182,3
175-11-2020413-46,4
186-11-202066260,3
197-11-20201-99,8
208-11-20200-100,0
Sheet1
Cell Formulas
RangeFormula
C9:C20C9=IF(B8=0, B9, (B9-B8)/B8*100)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C9:C20Cell Value<0textNO
 

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
65
Office Version
  1. 365
Platform
  1. Windows
Thank you - that's wonderful
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,528
Messages
5,625,334
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top