Afternoon all and hope I find you well.
I have created a working sheet, but the management and user-friendliness are around 1 out of 10.
I am usually able to get cells to do what I need, but not in a space saving, tidy way and wondered if there is a simple solution to what I try to update quarterly.
---
Column A : A list of activities to do i.e. skim wall, paint wall, fit skirting etc...
Column B&C : Start date and End date
Column D : Networking days (to omit recognised holidays)
Column E : The cost of the activity i.e. skim wall may be £350
Column F : I place upfront costs i.e. plaster for £50
Bit of a jump now.
I have merged each activity cell into 1 cell from 4, so Skim wall referenced is A21:24.
Row 21 : This is the difference between start and end date (B-C)
Row 22 : This returns the start date of the whole project - Activity End date (Set date - C)
Row 23 : =IF(SUM(M21:M22)>-1,IF(M22<=0,1,0),0) Conditional Format to show the cell in Red if returns 1. This creates a bar chart to show The start date and end date.
Row 24: =IF(M15=1,IF($C13=M$5,$I13,($H13-$I13)/($F13-$D13)),0)
This places Column F's figure on the start date and then splits the remaining cost across the rest of the bar chart.
Ie The plaster was £350 across 11 days, so day 1 has £50 in and then £30 is spread across the remaining cells.
If it was 21 days it would be £50 and £15 spread.
This is cumbersome and hard to manage and in addition it is more realistic to have an S-Curve for the spend rather than an equal amount, so rather than the £30 across 10 days it would be more realistic to be 5, 5, 8, 10.... 7, 3, 1.
This is incredibly long winded for which I apologise, and would welcome guidance to a function to save time and how to have the S-Curve linked between 2 dates that move each quarter.
Thanking in advance for your time in reading and all assistance.
Roj
I have created a working sheet, but the management and user-friendliness are around 1 out of 10.
I am usually able to get cells to do what I need, but not in a space saving, tidy way and wondered if there is a simple solution to what I try to update quarterly.
---
Column A : A list of activities to do i.e. skim wall, paint wall, fit skirting etc...
Column B&C : Start date and End date
Column D : Networking days (to omit recognised holidays)
Column E : The cost of the activity i.e. skim wall may be £350
Column F : I place upfront costs i.e. plaster for £50
Bit of a jump now.
I have merged each activity cell into 1 cell from 4, so Skim wall referenced is A21:24.
Row 21 : This is the difference between start and end date (B-C)
Row 22 : This returns the start date of the whole project - Activity End date (Set date - C)
Row 23 : =IF(SUM(M21:M22)>-1,IF(M22<=0,1,0),0) Conditional Format to show the cell in Red if returns 1. This creates a bar chart to show The start date and end date.
Row 24: =IF(M15=1,IF($C13=M$5,$I13,($H13-$I13)/($F13-$D13)),0)
This places Column F's figure on the start date and then splits the remaining cost across the rest of the bar chart.
Ie The plaster was £350 across 11 days, so day 1 has £50 in and then £30 is spread across the remaining cells.
If it was 21 days it would be £50 and £15 spread.
This is cumbersome and hard to manage and in addition it is more realistic to have an S-Curve for the spend rather than an equal amount, so rather than the £30 across 10 days it would be more realistic to be 5, 5, 8, 10.... 7, 3, 1.
This is incredibly long winded for which I apologise, and would welcome guidance to a function to save time and how to have the S-Curve linked between 2 dates that move each quarter.
Thanking in advance for your time in reading and all assistance.
Roj
