So I have done a few of these formulas in large tabular data sets for conversion into power pivot. I did this fine doing z-score using averageifs and my own stdev formula using ifs to check against names/data types as I wanted.
Anyway ... I wanted to also get the first difference of the set and ran into some trouble while trying to do dates in this way. Near as I can tell in evaluate formula it seems to work fine but gives off a 'false' despite having a single 'true' output.
I also broke it up so like
I added results for false and came up with month causing the 'false' every time while everything else had at least one true match. Even stranger is that it worked in the first cell I wrote it for but no other cells work.
Anyway ... I wanted to also get the first difference of the set and ran into some trouble while trying to do dates in this way. Near as I can tell in evaluate formula it seems to work fine but gives off a 'false' despite having a single 'true' output.
Code:
=E2/IF(C2=$C$2:$C$155378,IF(B2=$B$2:$B$155378,IF(DATE(YEAR($D$2:$D$155378),MONTH($D$2:$D$155378)+2,0)=D2,IF(ISNUMBER($E$2:$E$155378),$E$2:$E$155378))))-1
I also broke it up so like
Code:
if(year(d2)=12, if(year(d2)=year(datearray)+1,if(month(d2)=month(datearray)+1)