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

JLP0380

New Member
Joined
Nov 9, 2011
Messages
3
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,141
Messages
5,599,966
Members
414,352
Latest member
macquarie_jchan58

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