Excel 2007 : Adds values for a specific time frame

[Raist23]

Hello,

I am having problems trying to figure out a formula that has a time delay based on an entry, and adds values for only 12 months after that.

If someone in our organization is hired in Jan, we have a savings for that person that comes into play starting 6 months later so in July. On the same line item we will have 1 person hired in Feb, so that savings starts in Aug. The savings only runs for 12 months for each person hired.

So if I assume a 100.00 savings per person and I hire 1 person in Jan 2012 and 1 person in Feb 2012, my outcome should show 0 savings from Jan 2012 thru June 2012, a 100.00 savings in July 2012, a 200.00 savings from Aug 2012 thru June 2013, a 100.00 savings in July 2013, and 0 savings from Aug 2013 onwards.

I hope this makes sense and someone can help me.

[AndyPS]

I think this attached worksheet could satisfy your requirement. It looks quite complex at first because the formula needs to allow for a varying range length as the calculation moves to later months. Also, I have used a range to define the way the savings are achieved over time. This is flexible and you could easily have a different value in each month (perhaps ramping up and then declining rather than a sharp cut-off).

The OFFSET() function is used to control the range length. Whilst this provides flexibility, it is at a price of (a) complexity and (b) calculation speed. The latter is because Excel treats OFFSET as a volatile function and recalculates it even is the data it depends on has not altered.

The method I have used here is based on one I often apply to situations where, for example, income in a month depends both on the value billed in previous months and a payment profile.

Hopefully this helps.

[Raist23]

thank you so much for this. it helps a lot and I see other areas to use this as well

[AndyPS]

Pleased to having been of assistance.