Hi All,
I'm trying to produce a ceiling formula to match and identify a row number for indexing. But due to the numbers in column B not having a uniformed spacing between them it is proving very difficult. Maybe there is a better system i should use?
Example below:
For reference column B is a dimension in mm, row 1 is a dimension in mm and the data within the table is a sale price in £.
Without Formulas:
<colgroup><col><col><col><col><col span="2"><col><col><col span="3"><col span="7"></colgroup><tbody>
</tbody>
With Formulas:
<colgroup><col><col span="2"><col><col span="2"><col><col><col span="10"></colgroup><tbody>
</tbody>
Thanks in advance Dan.
I'm trying to produce a ceiling formula to match and identify a row number for indexing. But due to the numbers in column B not having a uniformed spacing between them it is proving very difficult. Maybe there is a better system i should use?
Example below:
For reference column B is a dimension in mm, row 1 is a dimension in mm and the data within the table is a sale price in £.
Without Formulas:
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R |
1 | 2250 | 2500 | 2750 | 3000 | 3250 | 3500 | 3750 | 4000 | 4250 | 4500 | 4750 | 5000 | 5250 | 5500 | 5750 | 6000 | |
2 | 2000 | £1.00 | £2.00 | £3.00 | £4.00 | £5.00 | £6.00 | £7.00 | £8.00 | £9.00 | £10.00 | £11.00 | £12.00 | £13.00 | £14.00 | £15.00 | £16.00 |
3 | 2125 | £1.00 | £2.00 | £3.00 | £4.00 | £5.00 | £6.00 | £7.00 | £8.00 | £9.00 | £10.00 | £11.00 | £12.00 | £13.00 | £14.00 | £15.00 | |
4 | 2250 | £1.00 | £2.00 | £3.00 | £4.00 | £5.00 | £6.00 | £7.00 | £8.00 | £9.00 | £10.00 | £11.00 | £12.00 | £13.00 | £14.00 | ||
5 | 2350 | £1.00 | £2.00 | £3.00 | £4.00 | £5.00 | £6.00 | £7.00 | £8.00 | £9.00 | £10.00 | £11.00 | £12.00 | £13.00 | |||
6 | 2500 | £1.00 | £2.00 | £3.00 | £4.00 | £5.00 | £6.00 | £7.00 | £8.00 | £9.00 | £10.00 | £11.00 | £12.00 | ||||
7 | 2750 | £1.00 | £2.00 | £3.00 | £4.00 | £5.00 | £6.00 | £7.00 | £8.00 | £9.00 | £10.00 | ||||||
8 | 2960 | £1.00 | £2.00 | £3.00 | £4.00 | £5.00 | £6.00 | £7.00 | £8.00 | ||||||||
9 | |||||||||||||||||
10 | |||||||||||||||||
11 | WIDTH | HEIGHT | |||||||||||||||
12 | QUOTE | 2415 | 2515 | ||||||||||||||
13 | ROUND UP | 2500 | 2625 | ||||||||||||||
14 | |||||||||||||||||
15 | ROW NUMBER | #N/A | |||||||||||||||
16 | COLUMN NUMBER | 2 | |||||||||||||||
17 | |||||||||||||||||
18 | ANSWER | #N/A | |||||||||||||||
19 | |||||||||||||||||
20 | ANSWER | #N/A |
<colgroup><col><col><col><col><col span="2"><col><col><col span="3"><col span="7"></colgroup><tbody>
</tbody>
With Formulas:
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R |
1 | 2250 | 2500 | 2750 | 3000 | 3250 | 3500 | 3750 | 4000 | 4250 | 4500 | 4750 | 5000 | 5250 | 5500 | 5750 | 6000 | |
2 | 2000 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 |
3 | 2125 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | |
4 | 2250 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | ||
5 | 2350 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | |||
6 | 2500 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | ||||
7 | 2750 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | ||||||
8 | 2960 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ||||||||
9 | |||||||||||||||||
10 | |||||||||||||||||
11 | WIDTH | HEIGHT | |||||||||||||||
12 | QUOTE | ='Main Sheet'!B10 | ='Main Sheet'!B11 | ||||||||||||||
13 | ROUND UP | =CEILING(G13,250) | =CEILING(H13,125) | ||||||||||||||
14 | |||||||||||||||||
15 | ROW NUMBER | =MATCH(H14,$B$3:$B$9,0) | |||||||||||||||
16 | COLUMN NUMBER | =MATCH(G14,$C$2:$R$2,0) | |||||||||||||||
17 | |||||||||||||||||
18 | ANSWER | =INDEX(C3:R9,H16,G17) | |||||||||||||||
19 | |||||||||||||||||
20 | ANSWER | =ROUNDUP(H19,2) |
<colgroup><col><col span="2"><col><col span="2"><col><col><col span="10"></colgroup><tbody>
</tbody>
Thanks in advance Dan.