incremental percentage reduction

[Sirod]

Hello all,

I'm looking to create a formula that will decrease a percentage applied each year by a 2.5%. For example, to start or year 0, 50% is applied. In Year 1 this is reduced 2.5% to 47.5%, in year 2 there is a further reduction of 2.5% to 45%. This continues until the percentage applied reaches 25% (for 2.5% it happens to be 10 years, but at different percentages the number of years to reach 25% will change). Rather than manually type in the percentage applied for each year, I'd like to create a formula that will calculate the percentage for me.

Below is a snap shot of what my table needs to look like.

[TABLE="width: 572"]
<colgroup><col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;" span="10"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <tbody>[TR]
[TD="class: xl67, width: 84, bgcolor: transparent"]Year:[/TD]
[TD="class: xl68, width: 61, bgcolor: transparent"] YEAR 0 [/TD]
[TD="class: xl68, width: 61, bgcolor: transparent"] YEAR 1 [/TD]
[TD="class: xl68, width: 61, bgcolor: transparent"] YEAR 2 [/TD]
[TD="class: xl68, width: 61, bgcolor: transparent"] YEAR 3 [/TD]
[TD="class: xl68, width: 61, bgcolor: transparent"] YEAR 4 [/TD]
[TD="class: xl68, width: 61, bgcolor: transparent"] YEAR 5 [/TD]
[TD="class: xl68, width: 61, bgcolor: transparent"] YEAR 6 [/TD]
[TD="class: xl68, width: 61, bgcolor: transparent"] YEAR 7 [/TD]
[TD="class: xl68, width: 61, bgcolor: transparent"] YEAR 8 [/TD]
[TD="class: xl68, width: 61, bgcolor: transparent"] YEAR 9 [/TD]
[TD="class: xl68, width: 65, bgcolor: transparent"] YEAR 10 [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Percentage:[/TD]
[TD="class: xl69, width: 61, bgcolor: transparent"]50.00%[/TD]
[TD="class: xl69, width: 61, bgcolor: transparent"]47.50%[/TD]
[TD="class: xl69, width: 61, bgcolor: transparent"]45.00%[/TD]
[TD="class: xl69, width: 61, bgcolor: transparent"]42.50%[/TD]
[TD="class: xl69, width: 61, bgcolor: transparent"]40.00%[/TD]
[TD="class: xl69, width: 61, bgcolor: transparent"]37.50%[/TD]
[TD="class: xl69, width: 61, bgcolor: transparent"]35.00%[/TD]
[TD="class: xl69, width: 61, bgcolor: transparent"]32.50%[/TD]
[TD="class: xl69, width: 61, bgcolor: transparent"]30.00%[/TD]
[TD="class: xl69, width: 61, bgcolor: transparent"]27.50%[/TD]
[TD="class: xl69, width: 65, bgcolor: transparent"]25.00%[/TD]
[/TR]
</tbody>[/TABLE]

Many thanks!

 

[hotabae]

Here's What I've Got:


[TABLE="width: 680"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Year[/TD]
[TD]Year 0[/TD]
[TD]Year 1[/TD]
[TD]Year 2[/TD]
[TD]Year 3[/TD]
[TD]Year 4[/TD]
[TD]Year 5[/TD]
[TD]Year 6[/TD]
[TD]Year 7[/TD]
[TD]Year 8[/TD]
[TD]Year 9[/TD]
[TD]Year 10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Percentage[/TD]
[TD]50.00%[/TD]
[TD]47.50%[/TD]
[TD]45.00%[/TD]
[TD]42.50%[/TD]
[TD]40.00%[/TD]
[TD]37.50%[/TD]
[TD]35.00%[/TD]
[TD]32.50%[/TD]
[TD]30.00%[/TD]
[TD]27.50%[/TD]
[TD]25.00%[/TD]
[/TR]
</tbody>[/TABLE]

All of my values are formatted as percentages with 2 decimal points.

The formula in C2 is

=IF(IF(B2>0.25,B2-0.025,B2)<0.25,0.25,IF(B2>0.25,B2-0.025,B2))

Then I applied that formula out through Year 30 (The max required for starting values 100% and below)

 

[dreid1011]

Perhaps this:

Assuming Year 0 is Column B, then in Column C and copy to the right, also assuming you manually enter the % for Year 0:

=IF(B2<>"",MAX(B2-0.025,0.25),"")

 

[hotabae]

I like this one better

=IF(B2<>"",MAX(B2-0.025,0.25),"")

Edit: (to clarify) : better than my own

 

[Sirod]

This worked perfectly. Thank you.

 

[dreid1011]

You're welcome.