jeudi 11 juillet 2019

Excel: Slope/Intercept function with an if condition applied to the X Axis

I'm working on a backend file for another program that, unfortunately, cannot have a graph in it. That said, I need to generate y axis values for 3 trendlines based on an existing set of data (ignoring zero-values on the Y axis.) All done with an array formula. Historical was simple and straightforward.

For the second, I'm trying to limit B's values (date range) to only 2019 dates. I've tried if(year(B:B)=2019). However, this didnt work. To test the functionality, I tried the if(B:B<>0) and that worked fine.

The first was easy enough, worked fine:

=SLOPE(IF($D:$D<>0,$D:$D),$B:$B)*B2+INTERCEPT(IF($D:$D<>0,$D:$D),$B:$B)

Any ideas on applying the if(year function to the X axis on both slope and intercept?

Aucun commentaire:

Enregistrer un commentaire