Some Subtle Issues with Some Formulas

Attached is a spreadsheet. A little complex, but let's see if I can explain it. The problem lies in what the little black boxes (ex. F4) are returning for a number. One of the things the formula is referencing is the B column number on the same row. The problem comes from what type of number is in that box evidently. For example, B4 returns a value of 1 from another worksheet in the file. This should translate into the black boxes in row 4 reading as follows 0,0,0,0,1,0,0,0,0. But instead it reads all 0s. Now if I simply type the number 1 into B4, and therefore typing over the formula, the correct numbers will return in the black boxes. I don't understand.

You can see the difference by typing the numbers in B4, B6, B8 & B10.

Any ideas? I would like to keep my formulas in Column B's cells rather than manually typing them. Thanks

Hi

The number in records!H3 is approx .83, not 1. If you examine the match statement, it is returning 1 as 0 is the largest number <= .83. If you want it to use 1, then use the ROUND function to convert .83 to 1. This can be done in various places.

One easy way is to make the formula in
records!H3: =ROUND(VLOOKUP(I3,$A$3:$F$12,6,FALSE),0)
This will bring back a result of 1 in scoring!N4.

Another place to make the conversion is
scoring!N4: =IF($B4="","",INDEX($AC$5:$AK$23,MATCH(ROUND($B4,0),$AB$5:$AB$23,1),MATCH(M$1,$AC$4:$AK$4,0)))


HTH

rylo