Formula to populate cells based on largest number to smallest

[kenadams378]

Hi,

In the attached sheet i need a formula to look up the number in column A and then for the 2 highest numbers populate column B with 8, the next 2 highest numbers, populate column b with 6, the next 2 populate column b with 4 and then the last 2 populate column b with 2.

Is there a formula that will do this?

regards

[cbatrody]

Hi,

Try the following formula in B3:

=LOOKUP(RANK(A3,$A$3:$A$10,1)+COUNTIF($A$3:A3,A3)-1,{1,3,5,7})+1

[kenadams378]

Thanks i should have attached the full sheet...

I need the formulas to run down each cell in the blue column and calulate based on column D

[cbatrody]

What points will "#DIV/0! (0/0)" have? 2 or 8?

[kenadams378]

It would have 8 as it's the 2nd highest figure

[cbatrody]

Hi,

I have updated the formula in the attached file. However, this won't work when there is #DIV/0! in column "D". You need to modify the formula in column D by using IFERROR condition to set it to "0".