Need help finding the most efficient formula so my workbook isn't too slow?

MickeyW

New Member
Joined
Apr 2, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hey Guys,

Only new to the excel world but trying to dive straight in. I have an issue. Pretty much I have received some lab results for specific samples which I have an expected result that should be returned. I have built a reference table with all the expected min and max range and specific name of said samples. I need to be able to look up the correct sample in my refence table and check if it's within the min and max range and give a pass or fail. I have done a IFS formula, but it was a whole A4 sheet long and thats just clunky and impractical. My database will be thousands and thousands of returned results and I can't afford to have it slow down. So I used a vlookup formula which I managed to get it to work very well. But more research it looks like that may slow down my workbook aswell. So I changed it to an Index/match formula. But it's not working for all the samples and I can't see a problem with it...

So my two questions are:

1: What is wrong with my formula?
2: Is my formula the most efficient and will ensure my spreadsheet doesn't slow down to a crawl?


Thanks in Advance!!!


1617384757114.png
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Doesn't look to be anything wrong with the formula, but very difficult to tell from an image. Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Hi,

Thanks for replying. My computer crashed, so had to start again...

STD_check.xlsx
ABCDEFGHIJKL
1 Std ID Submission Number Original Assay Reassay Results Original Weight Pass/Fail? STDAuMin (2SD)Max (2SD)SD Value
2G911-60.18095FailG316-40.240.220.260.01
3G316-50.52095PassG316-50.503910.456212520.55160760.0238488
4G911-60.18095FailG308-21.1130931.017390831.208794990.047851
5G911-60.18095FailG911-31.371.251.490.06
6G913-22.410100PassG914-63.2131822.976742253.4496210.1182197
7G916-74.410105FailG916-74.5070564.224499214.789612290.1412783
8G911-60.1683160.140906760.195724390.0137044
9G312-70.220.20.240.01
10G913-10.820.760.880.03
11G913-62.192.012.370.09
12G913-22.42.242.560.08
13G912-70.4239440.375409250.472478090.0242672
14G911-101.31.21.40.05
15G913-41.371.291.450.04
16G913-62.192.012.370.09
17G912-64.0796653.735981944.423347170.1718413
18G300-52.342.122.560.11
19G905-73.923.624.220.15
Sheet1
Cell Formulas
RangeFormula
J2:J19J2=I2-2*L2
K2:K19K2=I2+2*L2
F2:F7F2=IF(AND(C2>=(INDEX(STD_ref[Min (2SD)],MATCH(A2,STD_ref[STD]),0)),C2<=(INDEX(STD_ref[Max (2SD)],MATCH(A2,STD_ref[STD]),0))),"Pass","Fail")
Named Ranges
NameRefers ToCells
Au=Sheet1!$I$2:$I$19J2:K2
G911_6=Sheet1!$A$3:$A$7F3
Max__2SD=Sheet1!$K$2:$K$19F2:F7
Min__2SD=Sheet1!$J$2:$J$19F2:F7
SD_Value=Sheet1!$L$2:$L$19J2:K2
STD=Sheet1!$H$2:$H$19F2:F7
STD_ID=test1F2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:B1Other TypeDataBarNO
C1:E1Other TypeDataBarNO


Hope that worked!
 
Upvote 0
Many thanks for that, you have a couple of ",0" in the wrong place, the formula should be
Excel Formula:
=IF(AND(C2>=(INDEX(STD_ref[Min (2SD)],MATCH(A2,STD_ref[STD],0))),C2<=(INDEX(STD_ref[Max (2SD)],MATCH(A2,STD_ref[STD],0)))),"Pass","Fail")

Do you have the LET function?
 
Upvote 0
The formula in G should be more efficient than yours
+Fluff 1.xlsm
ABCDEFGHIJKLM
1 Std ID Submission Number Original Assay Reassay Results Original Weight Pass/Fail? STDAuMin (2SD)Max (2SD)SD Value
2G911-60.18095PassPassG316-40.240.220.260.01
3G316-50.52095PassPassG316-50.5039100560.4562125160.5516075960.02384877
4G911-60.14095FailFailG308-21.1130929131.0173908351.208794990.047851039
5G911-60.196095FailFailG911-31.371.251.490.06
6G913-22.410100PassPassG914-63.2131816262.9767422533.4496209990.118219687
7G916-74.410105PassPassG916-74.5070557474.2244992094.7896122850.141278269
8G905-74.30105FailFailG911-60.1683155740.1409067620.1957243860.013704406
9G312-70.220.20.240.01
10G913-10.820.760.880.03
11G913-62.192.012.370.09
12G913-22.42.242.560.08
13G912-70.4239436710.375409250.4724780920.024267211
14G911-101.31.21.40.05
15G913-41.371.291.450.04
16G913-62.192.012.370.09
17G912-64.0796645573.7359819444.4233471690.171841306
18G300-52.342.122.560.11
19G905-73.923.624.220.15
Master
Cell Formulas
RangeFormula
F2:F8F2=IF(AND(C2>=(INDEX(STD_ref[Min (2SD)],MATCH(A2,STD_ref[STD],0))),C2<=(INDEX(STD_ref[Max (2SD)],MATCH(A2,STD_ref[STD],0)))),"Pass","Fail")
G2:G8G2=IF(MEDIAN(XLOOKUP(A2,STD_ref[STD],STD_ref[[Min (2SD)]:[Max (2SD)]],"",0),C2)=C2,"Pass","Fail")
K2:K19K2=J2-2*M2
L2:L19L2=J2+2*M2
 
Upvote 0
Solution
Thank you much for that! It looks much better :)

I started learning it all today, I didn't come across LET? I'd be keen to hear about it if you have a spare moment

Also is there anything actually wrong with using the vlookup in a large Excel workbook? I attempted to do some research on volatile formulas as my spreadsheet sheet was barely usable. One of things they keep saying was swapping vlookup for index. But it never said vlookup was bad either.


Thanks again for your help
 
Upvote 0
There's nothing wrong with Vlookup, although an Index/Match tends to be faster & the new Xlookup is faster still.
In this instance there is no need for the Let function as I came up with the idea I used in col G.
 
Upvote 0
There's nothing wrong with Vlookup, although an Index/Match tends to be faster & the new Xlookup is faster still.
In this instance there is no need for the Let function as I came up with the idea I used in col G.
Awesome. Thank you for clearing that up!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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