Conditional Formatting - Last Occurrence

Hi there,

I need some help, a bit stuck here.

1 2 3 4 5
A 40 45 70 78 87

I put this formula down to find the first occurrence thats greater than 50
=COUNTIF($A$1:A1,">"&50)=1 and it worked to pick up the first occurrence more than 50.

But now I want to pick up the last occurrence less than 50. Countif doesnt seem to work and I am kinda stuck now...

Anyone can help?

See this file Please

Salim Ali - thanks for helping out but I am not sure how to apply array formula in conditional formatting...

Originally Posted by jagsnumpty

Salim Ali - thanks for helping out but I am not sure how to apply array formula in conditional formatting...

Formulas for cond.format (directly in Dialog Box without Ctrl+Shift+Enter)
and the cell a2 must me the active cell
*-last number

=$A2=LOOKUP(MAX($A$2:$A$20)+1,IF($A$2:$A$20<=$F$1,$A$2:$A$20),$A$2:$A$20)

*- first number

=$A2=INDEX($A$2:$A$20,MATCH(TRUE,IF($A$2:$A$20<=$F$1,TRUE,FALSE),0))

How about
=COUNTIF($A$1:$A2,">=50")=1

No, this doesnt work....

Originally Posted by jagsnumpty

No, this doesnt work....

See this file

Are you referring to me, or to salim ali?

Also what does "doesnt work" mean?
Can you please supply a sample workbook showing your data.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Sorry, i meant both of the formula Fluff13 & Salim provided aren't working for me.

My row of data comes in sequence. Refer to my file, in this case, I want to find the the last number that is less than cell yellow, which is March - 34.

I can find the first time its more than cell yellow, just need to flip this...

Ok, thanks for the file, try
=COUNTIF($B$2:C$2,">="&$J$1)=1

Originally Posted by Fluff13

Ok, thanks for the file, try
=COUNTIF($B$2:C$2,">="&$J$1)=1

BAMMM! This works! Thanks very much!!!!

You're welcome & thanks for the feedback