• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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 :awesome:

Thanks in Advance.
 

Attachments

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

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
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.

Please see a revised version.

Thanks
 

Attachments

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,

Thanks for your input,

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

I await your response,
 

Somendra Misra

Excel Ninja
Hi Misra,

Thanks for your input,

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

I await your response,
In the formula:
=IF(YEAR(J7)-YEAR($J$7)=$E$12-1,IF(MONTH(J7)<=$F$12,$I$12,0),IF(YEAR(J7)-YEAR($J$7)=$G$12-1,IF(MONTH(J7)<=$H$12,$I$12,0),0))

The bold part is for months, you can change it to suit your need.

Regards,
 
Top