How can I calculate this Faster?

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
686
Office Version
  1. 365
Platform
  1. Windows
=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?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
:confused: 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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
This:

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

is the same as this:

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

it looks to me.
 
Upvote 0
I would have thought that MATCH(1,$P6:$P$2100,0) would be more efficient.
 
Upvote 0
So if i had to guess it needs to sum from O5 to the first cell with a 1 in it in column P.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top