Not even sure if Excel can do something like this but...

[JLP0380]

I need to create a spreadsheet for work that does a bunch of different things. First it must take 60 samples with 5 data points each and calculate the Mean Concentration, Mean RLU, RLU Standard Deviation & RLU % CV for each sample. The mean of the sample must be < or = 0.02 in order to pass this step. This has to all happen on one tab. Next it needs to take another set of 60 samples with 5 data points each and calculate the Mean Concentration and Recovery % for each sample. Recover % for the sample must be > or = to 85% to pass this step. This is tab two. Corresponding samples must pass both Pass/Fail tests to go on to the next step. Example: Sample 1 from the first step and Sample 1 from the second step must both pass in order for Sample 1 to move on to the next step. Of the samples that pass both tests Excel then needs to Rank, from Highest to Lowest, the samples by the RLU Mean but all of the other data (System #, Sample #, Mean Concentration, RLU Mean & RLU %CV) must be kept together in this ranking. Then Excel needs to calculate the RLU Mean Median. This is tab three. From there Excel needs to pull the Samples and all their data (System #, Sample #, Mean Concentration, RLU Mean & RLU %CV) that fall BELOW the Median to Tab 4 where they are then Ranked, From Highest to Lowest, again using the RLU Mean. Then Excel needs to pull theses samples and accompanying data to tab 5 where the samples are the Ranked, from Highest to Lowest, by their RLU %CV values. Then Excel needs to calculate the RLU %CV Median. And finally Excel needs to pull all the samples numbers and accompanying System numbers to tab 6 that fall BELOW the RLU %CV Median. These are the samples and information that needs to be reported.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Are there even formulas out there that can do all this? I'm trying to get Excel to do all this for us so that we do not have to do all the calculations by hand as that takes us 10+ hours to do and can hold up production. Please help!<o></o>

 

[xtremegrump]

Please provide some sample data.

 

[Peter_SSs]

I would say that Excel can do all that and, once set up, do it very quickly. However, it is quite a project so you may not get it all here in one go. This forum is more about helping with small parts of projects rather than developing a whole project from scratch.

You would need to use macros (rather than just formulas) to do some of that manipulation, calculation, sorting, moving etc.

a) Is that acceptable?

b) Do you have any experience with writing or adapting macros?

If you do provide some sample data (I'm sure it would help), please make it small. Instead of 60 samples with 5 data points, could you make up some small dummy data with say 6 samples with 3 data points? It would also help if, with those small samples you gave the expected results and how you calculated them. Terms like RLU and CV mean nothing to me and probably plenty of others here.