Vba Formula Array Calc

[bendy315]

Trying to calculate the percentage of projects meeting the target for each year. Here's what I came up with. Maybe there is a better way to do this, but I need it to work dynamically with changing data. What I have so far doesn't seem to return anything, just a null value...

'P-Std Calc
Range("H" & rwc).Select
Selection.FormulaArray = _
"=COUNTIFS(L2:L" & LastRow & ","" = """ & year & """"",AC2:AC" & LastRow & ","" <= """ & std & """"") / & count & "

Here's the Data...
[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Hrs[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]530[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]1,106[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]486[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]687[/TD]
[/TR]
</tbody>[/TABLE]


In this case year = 15, lastrow = 6, std = 700, and count =2, so should return 50% of projects in '15 beat the target.

 

[Marcelo Branco]

Not tested, but this seems fine to me
str = "=COUNTIFS($L$2:$L$@LR,""=""&@YR,$AC$2:$AC$@LR,""<=""&@std)/@count"

M.

 

[JackDanIce]

Ignore everything I said, you're right and it's what I had originally posted - too many speech marks and confusing myself!