# find a group and averages

#### rhino4eva

##### Active Member
fn1-1 1.1
fn1-1 1.2
fn1-1 1.1
fn2-1 3.4
fn2-2 3.5
fn3-1 6.6
fn4-2 6.4

this is a really big ask. i dont know where to start really
i work in a laboratory where our analysers throw out huge lists of data similar to the above
each list is made of of a groups of similar file names and a score an xl sheet
what i need is a macro that will find each group of values ie all the fn1 or fn2 etc then calculate the average score for that group and delete anything that is less than 90% of the average from the group

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
at a really simplistic level, with your sample data in A1:B7, the following formula in cell C1, copied down as far as is required (double click the fill handle to do this quickly), should identify whether any value is less than 90% of it's group value

=B1 < SUMIF(A:A,A1,B:B)/COUNTIF(A:A,A1)*0.9<?XML:NAMESPACE PREFIX = SUMIF(A /><SUMIF(A:A,A1,B:B) p COUNTIF(A:A,A1)*0.9<>
This will return TRUE or FALSE

the following code will then delete all rows where TRUE is found, although there are better ways, as this will be slow. Simply using Autofilter to hide all FALSE rows, then deleting the visible range would work

Code:
``````Application.calculation = xlCalculationManual

For i = [end row] To [start row] Step -1
If Cells(i, [true / false column]).Value = "True" Then
Rows(i).Delete shift:=xlUp
End If
Next i

Application.calculation = xlCalculationAutomatic``````
</SUMIF(A:A,A1,B:B)>

thanks for haviing a go baitmaster but i have tried the formula out and it doesn't quite work

I'm guessing that you mean it doesn't count the groupings correctly. I note in your descrition you refer to e.g. "fn1" but in your data you have "fn1-1, fn1-2" etc

In which case you would need to perform the calculation on the fn1 only. Simplest way to do this, create an additional column of fn1-1, fn1-2 etc, then find and replace (ctrl + H) all instances of "-*" with [nothing]

You could do this with macros, but I'm trying to stick to a straightforward process at the moment. You could also look at the full result name and find only the first section, but with a large dataset you'd have some heavy calculations going on, causing potantial performance problems

thanks for getting back !
i dont think i am very good at describing my requirements
the results that are derived from the analyser are 7 digit lab numbers
but for each labnumber there will be several rows with the same number but different data points

1234567 ¦ 1.3
1234567 ¦ 1.3
1234567 ¦ 1.4
1234567 ¦ 0.1
1234567 ¦ 1.3
1234570 ¦ 8.9
1234570 ¦ 8.9
1234570 ¦ 1.1

so i'll try again ... my data comes of the analyser like above. two columns ,first coulmn is the lab number (several entries of each number) with different data points for each. for each labnumber i need to delete any data point that is less than 90% of the average for that labnumber only

what's wrong with the formula in my first post? It appears to work for me. Just saying it doesn't work isn't helpful, it might help to explain the problem

Replies
1
Views
298
Replies
15
Views
793
Replies
1
Views
377
Replies
11
Views
1K
Replies
0
Views
473

1,203,667
Messages
6,056,649
Members
444,880
Latest member
Kinger1968

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