this is prob the longest form i've ever seen
it's comes up with combination game of a series for multiple series at home or on the road
some of you pin ball wizzards should be able to come up with something easier than this. i'll look at a micro as a possible solution
home
=IF(SUMPRODUCT((B$2:B15>B16-12)*(P$2
15=G16))=0,SUMPRODUCT((B$2:B15>B16-12)*(G$2:G15=G16))+1,IF(SUMPRODUCT((B$2:B15>B16-11)*(P$2
15=G16))=0,SUMPRODUCT((B$2:B15>B16-11)*(G$2:G15=G16))+1,IF(SUMPRODUCT((B$2:B15>B16-10)*(P$2
15=G16))=0,SUMPRODUCT((B$2:B15>B16-10)*(G$2:G15=G16))+1,IF(SUMPRODUCT((B$2:B15>B16-9)*(P$2
15=G16))=0,SUMPRODUCT((B$2:B15>B16-9)*(G$2:G15=G16))+1,IF(SUMPRODUCT((B$2:B15>B16-8)*(P$2
15=G16))=0,SUMPRODUCT((B$2:B15>B16-8)*(G$2:G15=G16))+1,IF(SUMPRODUCT((B$2:B15>B16-7)*(P$2
15=G16))=0,SUMPRODUCT((B$2:B15>B16-7)*(G$2:G15=G16))+1,IF(SUMPRODUCT((B$2:B15>B16-6)*(P$2
15=G16))=0,SUMPRODUCT((B$2:B15>B16-6)*(G$2:G15=G16))+1,IF(SUMPRODUCT((B$2:B15>B16-5)*(P$2
15=G16))=0,SUMPRODUCT((B$2:B15>B16-5)*(G$2:G15=G16))+1,IF(SUMPRODUCT((B$2:B15>B16-4)*(P$2
15=G16))=0,SUMPRODUCT((B$2:B15>B16-4)*(G$2:G15=G16))+1,IF(SUMPRODUCT((B$2:B15>B16-3)*(P$2
15=G16))=0,SUMPRODUCT((B$2:B15>B16-3)*(G$2:G15=G16))+1,IF(SUMPRODUCT((B$2:B15>B16-2)*(P$2
15=G16))=0,SUMPRODUCT((B$2:B15>B16-2)*(G$2:G15=G16)),1)+1))))))))))
vis
=IF(SUMPRODUCT((B$2:B15>B16-12)*(G$2:G15=P16))=0,SUMPRODUCT((B$2:B15>B16-12)*(P$2
15=P16))+1,IF(SUMPRODUCT((B$2:B15>B16-11)*(G$2:G15=P16))=0,SUMPRODUCT((B$2:B15>B16-11)*(P$2
15=P16))+1,IF(SUMPRODUCT((B$2:B15>B16-10)*(G$2:G15=P16))=0,SUMPRODUCT((B$2:B15>B16-10)*(P$2
15=P16))+1,IF(SUMPRODUCT((B$2:B15>B16-9)*(G$2:G15=P16))=0,SUMPRODUCT((B$2:B15>B16-9)*(P$2
15=P16))+1,IF(SUMPRODUCT((B$2:B15>B16-8)*(G$2:G15=P16))=0,SUMPRODUCT((B$2:B15>B16-8)*(P$2
15=P16))+1,IF(SUMPRODUCT((B$2:B15>B16-7)*(G$2:G15=P16))=0,SUMPRODUCT((B$2:B15>B16-7)*(P$2
15=P16))+1,IF(SUMPRODUCT((B$2:B15>B16-6)*(G$2:G15=P16))=0,SUMPRODUCT((B$2:B15>B16-6)*(P$2
15=P16))+1,IF(SUMPRODUCT((B$2:B15>B16-5)*(G$2:G15=P16))=0,SUMPRODUCT((B$2:B15>B16-5)*(P$2
15=P16))+1,IF(SUMPRODUCT((B$2:B15>B16-4)*(G$2:G15=P16))=0,SUMPRODUCT((B$2:B15>B16-4)*(P$2
15=P16))+1,IF(SUMPRODUCT((B$2:B15>B16-3)*(G$2:G15=P16))=0,SUMPRODUCT((B$2:B15>B16-3)*(P$2
15=P16))+1,IF(SUMPRODUCT((B$2:B15>B16-2)*(G$2:G15=P16))=0,SUMPRODUCT((B$2:B15>B16-2)*(P$2
15=G16)),1)+1))))))))))
it's actually the number of games at home or on the road multiple series