Increase percentage each day between a date range

[asmyth]

Hi,

I've been scouring the internet to find a solution to my problem but I haven't found one yet.
I'm looking to increase a cell by 4% each day between a set date range (workdays would be preferable). I've tried formulas using TODAY() and NETWORKDAYS, but I can't seem to get my head around how to increment the percentage each day.

Can this be done?

Thanks,
Adam

 

[Rhino_Dance]

If C3 is the old date and C4 is the new date and F3 holds the original number you want incremented, then type this into your target cell:

=F3*((1.04)^(C4-C3))

Hope this helps.

 

[asmyth]

If C3 is the old date and C4 is the new date and F3 holds the original number you want incremented, then type this into your target cell:

=F3*((1.04)^(C4-C3))

Hope this helps.

I'm not using cells with date values. I'm trying to do the calculation within the same formula. I was playing round with the formula and now I'm thinking it would be easier to multiple the number of workdays by 0.04 using TODAY(), but obviously the percentage increasing as the number of days get smaller. An inverted version of this:

=0.04*NETWORKDAYS(TODAY(),5/8/2015)

 

[Rhino_Dance]

Are you trying to do an exponential formula so that the value increases by 4% each day? (This balloons the value rather quickly.) Or are you calculating the 4% and then increasing the value linearly by that same amount each day? E.g. If the original value is 100 then 4% = 4 and you would simply add 4 each day.

I'm not sure where you are sourcing the original value to be incremented. If that value is in F3 then the formula for exponential expansion would be =F3*((1.04)^("5/8/15"-(TODAY())))

If you are simply doing a linear expansion it would be F3 + ((F3 *.04) *("5/8/15"-(TODAY())))

 

[asmyth]

How about this? I'm firstly determining if today's date falls between my date range, if it does then i'm dividing the number of days by 100 to get the percentage based on how days are left. (I stated 4% earlier because that is the correct p/day percentage in my actual date range)

=IF(TODAY()=NETWORKDAYS(TODAY(),2/1/2015),100/NETWORKDAYS(TODAY(),2/1/2015),"")

I'm getting no errors but getting a blank which means it has determined it's false.

 

[Rhino_Dance]

I'm not clear on what the exact aim is. I don't know what you are starting with and what you want as output.

 

[asmyth]

I have a cell that calculates the actual progress of my work in a percentage, and this field calculates where my target percentage should be based on how many days I have left. I've made it easier now with adding in a column to calculate the number of days in a range I've referenced in another tab.
C2=NETWORKDAYS('Release Timeline'!C7,'Release Timeline'!D7)

I now need to update C2 to take TODAY into account so C2 decreases with everyday.
Then I need to calculate a percentage based off of C2. Is it simple 100/C2?

[TABLE="width: 300, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Actual %[/TD]
[TD]Target %[/TD]
[TD]Execution Days[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]35[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rhino_Dance]

You need to store the original numbers of days you had to do the task. Let's say it's 40. Then if your time calculation indicates you have 13 days left then the target pace would be 40-13//40= .675 or 67.5%. So if you have only done 55% of the work then you are 12.5% behind.