Filtered Average Late/On time/ Early percentage??

[jeskel]

Hi All,

I could really use some help with this.

=COUNTIF(J16:J575,"Late")/COUNTA(J16:J575)*100

This Formula will give me the average but it will not change as the filters are used?

I created a formula in VBA that does work and will change as the filter changes but will only work on numbers, not on time/late/early..

Function AverVisible(Rg As Range)
'UpdateByKutoolsforExcel20151208
Dim xCell As Range
Dim xCount As Integer
Dim xTtl As Double
Application.Volatile
Set Rg = Intersect(Rg.Parent.UsedRange, Rg)
For Each xCell In Rg
If xCell.ColumnWidth > 0 _
And xCell.RowHeight > 0 _
And Not IsEmpty(xCell) _
And IsNumeric(xCell.Value) Then
xTtl = xTtl + xCell.Value
xCount = xCount + 1
End If
Next
If xCount > 0 Then
AverVisible = xTtl / xCount
Else
AverVisible = 0
End If
End Function

If someone knows anything that could help it would be greatly appreciated

- Jessy

 

[James006]

Hello Jessy,

Regarding your Countif() formula for filtered records ... you could test following

=SUMPRODUCT(SUBTOTAL(3,OFFSET(J16:J575,ROW(J16:J575)-MIN(ROW(J16:J575)),,1)),ISNUMBER(SEARCH("Late",J16:J575))+0)

Hope this will help

 

[Eric W]

I noticed I had referenced the wrong row in a few places, try:

=SUMPRODUCT(--(J16:J575="on time"),SUBTOTAL(103,OFFSET(J16,ROW(J16:J575)-ROW(J16),0)))/SUBTOTAL(103,J16:J575)*100

This works OK on my test sheet. If you still get a #REF ! error, check that your range references are correct. And what version of Excel are you using?

 

[jeskel]

Hi James,

Sorry didn't work. I got an answer of 132 without the filter on but it should have been 23.57
Thanks for trying though Have a great day.

 

[jeskel]

Still getting the #REF !, ranges seem to be correct. I'm not sure... I have been looking for every possible answer to make this work for days ha ha.

Thanks for your help again!

 

[jeskel]

Jame's Formula counted the correct # of Lates, and does change on the filtered data but did not find the average
132 is how many are currently showing "late" through the entire sheet, when filter is changed it counts the # of late's displayed...just not the average.

If this helps anyone to figure it out better...

Thanks again.

- Jessy

 

[jeskel]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(J16:J575,ROW(J16:J575)-MIN(ROW(J16:J575)),,1)),ISNUMBER(SEARCH("Late",J16:J575))+0)/SUMPRODUCT(SUBTOTAL(3,OFFSET(J16:J575,ROW(J16:J575)-MIN(ROW(J16:J575)),,1)))*100

I Modified James's Formula and got it to work! THANK YOU SO MUCH for the help!

Posting just so you can see the formula incase it helps someone else down the road!

- Jessy

 

[James006]

Thanks for sharing your solution with the Forum ...:wink:

Thanks ... for your Thanks ...