Looking for a formula to accomplish the following...

[henningstl]

Hi,

I'm currently working on a worksheet and need to find a way to classify the following:

Below is a snapshot of some rankings by state for a measure I'm looking at. What I need to do is classify them on a Red (Top 25%)/Orange/Yellow/Green (Lowest 25%) scale. The problem I'm having though is that each state doesn't have the same number of health plans.

<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>
[TD="class: xl64, width: 64"]State[/TD]
[TD="class: xl64, width: 64"]Rank[/TD]

[TD="class: xl65"]AZ[/TD]
[TD="class: xl65"]1[/TD]

[TD="class: xl65"]AZ[/TD]
[TD="class: xl65"]1[/TD]

[TD="class: xl66"]FL[/TD]
[TD="class: xl66"]6[/TD]

[TD="class: xl66"]FL[/TD]
[TD="class: xl66"]9[/TD]

[TD="class: xl66"]FL[/TD]
[TD="class: xl66"]11[/TD]

[TD="class: xl66"]FL[/TD]
[TD="class: xl66"]12[/TD]

[TD="class: xl66"]FL[/TD]
[TD="class: xl66"]10[/TD]

[TD="class: xl66"]FL[/TD]
[TD="class: xl66"]1[/TD]

[TD="class: xl66"]FL[/TD]
[TD="class: xl66"]3[/TD]

[TD="class: xl66"]FL[/TD]
[TD="class: xl66"]7[/TD]

[TD="class: xl66"]FL[/TD]
[TD="class: xl66"]8[/TD]

[TD="class: xl66"]FL[/TD]
[TD="class: xl66"]4[/TD]

[TD="class: xl66"]FL[/TD]
[TD="class: xl66"]5[/TD]

[TD="class: xl66"]FL[/TD]
[TD="class: xl66"]2
[/TD]

</tbody>

Is there an easy formula I can use to get these classified into the appropriate buckets? Any assistance would be greatly appreciated.