# Really Complex Interval Problem

#### michaelbanks

##### New Member
Greeting to the Chandoo Universe.
Hi guys,

Please i need help writing a formula that applies the given rate (%) at the given intervals.

--First interval occurs on the 2nd year for 2 month period (in 2016 Jan-10%,feb-10%)

--Second inveral occurs on the 5th year (3 years after the first) for a 1 month period (in 2019 - jan 10%)

Also the intervals & duration are variable subject to change

I have attached a sample excel file to illustrate my challenge

I would really appreciate all help and inputs

#### Attachments

• 36 KB Views: 8

#### NARAYANK991

##### Excel Ninja
Hi ,

Can you clarify a few points ?

1. The data in E12 : H12 should preferably be just numbers to simplify matters. Can we assume that the interval will be the year , and the duration will be in months ?

2. If the duration is 1 will the month be January , if it is 2 , will it be January and February ,.... ?

In the example you have given , there seems to be some discrepancy :

Given the first interval is 2 , and the duration is 2 , and given the second interval is 5 and the duration is 1 , you have shown the 10 % entry in the following months :

January 2015 , January 2016 , January 2017 , January 2018 ,..., January 2024

What does this mean ?

Narayan

#### Somendra Misra

##### Excel Ninja
Hi MB,

See the file. Orange row has formula, note this is some hardcoded formula. If you range will increase say now you are saying 2 and 5 th year but later you decide 2,5,8,13,29 and so on...this formula will not be able to go beyond a point.

Regards,

#### Attachments

• 47.5 KB Views: 4

#### Faseeh

##### Excel Ninja
Hi Michel,

Just another option, you need to add helper columns:

#### Attachments

• 83 KB Views: 0

#### Faseeh

##### Excel Ninja
There was a mistake in last file please find updated one.

#### Attachments

• 106 KB Views: 1

#### michaelbanks

##### New Member
Hi ,

Can you clarify a few points ?

1. The data in E12 : H12 should preferably be just numbers to simplify matters. Can we assume that the interval will be the year , and the duration will be in months ?

2. If the duration is 1 will the month be January , if it is 2 , will it be January and February ,.... ?

In the example you have given , there seems to be some discrepancy :

Given the first interval is 2 , and the duration is 2 , and given the second interval is 5 and the duration is 1 , you have shown the 10 % entry in the following months :

January 2015 , January 2016 , January 2017 , January 2018 ,..., January 2024

What does this mean ?

Narayan
Hi Naryan,

the formula i had in there, was an old formula, please ignore it.

Your first and second assumptions are right...we can work with those.

Thanks

#### michaelbanks

##### New Member
Thanks Faseeh, i will try to implement it now and reply you with my results.

There was a mistake in last file please find updated one.

#### michaelbanks

##### New Member
Hi guys,

I manged to write a formula, that tries to incorporate the results i want.

1st problem - the intervals don't seem to be working correctly.

2nd problem - i haven't incorporated the Duration into the formula.

Thanks

#### Attachments

• 36.5 KB Views: 2

#### Attachments

• 117 KB Views: 2

#### michaelbanks

##### New Member
Hi MB,

See the file. Orange row has formula, note this is some hardcoded formula. If you range will increase say now you are saying 2 and 5 th year but later you decide 2,5,8,13,29 and so on...this formula will not be able to go beyond a point.

Regards,
Hi Misra,

with your formula is it possible to specify what month the rate will applied,

right now it applies in January. can it be modified to begin in July?

also if you could look at the updated version i posted today

#### Somendra Misra

##### Excel Ninja
Hi Misra,

with your formula is it possible to specify what month the rate will applied,

right now it applies in January. can it be modified to begin in July?

also if you could look at the updated version i posted today