Need a formula that will return the first 3 values in column Q & R that will show the digit zero when zero is leading value.
Excel 2007
| Excel Workbook | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| J | K | L | M | N | O | P | Q | R | |||
| 11 | 132844 | 070558 | 2 | 6 | 19 | 22 | 34 | 132 | 705 | ||
| 12 | 183588 | 133590 | 3 | 15 | 28 | 29 | 32 | 183 | 133 | ||
| 13 | 226554 | 127978 | 3 | 12 | 17 | 23 | 31 | 226 | 127 | ||
| 14 | 342589 | 059257 | 2 | 4 | 8 | 14 | 24 | 342 | 592 | ||
| 15 | 158462 | 309188 | 10 | 20 | 25 | 27 | 33 | 158 | 309 | ||
| 16 | 025497 | 363473 | 17 | 20 | 28 | 30 | 36 | 254 | 363 | ||
Sheet1 | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J11 | =COMBIN(36,5)-IF(32-$C11>0,COMBIN(36-$C11,5),0)-IF(32-$D11>0,COMBIN(36-$D11,4),0)-IF(33-$E11>0,COMBIN(36-$E11,3),0)-IF(34-$F11>0,COMBIN(36-$F11,2),0)-IF(36-$G11>0,COMBIN(36-$G11,1)) | |
| J12 | =COMBIN(36,5)-IF(32-$C12>0,COMBIN(36-$C12,5),0)-IF(32-$D12>0,COMBIN(36-$D12,4),0)-IF(33-$E12>0,COMBIN(36-$E12,3),0)-IF(34-$F12>0,COMBIN(36-$F12,2),0)-IF(36-$G12>0,COMBIN(36-$G12,1)) | |
| J13 | =COMBIN(36,5)-IF(32-$C13>0,COMBIN(36-$C13,5),0)-IF(32-$D13>0,COMBIN(36-$D13,4),0)-IF(33-$E13>0,COMBIN(36-$E13,3),0)-IF(34-$F13>0,COMBIN(36-$F13,2),0)-IF(36-$G13>0,COMBIN(36-$G13,1)) | |
| J14 | =COMBIN(36,5)-IF(32-$C14>0,COMBIN(36-$C14,5),0)-IF(32-$D14>0,COMBIN(36-$D14,4),0)-IF(33-$E14>0,COMBIN(36-$E14,3),0)-IF(34-$F14>0,COMBIN(36-$F14,2),0)-IF(36-$G14>0,COMBIN(36-$G14,1)) | |
| J15 | =COMBIN(36,5)-IF(32-$C15>0,COMBIN(36-$C15,5),0)-IF(32-$D15>0,COMBIN(36-$D15,4),0)-IF(33-$E15>0,COMBIN(36-$E15,3),0)-IF(34-$F15>0,COMBIN(36-$F15,2),0)-IF(36-$G15>0,COMBIN(36-$G15,1)) | |
| J16 | =COMBIN(36,5)-IF(32-$C16>0,COMBIN(36-$C16,5),0)-IF(32-$D16>0,COMBIN(36-$D16,4),0)-IF(33-$E16>0,COMBIN(36-$E16,3),0)-IF(34-$F16>0,COMBIN(36-$F16,2),0)-IF(36-$G16>0,COMBIN(36-$G16,1)) | |
| K11 | =COMBIN(36,5)-IF(32-$L11>0,COMBIN(36-$L11,5),0)-IF(32-$M11>0,COMBIN(36-$M11,4),0)-IF(33-$N11>0,COMBIN(36-$N11,3),0)-IF(34-$O11>0,COMBIN(36-$O11,2),0)-IF(36-$P11>0,COMBIN(36-$P11,1)) | |
| K12 | =COMBIN(36,5)-IF(32-$L12>0,COMBIN(36-$L12,5),0)-IF(32-$M12>0,COMBIN(36-$M12,4),0)-IF(33-$N12>0,COMBIN(36-$N12,3),0)-IF(34-$O12>0,COMBIN(36-$O12,2),0)-IF(36-$P12>0,COMBIN(36-$P12,1)) | |
| K13 | =COMBIN(36,5)-IF(32-$L13>0,COMBIN(36-$L13,5),0)-IF(32-$M13>0,COMBIN(36-$M13,4),0)-IF(33-$N13>0,COMBIN(36-$N13,3),0)-IF(34-$O13>0,COMBIN(36-$O13,2),0)-IF(36-$P13>0,COMBIN(36-$P13,1)) | |
| K14 | =COMBIN(36,5)-IF(32-$L14>0,COMBIN(36-$L14,5),0)-IF(32-$M14>0,COMBIN(36-$M14,4),0)-IF(33-$N14>0,COMBIN(36-$N14,3),0)-IF(34-$O14>0,COMBIN(36-$O14,2),0)-IF(36-$P14>0,COMBIN(36-$P14,1)) | |
| K15 | =COMBIN(36,5)-IF(32-$L15>0,COMBIN(36-$L15,5),0)-IF(32-$M15>0,COMBIN(36-$M15,4),0)-IF(33-$N15>0,COMBIN(36-$N15,3),0)-IF(34-$O15>0,COMBIN(36-$O15,2),0)-IF(36-$P15>0,COMBIN(36-$P15,1)) | |
| K16 | =COMBIN(36,5)-IF(32-$L16>0,COMBIN(36-$L16,5),0)-IF(32-$M16>0,COMBIN(36-$M16,4),0)-IF(33-$N16>0,COMBIN(36-$N16,3),0)-IF(34-$O16>0,COMBIN(36-$O16,2),0)-IF(36-$P16>0,COMBIN(36-$P16,1)) | |
| L11 | =37-G11 | |
| L12 | =37-G12 | |
| L13 | =37-G13 | |
| L14 | =37-G14 | |
| L15 | =37-G15 | |
| L16 | =37-G16 | |
| M11 | =37-F11 | |
| M12 | =37-F12 | |
| M13 | =37-F13 | |
| M14 | =37-F14 | |
| M15 | =37-F15 | |
| M16 | =37-F16 | |
| N11 | =37-E11 | |
| N12 | =37-E12 | |
| N13 | =37-E13 | |
| N14 | =37-E14 | |
| N15 | =37-E15 | |
| N16 | =37-E16 | |
| O11 | =37-D11 | |
| O12 | =37-D12 | |
| O13 | =37-D13 | |
| O14 | =37-D14 | |
| O15 | =37-D15 | |
| O16 | =37-D16 | |
| P11 | =37-C11 | |
| P12 | =37-C12 | |
| P13 | =37-C13 | |
| P14 | =37-C14 | |
| P15 | =37-C15 | |
| P16 | =37-C16 | |
| Q11 | =LEFT(H11,3) | |
| Q12 | =LEFT(H12,3) | |
| Q13 | =LEFT(H13,3) | |
| Q14 | =LEFT(H14,3) | |
| Q15 | =LEFT(H15,3) | |
| Q16 | =LEFT(H16,3) | |
| R11 | =(LEFT(K11,3))+0 | |
| R12 | =(LEFT(K12,3))+0 | |
| R13 | =(LEFT(K13,3))+0 | |
| R14 | =(LEFT(K14,3))+0 | |
| R15 | =(LEFT(K15,3))+0 | |
| R16 | =(LEFT(K16,3))+0 | |
