Hi Mike,
Thanks a zillion for the suggestion. But it did not work, got #NUM! error for the first formula after changing AV to aston villa and "over" for the second formula which should output "under" because AV=0.
Thanks and Regards
Mike H wrote:
Hi,Try this ARRAY formula method.
21-Nov-09
Hi
Try this ARRAY formula method. See below on how to enter and array and whe
it is array entered dag down for the second and third occurrence etc
=IF(INDEX($C$1:$C$13,LARGE(($A$1:$A$13="AV")*ROW($A$1:$A$13),COUNTIF($A$1:$A$13,"AV")+1-ROW(A1)))>1.5,"Over","Under"
Note for testing I shortened Aston Villa to AV so change back. In practice
would not use the name in the formula, id use a cell reference. In this cas
E1 hold Aston Vill
=IF(INDEX($C$1:$C$13,LARGE(($A$1:$A$13=E1)*ROW($A$1:$A$13),COUNTIF($A$1:$A$13,E1)+1-ROW(A1)))>1.5,"Over","Under"
This is an array formula which must be entered by pressing CTRL+Shift+Ente
'and not just Enter. If you do it correctly then Excel will put curly bracket
'around the formula {}. You cannot type these yourself. If you edit the formul
'you must enter it again with CTRL+Shift+Enter
P.S I do not like the nil nil for Chelski- Liverpool, I prefer 0 - 5. You'l
never walk alone
Mik
:
Previous Posts In This Thread:
EggHeadCafe - Software Developer Portal of Choice
DPAPI Encrypt and store sensitive data
http://www.eggheadcafe.com/tutorial...c-d876c238a315/dpapi-encrypt-and-store-s.aspx