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.

Year:	YEAR 0	YEAR 1	YEAR 2	YEAR 3	YEAR 4	YEAR 5	YEAR 6	YEAR 7	YEAR 8	YEAR 9	YEAR 10
Percentage:	50.00%	47.50%	45.00%	42.50%	40.00%	37.50%	35.00%	32.50%	30.00%	27.50%	25.00%

<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> </tbody>

Many thanks!

 

[hotabae]

Here's What I've Got:

A

B

C

D

E

F

G

H

I

J

K

L

1

Year

Year 0

Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

Year 7

Year 8

Year 9

Year 10

2

Percentage

50.00%

47.50%

45.00%

42.50%

40.00%

37.50%

35.00%

32.50%

30.00%

27.50%

25.00%

<tbody>
</tbody>

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.