How can I calculate this Faster?

[VBE313]

=IFERROR(IF(P4=1,SUM(O5:INDEX($P5:$P$2100,MATCH(TRUE,($P5:$P$2100=1),0)),-1),"")/R2,IF(P4=1,SUM(O5:INDEX($P5:$P$2100,MATCH(TRUE,($P5:$P$2100=1),0)),-1),"")/R2)

Would a UDF calculate this faster?

 

[Gerald Higgins]

This:

=MATCH(TRUE,($P6:$P$2100=1),0)

is the same as this:

=MATCH(1,$P6:$P$2100,0)

it looks to me.

Well I've tried this and they give me different results.
=MATCH(1,$P6:$P$2100,0)
Returns 1 if the value 1 is held in P6, 2 if the value 1 is held in P7 and not P6, and so on.

I'm struggling to get the other formula to return anything other than #VALUE !

 

[steve the fish]

The match true formula requires control shift enter not just enter. They will then produce the same result.

 

[Gerald Higgins]

Thanks Steve, OK I get that, but what's the point ?

=MATCH(1,$P6:$P$2100,0)
is easier to enter and edit.

On a related topic, I wonder if the original poster is still reading any of this ?

 

[steve the fish]

They both are doing exactly the same thing. Finding the relative position of 1 within the range. There are lots of ways to write the same thing in excel often just down to preference of the user.

 

[VBE313]

Hi Gerald, Yes I am still reading this haha. I need to find a way to make this not an array formula!

 

[RoryA]

If you use this version:

MATCH(1,$P6:$P$2100,0)

then the formula doesn't need to be an array formula.