mardi 14 novembre 2017

Why does an Excel Table function with IF filter with AND condition not return results

I have an Excel book here where I have imported data from CSV, aggregated in a Pivot table. I need to calculate Percentiles on the aggregation, and the function

{=PERCENTILE.INC(IF(MyTable[label]=$A3,MyTable[elapsed]),$C$1)} 

did return a result.($A3 is the pivot table row label, $C$1` contains 0.9, = 90%) I tried to extend the filtering IF statement with another condition using AND but suddenly the Percentile output dropped to 0. For debug reasons I simplified my condition down to the following:

{=PERCENTILE.INC(IF(AND(MyTable[label]=$A3, TRUE()),MyTable[elapsed]),$C$1)}

The expectation was that the formula result should be identical to the one above but with that AND statement it returns 0. Why?

Aucun commentaire:

Enregistrer un commentaire