find a group and averages

[rhino4eva]

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

 

[baitmaster]

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

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

 

[rhino4eva]

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

 

[baitmaster]

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

 

[rhino4eva]

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

 

[rhino4eva]

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

 

[baitmaster]

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