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

#### MikeyP14

##### New Member
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

 CN sub category max min max location min location CN 101 p 7​ 0.4​ CN 101_3 CN 101_2 CN 101_1_T p 4​ 1​ CN 101_1 CN 101_1 CN 101_2_T p 0.6​ 0.4​ CN 101_2 CN 101_2 CN 101_3_T p 7​ 3​ CN 101_3 CN 101_3 CN 100 50​ 50​ CN 100 CN 100 CN 55 90​ 9​ CN 55_2 CN 55 CN 55_1_R 35​ 35​ CN 55_1 CN 55_1 CN 55_2_R 90​ 90​ CN 55_2 CN 55_2 CN 55_3_R 58​ 58​ CN 55_3 CN 55_3 CN 70 92​ 21​ CN 70_3 CN 70 CN 70_1_S 50​ 50​ CN 70_1 CN 70_1 CN 70_2_S 65​ 65​ CN 70_2 CN 70_2 CN 70_3_S 92​ 92​ CN 70_3 CN 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

 CN Value 1 Value 2 value 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.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### offthelip

##### Well-known Member
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
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.

#### RoryA

##### MrExcel MVP, Moderator
Have you tried using Tables?

#### offthelip

##### Well-known Member
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
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.

Replies
1
Views
71
Replies
2
Views
155
Replies
3
Views
228
Replies
1
Views
192
Replies
2
Views
180

1,127,859
Messages
5,627,293
Members
416,236
Latest member
Lynchbox

### 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.

### Which adblocker are you using?

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

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