Formula Help - Increasing values to reach target figure (including capping)

**Southy83** · 06-14-2012, 07:04 AM

I am currently trying to prepare some modelling data and am trying to find a formula for the following scenario:

I have attached a list of current figures which I need to gradually increase year on year to a target total.

Year 1

If Current + 15% (which is variable) is greater than target, then highest of current or target.
If Current + 15% is less than target, current + 15%

I have actually input this formula for Year 1 and get a total of 228,613. The issue I have is that I actually want 225,572 as the total for Year 1 as this is a phased modelling scenario.

Therefore, what formula can I use to get to my figure of 225,572 but ensure that the following rules are followed:

If figures currently stated for Year 1 are over the target figure, I want to reduce these items collectively by 3,041, BUT the revised Year 1 figure cannot fall below the target figure.

Can anyone provide an array formula which would resolve this, or I am stuck with the option of having a large number of additional columns to continually get to my end result?

Many thanks

**wayneg** · 06-14-2012, 07:25 AM

can i ask, the target doesnt for each year go incrementally . year 1 is 5.4911% greater that the default.... year 2 is 5.288% greater than yr 1 and year 3 is 5.4739% greater than year 2... how do you really need this

**Southy83** · 06-14-2012, 07:31 AM

Any of the targets could change, as I am currently modelling the data and need to build in a level of flexibility.

The key thing I am trying to get to is how I can get my total to match the specific years target, whilst following the rules in my penultimate paragraph.