Need help with forecasting formula

[f_v]

Hi,

I need some help with a forecasting formula. In the attachment you can find my excel-file in which I already made some calculations.

There are 6 launch years and a timeline from 2013 until 2030.
For every year in the timeline, we want to know how many years do not have a launch year.
The counting starts from the first launch year (in example .2016)
You don't have a launch year for the years: 2019, 2021, 2022, 2023, 2025, 2026, 2027, 2028, 2029 and 2030.
So this should give the result: 2019 = 1, 2020 = 1, 2021 = 1+1 , ...

Row 25 with 'F5' is already a way in the good direction, but I want to count this years together.
Row 26 shows what I want to do in a final formula.

We have a deadline at sunday noon, so it would be a great help if someone could find a solution for this.
This is part of an other formula, so we are kind of stuck on it.

Thanks in advantage!

(And sorry for my bad English..)

Fien

[Ron Coderre]

Using your posted workbook...
This regular formula begins the count of no-launch years between the first launch year and the reference year (in Row_12)

Please Login or Register  to view this content.

Copy that formula across through T15

Is that something you can work with?

[f_v]

The output of the formula is correct indeed! Thank you.
But I need this formula into Qlikview and I don't have specific row numbers or ranges.
Functions as Indirect() and Row() doesn't exists into Qlikview, because it's based on dynamic data.

Still I can use your formula as basic.

Could you explain me your logic of how you became this formula?
Like why are you using the formula Row(Indirect($J$4&":"$MAX(C12,$J$4))) ?

This would help me a lot to build a formula without the Indirect() and Row() functions.

Thanks in advantage!

[Ron Coderre]

This section:
Row(Indirect($J$4&":"$MAX(C12,$J$4)))
defines the list of years to search for.
$J$4 is the first launch year (any previous are to be ignored)
This part:
$MAX(C12,$J$4)
creates the ending year to find...making sure that if the referenced year is less than the starting launch year, the starting lauch year will be used.

The entire phrase builds a range of rows from the starting year to the currently referenced year.
Example: "2008:2011" becomes the range of these rows 2008:2011
The ROW function creates the list...effectively ROW(2008:2011)...becomes:
2008
2009
2010
2011

I hope that helps.