date from interception line from card

[DB73]

Hi Guys,

long time no see

Got a question about a card
i've created a trending card.
the thing i want is that i need the date when the 2 line cross. (measurments and MAWT) trendingline
if possible on the card itsself on the crosspoint or maybe just in a cell next to my chard

if im not clear,,i apologise and will answer ur questions if needed.

thanks in advance

 

[RobP]

Hi, would this work for you ?

I am not sure if you will always have your dates in ascending order in your chart. This relies on picking the minimum (earliest therefore) date that the lines cross in your table, so needs them in order - which I think is usually the case, but just checking.

=MIN(FILTER(P2:P10,Q2:Q10<R2:R10,""))

 

[DB73]

Hi, would this work for you ?

I am not sure if you will always have your dates in ascending order in your chart. This relies on picking the minimum (earliest therefore) date that the lines cross in your table, so needs them in order - which I think is usually the case, but just checking.

=MIN(FILTER(P2:P10,Q2:Q10<R2:R10,""))

thanks for the help

year will be in ascending order, could be changed.

but as i used ur formula, u can see the date it gives does not match the crossing line in the card

 

[RobP]

ah sorry, I see you forecast on your chart, but dont actually do it in your data chart (that I can see). I assumed you were filling the data in col Q manually so we could use that as a reference point.

Only other idea I have is to use another column (say 'S') in which you can put your forecast data in :

Book1
	L	M	N	O	P	Q	R	S	T
1					year	measurement	MAWT
2					01/01/2000	10	7.15	10
3		01/01/2036			01/01/2024	8	7.15	8
4					01/01/2030		7.15	7.499886
5					01/01/2036		7.15	7
6					01/01/2042		7.15	6.499886
7					01/01/2048		7.15	6
8					01/01/2050		7.15	5.833219
9					01/01/2054		7.15	5.499886
10					01/01/2060		7.15	5
11
12
Sheet1

Cell Formulas
Range	Formula
M3	M3	=MIN(FILTER(P2:P10,S2:S10<R2:R10,""))
S2:S10	S2	=FORECAST(P2,Q$2:Q$10,P$2:P$10)

 

[DB73]

ah sorry, I see you forecast on your chart, but dont actually do it in your data chart (that I can see). I assumed you were filling the data in col Q manually so we could use that as a reference point.

Only other idea I have is to use another column (say 'S') in which you can put your forecast data in :

Book1
	L	M	N	O	P	Q	R	S	T
1					year	measurement	MAWT
2					01/01/2000	10	7.15	10
3		01/01/2036			01/01/2024	8	7.15	8
4					01/01/2030		7.15	7.499886
5					01/01/2036		7.15	7
6					01/01/2042		7.15	6.499886
7					01/01/2048		7.15	6
8					01/01/2050		7.15	5.833219
9					01/01/2054		7.15	5.499886
10					01/01/2060		7.15	5
11
12
Sheet1

Cell Formulas
Range	Formula
M3	M3	=MIN(FILTER(P2:P10,S2:S10<R2:R10,""))
S2:S10	S2	=FORECAST(P2,Q$2:Q$10,P$2:P$10)

i think i got it working...but the excel file is on my work laptop, i have 3 weeks vacation now and no acces anymore till then.
thank for the help anyway..ill check in 3 weeks..

 

[RobP]

No problem, good to hear it, glad to have helped.

cheers
Rob