Aggregate Var.P

[lrobbo314]

I have been trying to get Var.P to work in an array formula and I haven't been able to figure it out. I tried using Sumproduct and kind of got it to work, but it was incorporating 0s for the non-matches and gave the wrong answer. So, I wanted to use aggregate to ignore errors. This is the formula I came up with.

=AGGREGATE(11,3,IF($A$2:$A$468=N2,ABS($F$2:$F$468),NA()))

So, the item I am looking up is in N2.
It looks for a match in column A.
If it matches, it gets the absolute value of the number in column F, else it puts #N/A into the array.

When I step through the formula, it looks like it should work, but it returns a #VALUE error.

Any ideas?

 

[AhoyNC]

Try this array formula that needs to be entered with CTRL-SHIFT-ENTER.

=VAR.P(IF($A$2:$A$468=N2,ABS($F$2:$F$468)))

 

[lrobbo314]

Perfect. Thanks. It's much better than the alternative that I finally got to work...

=VAR.P(ABS(OFFSET(INDIRECT("A" & MATCH(O2,$A$2:$A$468,0)+1 & ":A" &MAX(IF($A$2:$A$468=O2,ROW($A$2:$A$468)-ROW(INDEX($A$2:$A$468,1,1))+2))),0,5)))

 

[AhoyNC]

You're welcome. Thanks for the feedback.