• 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

  • Complex interval.xls
    36 KB · Views: 8
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 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

  • Complex interval.xls
    47.5 KB · Views: 4
Hi Michel,

Just another option, you need to add helper columns:
 

Attachments

  • Complex interval.xls
    83 KB · Views: 0
There was a mistake in last file please find updated one.
 

Attachments

  • Complex interval.xls
    106 KB · Views: 1
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
 
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

  • Complex interval updated.xls
    36.5 KB · Views: 2
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,
 
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,
 
Back
Top