Football Box Pool - Help With Formula??

olimits7

Board Regular
Joined
Oct 29, 2004
Messages
178
Hello,

I created a football box pool in excel and I used a combination of the OFFSET and MATCH formulas to find out who the winner is in each quarter depending on what score I put in the box. The formula works fine with single digit numbers, but I get a #N/A error for double digit scores.

For double digit scores it should just look at the last digit in the score to determine who won that quarter. Does anybody know of a way that I can just look at the second digit??

Here is a copy of the formula I used:

Code:
=IF(OR(ISBLANK($C$15),ISBLANK($C$16)),"No Score",OFFSET($B$2,MATCH($C$16,$B$3:$B$12,0),MATCH($C$15,$C$2:$L$2,0)))

Thank you,

olimits7
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
I'd suggest that INDEX is a better function to use than OFFSET in this situation but, nevertheless, for your specific request, if I understand correctly, try replacing C15 in the formula with MOD(C15,10) and similar for C16, i.e.

=IF(COUNT(C15:C16)=2,INDEX(C3:L12,MATCH(MOD(C16,10),B3:B12,0),MATCH(MOD(C15,10),C2:L2,0)),"No Score")
 

Watch MrExcel Video

Forum statistics

Threads
1,113,794
Messages
5,544,322
Members
410,603
Latest member
rseckler
Top