Last 3 results in a range

[AlfUK]

This is my first post so go easy on me.

I have created a spreadsheet with the backup job status for a number of servers. I would like to take the last 3 results for each backup job and then check whether the failure rate is more than 35%. I have looked through countless forums and sites trying to just find a way to find the last cell in a range, but have as yet found nothing helpful.

The status I what to check on is "FAILED".

I would like to do this by formula not VB.

Any pointers in the right direction would be greatly appreciated.

Thanks,
AlfUK

 

[Scott Huish]

Perhaps:

=COUNTIF(OFFSET(INDEX(A:A,MATCH(REPT("z",255),A:A)),-2,,3),"FAILED")>1

I'm only checking for greater than 1, because with only 3 results, 35% is never attainable. 1 instance would be 33 1/3%, so anything higher than 1 FAILED meets your criteria.

 

[AlfUK]

Thanks HOTPEPPER it works. Diamond.

Can I throw one more thing into the equation...

Can I search for more than 1 entry. So if the cell contents are either "FAILED" or "CANCELLED".

 

[snasui]

Try this

=SUMPRODUCT(ISNUMBER(MATCH(OFFSET(A1,MATCH(REPT("z",255),A:A)-1,0,-3),{"Failed","Cancelled"},0))+0)>1

Hope this helps.

 

[Scott Huish]

=SUM(COUNTIF(OFFSET(INDEX(A:A,MATCH(REPT("z",255),A:A)),-2,,3),{"CANCELLED","FAILED"}))>1

 

[AlfUK]

Thanks HOTPEPPER it works great. I modified the formula slightly as I was using columns not rows.

Thanks again for your help.