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]

That formula seems to be nonsensical to me.
Have you missed part of it out ?

 

[Fluff]

If you get an error on the first part of the formula, the IFERROR is saying do the same thing again.

What are you trying to achieve?

 

[VBE313]

Hi Fluff, this formula helps me find the Top Level aka "Parent Part" Cost Per Unit, I have multiple iterations in my spreadsheet with this formula. I understand what the IFERROR is doing, I just have multiple formulas like this in my spreadsheet and my VBA code is very slow.

 

[VBE313]

Hi Gerald, this formula helps me find the Top Level aka "Parent Part" Cost Per Unit, I have multiple iterations in my spreadsheet with this formula​

 

[Gerald Higgins]

Just as repeating parts of a formula MIGHT be superfluous, there is probably no need to repeat comments in consecutive posts

Anyway, posts #4 and #5 don't really help me to understand what you are trying to do.

If we try breaking your formula into chunks, what is this bit by itself doing ?

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

This seems to me a very unusual way of constructing a MATCH function, if it's doing something really clever can you explain it to me please ?

I could understand this
MATCH(TRUE,$P6:$P$2100,0)

 

[Fluff]

It would help if you could post some sample data along with an explanation of what you are trying to do.
There are some add-ins available here that enable you to post data to the thread https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[steve the fish]

This:

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

is the same as this:

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

it looks to me.

 

[RoryA]

I would have thought that MATCH(1,$P6:$P$2100,0) would be more efficient.

 

[steve the fish]

So if i had to guess it needs to sum from O5 to the first cell with a 1 in it in column P.