Excel: Slope berekenen mbv Xlookup en #N/A waarden

[BennyBr]

Beste Excellers,

Ik beheer enkele databanken waarvan de data vaak redelijk complex wordt.
Toch probeer ik zoveel mogelijk te automatiseren, wat tot hiertoe heel goed lukt.

In bijlage een voorbeeld waar ik struggle.
ALs voorbeeld geef ik 2 data sets.
Set 1 is een reeks data zonder enige probleem.
Set 2 is een reeks waar af en toe een #N/A staat.

De bedoeling is om de slope te berekenen tussen 2 ingevoerde datums mbv Xlookup (grote fan van Xlookup ).
Ik heb in de bijlage een zo goed mogelijke uiteenzetting geplaatst.
Set 1 berekent wat ik wens.
Set 2 geeft begrijpelijk #N/A

Mijn vragen:

Hoe kan ik de slope berekenen, terwijl waarden wel #N/A kunnen blijven?
(Ik weet dat #N/A vervangen met "" wel werkt, maar dan flippen heel veel andere berekeningen en grafieken)

Ik gebruik #N/A heel vaak bij onbeschikbare data. Is dit aangewezen, of is er een betere manier?

 

[route99]

Als je de fit line hebt... dan heb je toch ook de slope?
https://howtocreate.com/miscellaneous/faq-how-to-create-a-linear-regression-line-in-excel-19527/

How do you create a regression line?

The formula for the best-fitting line (or regression line) is y = mx + b, where m is the slope of the line and b is the y-intercept.

Dus je moet hem dan nog alleen effe in een aparte cel krijgen. Nog nooit gedaan, maar dat zal wel bekend zijn.

Edit.... dit werkt met LIJNSCH

=LIJNSCH(C4:C19;B4:B19;WAAR;WAAR)

 

[BennyBr]

Bedankt voor je reactie.
Je gaat een stap terug in de troubleshooting.
In de bijlage wordt de functie SLOPE() gebruikt waarin je dus 2 sets van data als variabelen moet ingeven.
Dus dat werkt.
Enkel wanneer sommige waarden #N/A zijn zit ik vast.

 

[route99]

Ai toch niet... met N/B.

 

[BennyBr]

Engelse versie, dus #N/A

 

[route99]

Werkt beiden hier...
Edit: het zou met LINEST wel moeten werken.
https://www.google.com/search?q=use+linest+skip+n/a+values
Ik doe vast iets verkeerd... als inlet zo ervaren "exceller"...heb bij een matrix netjes xtrl+shift+enter gebruikt...

 

[jkpieterse]

Ik zou in je brongegevens niets zetten in cellen waar geen meting is en in bereiken voor grafieken de lege cellen vervangen door NA().

 

[BennyBr]

Gevonden!

=SLOPE(XLOOKUP(H5;A2:A30;E2:E30;"-";1):XLOOKUP(H8;A2:A30;E2:E30;"-";1);XLOOKUP(H5;A2:A30;D2:D30;"-";1):XLOOKUP(H8;A2:A30;D2:D30;"-";1))

wordt

=SLOPE(IF(ISNUMBER(XLOOKUP(H5;A2:A30;E2:E30;"-";1):XLOOKUP(H8;A2:A30;E2:E30;"-";1));XLOOKUP(H5;A2:A30;E2:E30;"-";1):XLOOKUP(H8;A2:A30;E2:E30;"-";1));IF(ISNUMBER(XLOOKUP(H5;A2:A30;D2:D30;"-";1):XLOOKUP(H8;A2:A30;D2:D30;"-";1));XLOOKUP(H5;A2:A30;D2:D30;"-";1):XLOOKUP(H8;A2:A30;D2:D30;"-";1)))

Blijkbaar kan je dus de IF(ISNUMBER( -truuk ook toepassen op de XLOOKUP)

 

[route99]

:thumb: Mooi, had hem wel gezien maar kwam er omdat ik weg moest niet zo snel uit, ben niet zo'n ervaren Exceller. En om een 2e niet werkende optie aan te bieden... leek me niks...
Maar je kunt het zelf dus ook..... succes verder.

 

[AlexCEL]

Mag ik een wat korter alternatief voorstellen (Excel 365):

[NL] =LET(q;GEHEEL(A2:A30);r;FILTER(D2:E30;(q>=H5)*(q<=H8));x;ALS.FOUT(INDEX(r;;2);"");y;ALS.FOUT(INDEX(r;;1);"");RICHTING(x;y))
[EN] =LET(q;TRUNC(A2:A30);r;FILTER(D2:E30;(q>=H5)*(q<=H8));x;IFERROR(INDEX(r;;2);"");y;IFERROR(INDEX(r;;1);"");SLOPE(x;y))