Ceiling not working due to none sequential numbers

[Jimidibob]

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:

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.

 

[Fluff]

Add an equal sign to the formulae in F13 & G13 like
=SMALL(IF(B1:Q1>=F12,B1:Q1),1)

and don't forget to confirm with Ctrl Shift Enter

 

[Jimidibob]

Absolutely magic! Thanks very much!

 

[Fluff]

Glad to help & thanks for the feedback