Nested If functions with arrays

[cbjohn1]

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.

=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

if(year(d2)=12, if(year(d2)=year(datearray)+1,if(month(d2)=month(datearray)+1)

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.

 

[T. Valko]

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.


=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

if(year(d2)=12, if(year(d2)=year(datearray)+1,if(month(d2)=month(datearray)+1)

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.

Maybe you want something like this.

Array entered**:

=E2/INDEX($E$2:$E$155378,MATCH(1,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),1)))),0))-1

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.