Search results

Hello Ninjas, Can someone help me with this?

Hello Ninjas, Can someone please help me? TIA. Say I have a workbook with a worksheet "Report". I have the following UDF which works fine when I have the "Report" worksheet on the same workbook. I would like to modify this UDF to lookup a worksheet ("Range") in another workbook on the...

""

Hi Hui, Can you please take a look at the dropbox file. TIA

Hello All, Thanks so much for helping me out. Hui's max correl is the closest to what I need. We have a reference database for fruits. Each fruit has its own unique fingerprint. Now we have some unknown samples which we need to identify. Our assay generates data which needs to be queried...

Hi Hui, Thanks for your help. I am sorry I think I worded my problem incorrectly. I have a master database with the following fingerprints. B 1 1 0 C -1 0 0 D 1 0 -1 E 0 0 -1 There are only 3 variables (1, 0, -1) I run an assay for an unknown sample and generate fingerprint eg, A...

Hello, Can someone help me with this problem? I have an array, say A 1 2 3 I would like to find the maximum correlation for this array in a database, say B 2 3 4 C 3 4 5 D 1 2 3 E 3 2 1 Is this possible by using excel formula? TIA

Sorry, I didn't understand the problem before I posted. It is way above my excel skills.

Hi Johnny, Is this what you want? "123 Noob street Winnersville" Trick is to combine 3 vlookups. =VLOOKUP(B2,D9:G10,2,0)&" "&VLOOKUP(B2,D9:G10,3,0)&" "&VLOOKUP(B2,D9:G10,4,0) Maybe there is a simpler formula to do this. Ninja's can you comment?

Can you please explain how it works? I don't see a cutoff for the SD. Also it does not work if there are blank values, can this be overcome? TIA.

Works beautifully. Thanks so much Narayan. And thanks to SirBJ7 too.

Hello All, I need some help with this problem, please. It looks simple but I am clueless how to approach it. Say I have an array 100 25 29 30 18 200 I would like to get the average for this array, but after deleting the outliers 100 and 200. Is there a way I can write-up a formula which...

Its nice to see someone going to this extent to help someone out. Thanks Hui. You are a great asset to the forum.

So many ways to do the same thing. That makes excel so versatile. Thanks Hui for the explanation. I am an excel novice, I think it makes sense now but I have to dissect it further to really understand it. Thanks

Thanks to both Narayan and Hui, all 3 formulas work. I prefer to go with Hui's second formula because it is simple, straight forward and I understand MID and FIND function. I am yet to learn the SEARCH and LEN function. Though I have to admit, I don't really understand Hui's first formula. I get...

Hello, Can someone help me extract the Batch # from the following string? "A549_CL130497_SAM602138_Batch100_Plate_5_H4" Answer: 100 The formula that I use, "=(MID($A1,FIND("Batch",$A1)+5,4)*1)". The problem with this formula is that the batch numbers range from 1-1000 and I would like to...

Hello, I am posting from a tablet hence cannot look at your file, but from what I understand advanced filter can do the job for you. Just select the range and add company name as criteria. Filter in place or a new worksheet.

Thanks so much Vijay. As I mentioned I am very new to Excel VBA and do not know how to declare consts for my situation. Please find my scenario below. File on server: serverDatabaseSample.csv (Gets updated with new sample info every night. File name is the same). File on desktop...

Great site. Newbie using Excel 2010. I have a csv file (>1000000 rows) on the server which updates every morning. I use this file as my source to vlookup and enter information. I have no problems with vlookup (know how to use it) but if I create a macro, it works only when I have the source...