How do I let Microsoft know that there's a glitch in Excel 2010 that needs fixing?
This has been replicated across three different computers now. I don't know where to upload the file so I'll just copy paste the results and then the equations for the proof below if you wish to replicate.
You can see there's a completely mis-matched pattern of errors with the Match function depending how the numbers were created... for curiosities sake I did also create parallel tables running if() equations confirming the numbers were as they say they are and they all came up True, indicating that according to the if() equation the numbers are identical and therefor SHOULD match.
<colgroup><col width="72" span="10" style="width:54pt"> </colgroup><tbody>
</tbody>
<colgroup><col><col span="9"></colgroup><tbody>
</tbody>
<tbody>
</tbody>
This has been replicated across three different computers now. I don't know where to upload the file so I'll just copy paste the results and then the equations for the proof below if you wish to replicate.
You can see there's a completely mis-matched pattern of errors with the Match function depending how the numbers were created... for curiosities sake I did also create parallel tables running if() equations confirming the numbers were as they say they are and they all came up True, indicating that according to the if() equation the numbers are identical and therefor SHOULD match.
Lookup Array | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 |
Pre-rounded | 0.01 | 0.12 | 0.23 | 0.34 | 0.45 | 0.56 | 0.67 | 0.78 | 0.89 |
Ceiling | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 |
Roundup | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 |
re-typed | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 |
Match Equations | |||||||||
Ceiling | 1 | 2 | #N/A | 4 | 5 | #N/A | #N/A | 8 | 9 |
Roundup | 1 | 2 | #N/A | 4 | 5 | 6 | 7 | #N/A | 9 |
re-typed | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
Equation based Lookup Array | |||||||||
Lookup Array | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 |
Pre-rounded | 0.01 | 0.12 | 0.23 | 0.34 | 0.45 | 0.56 | 0.67 | 0.78 | 0.89 |
Ceiling | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 |
Roundup | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 |
re-typed | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 |
Match Equations | |||||||||
Ceiling | #N/A | #N/A | 3 | #N/A | 5 | 6 | #N/A | 8 | #N/A |
Roundup | #N/A | #N/A | 3 | #N/A | 5 | #N/A | 7 | #N/A | #N/A |
re-typed | #N/A | #N/A | #N/A | #N/A | 5 | #N/A | 7 | 8 | #N/A |
Cumulative Equation based Lookup Array | |||||||||
Lookup Array | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 |
Pre-rounded | 0.01 | 0.12 | 0.23 | 0.34 | 0.45 | 0.56 | 0.67 | 0.78 | 0.89 |
Ceiling | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 |
Roundup | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 |
re-typed | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 |
Match Equations | |||||||||
Ceiling | 1 | 2 | 3 | 4 | 5 | #N/A | #N/A | #N/A | #N/A |
Roundup | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | #N/A |
re-typed | 1 | 2 | #N/A | 4 | 5 | 6 | 7 | #N/A | #N/A |
<colgroup><col width="72" span="10" style="width:54pt"> </colgroup><tbody>
</tbody>
Typed Lookup Array | |||||||||
Lookup Array | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 |
Pre-rounded | 0.01 | 0.12 | 0.23 | 0.34 | 0.45 | 0.56 | 0.67 | 0.78 | 0.89 |
Ceiling | =CEILING(B3,0.1) | =CEILING(C3,0.1) | =CEILING(D3,0.1) | =CEILING(E3,0.1) | =CEILING(F3,0.1) | =CEILING(G3,0.1) | =CEILING(H3,0.1) | =CEILING(I3,0.1) | =CEILING(J3,0.1) |
Roundup | =ROUNDUP(B3,1) | =ROUNDUP(C3,1) | =ROUNDUP(D3,1) | =ROUNDUP(E3,1) | =ROUNDUP(F3,1) | =ROUNDUP(G3,1) | =ROUNDUP(H3,1) | =ROUNDUP(I3,1) | =ROUNDUP(J3,1) |
re-typed | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 |
Match Equations | |||||||||
Ceiling | =MATCH(B4,$B$2:$J$2,0) | =MATCH(C4,$B$2:$J$2,0) | =MATCH(D4,$B$2:$J$2,0) | =MATCH(E4,$B$2:$J$2,0) | =MATCH(F4,$B$2:$J$2,0) | =MATCH(G4,$B$2:$J$2,0) | =MATCH(H4,$B$2:$J$2,0) | =MATCH(I4,$B$2:$J$2,0) | =MATCH(J4,$B$2:$J$2,0) |
Roundup | =MATCH(B5,$B$2:$J$2,0) | =MATCH(C5,$B$2:$J$2,0) | =MATCH(D5,$B$2:$J$2,0) | =MATCH(E5,$B$2:$J$2,0) | =MATCH(F5,$B$2:$J$2,0) | =MATCH(G5,$B$2:$J$2,0) | =MATCH(H5,$B$2:$J$2,0) | =MATCH(I5,$B$2:$J$2,0) | =MATCH(J5,$B$2:$J$2,0) |
re-typed | =MATCH(B6,$B$2:$J$2,0) | =MATCH(C6,$B$2:$J$2,0) | =MATCH(D6,$B$2:$J$2,0) | =MATCH(E6,$B$2:$J$2,0) | =MATCH(F6,$B$2:$J$2,0) | =MATCH(G6,$B$2:$J$2,0) | =MATCH(H6,$B$2:$J$2,0) | =MATCH(I6,$B$2:$J$2,0) | =MATCH(J6,$B$2:$J$2,0) |
Equation based Lookup Array | |||||||||
Lookup Array | =B2+1-1 | =C2+1-1 | =D2+1-1 | =E2+1-1 | =F2+1-1 | =G2+1-1 | =H2+1-1 | =I2+1-1 | =J2+1-1 |
Pre-rounded | 0.01 | 0.12 | 0.23 | 0.34 | 0.45 | 0.56 | 0.67 | 0.78 | 0.89 |
Ceiling | =CEILING(B15,0.1) | =CEILING(C15,0.1) | =CEILING(D15,0.1) | =CEILING(E15,0.1) | =CEILING(F15,0.1) | =CEILING(G15,0.1) | =CEILING(H15,0.1) | =CEILING(I15,0.1) | =CEILING(J15,0.1) |
Roundup | =ROUNDUP(B15,1) | =ROUNDUP(C15,1) | =ROUNDUP(D15,1) | =ROUNDUP(E15,1) | =ROUNDUP(F15,1) | =ROUNDUP(G15,1) | =ROUNDUP(H15,1) | =ROUNDUP(I15,1) | =ROUNDUP(J15,1) |
re-typed | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 |
Match Equations | |||||||||
Ceiling | =MATCH(B16,$B$14:$J$14,0) | =MATCH(C16,$B$14:$J$14,0) | =MATCH(D16,$B$14:$J$14,0) | =MATCH(E16,$B$14:$J$14,0) | =MATCH(F16,$B$14:$J$14,0) | =MATCH(G16,$B$14:$J$14,0) | =MATCH(H16,$B$14:$J$14,0) | =MATCH(I16,$B$14:$J$14,0) | =MATCH(J16,$B$14:$J$14,0) |
Roundup | =MATCH(B17,$B$14:$J$14,0) | =MATCH(C17,$B$14:$J$14,0) | =MATCH(D17,$B$14:$J$14,0) | =MATCH(E17,$B$14:$J$14,0) | =MATCH(F17,$B$14:$J$14,0) | =MATCH(G17,$B$14:$J$14,0) | =MATCH(H17,$B$14:$J$14,0) | =MATCH(I17,$B$14:$J$14,0) | =MATCH(J17,$B$14:$J$14,0) |
re-typed | =MATCH(B18,$B$14:$J$14,0) | =MATCH(C18,$B$14:$J$14,0) | =MATCH(D18,$B$14:$J$14,0) | =MATCH(E18,$B$14:$J$14,0) | =MATCH(F18,$B$14:$J$14,0) | =MATCH(G18,$B$14:$J$14,0) | =MATCH(H18,$B$14:$J$14,0) | =MATCH(I18,$B$14:$J$14,0) | =MATCH(J18,$B$14:$J$14,0) |
Cumulative Equation based Lookup Array | |||||||||
Lookup Array | 0.1 | =B26+0.1 | =C26+0.1 | =D26+0.1 | =E26+0.1 | =F26+0.1 | =G26+0.1 | =H26+0.1 | =I26+0.1 |
Pre-rounded | 0.01 | 0.12 | 0.23 | 0.34 | 0.45 | 0.56 | 0.67 | 0.78 | 0.89 |
Ceiling | =CEILING(B27,0.1) | =CEILING(C27,0.1) | =CEILING(D27,0.1) | =CEILING(E27,0.1) | =CEILING(F27,0.1) | =CEILING(G27,0.1) | =CEILING(H27,0.1) | =CEILING(I27,0.1) | =CEILING(J27,0.1) |
Roundup | =ROUNDUP(B27,1) | =ROUNDUP(C27,1) | =ROUNDUP(D27,1) | =ROUNDUP(E27,1) | =ROUNDUP(F27,1) | =ROUNDUP(G27,1) | =ROUNDUP(H27,1) | =ROUNDUP(I27,1) | =ROUNDUP(J27,1) |
re-typed | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 |
Match Equations | |||||||||
Ceiling | =MATCH(B28,$B$26:$J$26,0) | =MATCH(C28,$B$26:$J$26,0) | =MATCH(D28,$B$26:$J$26,0) | =MATCH(E28,$B$26:$J$26,0) | =MATCH(F28,$B$26:$J$26,0) | =MATCH(G28,$B$26:$J$26,0) | =MATCH(H28,$B$26:$J$26,0) | =MATCH(I28,$B$26:$J$26,0) | =MATCH(J28,$B$26:$J$26,0) |
Roundup | =MATCH(B29,$B$26:$J$26,0) | =MATCH(C29,$B$26:$J$26,0) | =MATCH(D29,$B$26:$J$26,0) | =MATCH(E29,$B$26:$J$26,0) | =MATCH(F29,$B$26:$J$26,0) | =MATCH(G29,$B$26:$J$26,0) | =MATCH(H29,$B$26:$J$26,0) | =MATCH(I29,$B$26:$J$26,0) | =MATCH(J29,$B$26:$J$26,0) |
re-typed | =MATCH(B30,$B$26:$J$26,0) | =MATCH(C30,$B$26:$J$26,0) | =MATCH(D30,$B$26:$J$26,0) | =MATCH(E30,$B$26:$J$26,0) | =MATCH(F30,$B$26:$J$26,0) | =MATCH(G30,$B$26:$J$26,0) | =MATCH(H30,$B$26:$J$26,0) | =MATCH(I30,$B$26:$J$26,0) | =MATCH(J30,$B$26:$J$26,0) |
<colgroup><col><col span="9"></colgroup><tbody>
</tbody>
Typed Lookup Array |
<tbody>
</tbody>