Percentage Calculation from last 5 entries

[ExcelRoy]

Hi,

I am looking to calculate an efficiency percentage from the last 5 jobs worked

ie

150%
100%
-
-
150%

I am currently using the following formula

=IF(F16="","",SUM(J23:J27)/5)

But this assumes there is 5 entries, but as you can see from the example there is only 3 entries from the 5 available thus giving a false reading

Can I modify my formula to suit the available entries?

Many thanks

 

[AnalystJames]

I am getting 92 and 197.4 with

=SUM(A1:A5)/COUNTIF(A1:A5,"<>0")

is there an example where this fails?

 

[ExcelRoy]

Hi,

97%
76%
118%
-
77%
this should return 92% because its only divided by 4?

but when we have
-
346%
-
-
208%
this should return 277% because its divided by2 only

Thanks

 

[AnalystJames]

I am getting 92 and 277 using

=SUM(A1:A5)/COUNTIF(A1:A5,"<>0")

Did you adjust the ranges? A1:A5 to J23:J27?

=SUM(J23:J27)/COUNTIF(J23:J27,"<>0")

 

[ExcelRoy]

Hi,

Yes I changed the range

Another example

-
-
-
141%
134%

Should return 137% as its only divided by 2 this time?

But the formula returns 55%

Thanks

 

[shg]

Average ignores text and blank cells:

Row\Col	A​	B​	C​	D​
1​	97%​
2​	76%​	346%​
3​	118%​
4​			141%​
5​	77%​	208%​	134%​
6​	92%​	277%​	138%​	A6: =AVERAGE(A1:A5)

 

[ExcelRoy]

That's it, many thanks shg

Many thanks AnalystJames for trying to help

 

[shg]

You're welcome.