Long formula causing resource error and wanting to try a UDF but where to begin

MikeyP14

New Member
Joined
Jan 27, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hi All,

I have multiple formulas that are reading from one another and I currently have them setup to read all the data in a set column and I believe that is causing the sheet to run out of resources while computing. The max location and min location formulas are the ones I think a UDF could help. I was thinking if I could tell it what I want to evaluate and have it evaluate the data to the last row in the data tab that should save a significant amount of time computing and also should always change if the amount of data in the tab changes. I tried to figure out how to dynamically pick the last row in a dataset within excel formulas without going into VBA but I am thinking now that it might make sense to create a user defined formula but I have never messed around with one and it seems to be a bit more involved than my skillset allows for.

I have a summary sheet which is pulling information from a data tab depending on specific criteria

see table below that is in my summary sheet Assume "CN" is in cell A1 and "min location" is in cell F1

CNsub categorymaxminmax locationmin location
CN 101p
7​
0.4​
CN 101_3CN 101_2
CN 101_1_Tp
4​
1​
CN 101_1CN 101_1
CN 101_2_Tp
0.6​
0.4​
CN 101_2CN 101_2
CN 101_3_Tp
7​
3​
CN 101_3CN 101_3
CN 100
50​
50​
CN 100CN 100
CN 55
90​
9​
CN 55_2CN 55
CN 55_1_R
35​
35​
CN 55_1CN 55_1
CN 55_2_R
90​
90​
CN 55_2CN 55_2
CN 55_3_R
58​
58​
CN 55_3CN 55_3
CN 70
92​
21​
CN 70_3CN 70
CN 70_1_S
50​
50​
CN 70_1CN 70_1
CN 70_2_S
65​
65​
CN 70_2CN 70_2
CN 70_3_S
92​
92​
CN 70_3CN 70_3

Calculations for row with CN 101
max =IFERROR(IF(COUNTIF(B2,"*p*"),(AGGREGATE(14,6,Data!$C:$D/(LEFT(Data!$A:$A,LEN(A2))=A2&""),1)),(AGGREGATE(14,6,Data!$B:$B/(LEFT(Data!$A:$A,LEN(A2))=A2&""),1))),"no matches")

min =IF(COUNTIF(B2,"*p*"),(AGGREGATE(15,6,Data!$C:$D/(LEFT(Data!$A:$A,LEN(A2))=A2&""),1)),(AGGREGATE(15,6,Data!$B:$B/(LEFT(Data!$A:$A,LEN(A2))=A2&""),1)))

max location = (IF(COUNTIF(B2,"*p*"),(LEFT(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((C2=Data!$C:$C),(C2=Data!$C:$C),(C2=Data!$D:$D)),0)),IFERROR(FIND("_S",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((C2=Data!$C:$C),(C2=Data!$C:$C),(C2=Data!$D:$D)),0)))-1,IFERROR(FIND("_R",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((C2=Data!$C:$C),(C2=Data!$C:$C),(C2=Data!$D:$D)),0)))-1,IFERROR(FIND("_T",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((C2=Data!$C:$C),(C2=Data!$C:$C),(C2=Data!$D:$D)),0)))-1,(LEN(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((C2=Data!$C:$C),(C2=Data!$C:$C),(C2=Data!$D:$D)),0))))))))),(LEFT(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(C2=Data!$B:$B),0)),IFERROR(FIND("_S",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(C2=Data!$B:$B),0)))-1,IFERROR(FIND("_R",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(C2=Data!$B:$B),0)))-1,IFERROR(FIND("_T",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(C2=Data!$B:$B),0)))-1,(LEN(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(C2=Data!$B:$B),0)))))))))))

min location =(IF(COUNTIF(B2,"*p*"),(LEFT(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((D2=Data!$C:$C),(D2=Data!$C:$C),(D2=Data!$D:$D)),0)),IFERROR(FIND("_S",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((D2=Data!$C:$C),(D2=Data!$C:$C),(D2=Data!$D:$D)),0)))-1,IFERROR(FIND("_R",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((D2=Data!$C:$C),(D2=Data!$C:$C),(D2=Data!$D:$D)),0)))-1,IFERROR(FIND("_T",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((D2=Data!$C:$C),(D2=Data!$C:$C),(D2=Data!$D:$D)),0)))-1,(LEN(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((D2=Data!$C:$C),(D2=Data!$C:$C),(D2=Data!$D:$D)),0))))))))),(LEFT(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(D2=Data!$B:$B),0)),IFERROR(FIND("_S",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(D2=Data!$B:$B),0)))-1,IFERROR(FIND("_R",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(D2=Data!$B:$B),0)))-1,IFERROR(FIND("_T",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(D2=Data!$B:$B),0)))-1,(LEN(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(D2=Data!$B:$B),0)))))))))))

Data tab

CNValue 1Value 2value 3
CN 101
3​
2​
1​
CN 101_1_T
5​
1​
4​
CN 101_2_T
1​
0.6​
0.4​
CN 101_3_T
10​
7​
3​
CN 100
50​
30​
90​
CN 55
9​
75​
6​
CN 55_1_R
35​
50​
88​
CN 55_2_R
90​
1000​
2​
CN 55_3_R
58​
63​
678​
CN 70
21​
31​
9​
CN 70_1_S
50​
99​
45​
CN 70_2_S
65​
44​
78​
CN 70_3_S
92​
95​
235​

I would appreciate any guidance here. If this is not clear and you need additional information I will do my best to provide it.

Thanks in advance!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows
All of your formula are using entire columns because you have specified them as $A:$A, etc. you might find that it uses a lot less resource if you specify where the bottom of the column is , even if it is in the thousands it is going to be less than million rows that excel can cope with
 

MikeyP14

New Member
Joined
Jan 27, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Anyone have any suggestions on a UDF for this? I know the formulas are causing excel to run out of computing power because I have them reading A:A, B:B, etc... but that is why I was hoping someone might have a clever UDF for this since you make the UDF in VBA I was hoping you could tell it to compute to the last row of data in a sheet and then you would just need to make the input which column to look at within the sheet and it would automatically calculate to the last row.

Any advice is appreciated.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Have you tried using Tables?
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows
Trying to work out what the udf needs to do just from your huge equations and no other information at all, is not a trivial task. Certainly determining the last row with any data in it is easily done in VBA. Did you try my suggestion?
 

MikeyP14

New Member
Joined
Jan 27, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
I have shortened my dataset to be less lines, which has worked, but since the formula is so long I was hoping I wouldn't have to go in and edit it every time I wanted to use it.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,577
Members
417,151
Latest member
ChickenTenderer

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
Top