Ceiling not working due to none sequential numbers

Jimidibob

New Member
Joined
Sep 25, 2019
Messages
6
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:
ABCDEFGHIJKLMNOPQR
1 2250250027503000325035003750400042504500475050005250550057506000
22000£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
32125£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
42250£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
52350£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
62500£1.00£2.00£3.00£4.00£5.00£6.00£7.00£8.00£9.00£10.00£11.00£12.00
72750£1.00£2.00£3.00£4.00£5.00£6.00£7.00£8.00£9.00£10.00
82960£1.00£2.00£3.00£4.00£5.00£6.00£7.00£8.00
9
10
11WIDTHHEIGHT
12QUOTE24152515
13ROUND UP25002625
14
15ROW NUMBER#N/A
16COLUMN NUMBER2
17
18ANSWER#N/A
19
20ANSWER#N/A

<colgroup><col><col><col><col><col span="2"><col><col><col span="3"><col span="7"></colgroup><tbody>
</tbody>

With Formulas:
ABCDEFGHIJKLMNOPQR
1 2250250027503000325035003750400042504500475050005250550057506000
2200012345678910111213141516
32125123456789101112131415
422501234567891011121314
5235012345678910111213
62500123456789101112
7275012345678910
8296012345678
9
10
11WIDTHHEIGHT
12QUOTE='Main Sheet'!B10='Main Sheet'!B11
13ROUND UP=CEILING(G13,250)=CEILING(H13,125)
14
15ROW NUMBER=MATCH(H14,$B$3:$B$9,0)
16COLUMN NUMBER=MATCH(G14,$C$2:$R$2,0)
17
18ANSWER=INDEX(C3:R9,H16,G17)
19
20ANSWER=ROUNDUP(H19,2)

<colgroup><col><col span="2"><col><col span="2"><col><col><col span="10"></colgroup><tbody>
</tbody>


Thanks in advance Dan.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi & welcome to MrExcel.
Is the width in col B & height in row 2, or the other way round?
Also do you need to lookup the nearest higher number or nearest lower number?
Finally is it possible to change the layout of your sheet, or is that fixed?
 
Upvote 0
How about


Book1
BCDEFGHIJKLMNOPQR
22250250027503000325035003750400042504500475050005250550057506000
32000£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
42125£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
52250£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
62350£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
72500£1.00£2.00£3.00£4.00£5.00£6.00£7.00£8.00£9.00£10.00£11.00£12.00
82750£1.00£2.00£3.00£4.00£5.00£6.00£7.00£8.00£9.00£10.00
92960£1.00£2.00£3.00£4.00£5.00£6.00£7.00£8.00
10
11
12WIDTHHEIGHT
13QUOTE24152515
14ROUND UP25002750
15ANSWER2
Form
Cell Formulas
RangeFormula
H15=INDEX(C3:R9,MATCH(H14,B3:B9,0),MATCH(G14,C2:R2,0))
H14{=SMALL(IF(B3:B9>H13,B3:B9),1)}
G14{=SMALL(IF(C2:R2>G13,C2:R2),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Hi Fluff,

Thanks for your message.

Context:
It will be used for pricing purposes so it always needs to use the next highest dimension that is priced.
Dimensions along the top are widths
Dimensions down the side are heights

I need the formula to jump up to the next highest size for example if the height dimension was 2400mm I need the spreadsheet to look up the 2500 row.
i.e.
2890w x 2800h would = £4.00 (according to the table below)

With Formulas and correct column lettering:
ABCDEFGHIJKLMNOPR
12250250027503000325035003750400042504500475050005250550057506000
2200012345678910111213141516
32125123456789101112131415
422501234567891011121314
5235012345678910111213
62500123456789101112
7275012345678910
8296012345678
9
10
11WIDTHHEIGHT
12QUOTE='Main Sheet'!B10='Main Sheet'!B11
13ROUND UP=CEILING(G13,250)=CEILING(H13,125)
14
15ROW NUMBER=MATCH(H14,$B$3:$B$9,0)
16COLUMN NUMBER=MATCH(G14,$C$2:$R$2,0)
17
18ANSWER=INDEX(C3:R9,H16,G17)
19
20ANSWER=ROUNDUP(H19,2)

<tbody>
</tbody>
 
Upvote 0
Did you try the formulae I suggested?
 
Upvote 0
Hi Fluff,

Yes - unfortunately it goes to the lower value - not the higher value.

Ideally it needs like a roundup function but only to round up to a specific value.
 
Upvote 0
With the example I posted in post#3 it's getting the value from cell D9 and if I change it to 2890w x 2800h it pulls the value from F9.

In what way is this wrong?
 
Upvote 0
With the example I posted in post#3 it's getting the value from cell D9 and if I change it to 2890w x 2800h it pulls the value from F9.

In what way is this wrong?

Hi Fluff,

It's worked - I crossed one of the references when editing the cells it's referencing.

Thanks so much - legend!
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
Hi Fluff,

Upon closer inspection it looks like even if it is equal to the value it jumps to the next bracket. Do you have any idea how i can prevent this?

ABCDEFGHIJKLMNOPQ
12250250027503000325035003750400042504500475050005250550057506000
22000£205.45£208.18£211.82£214.55£217.27£220.91£223.64£225.45£231.82£233.64£238.18£244.55£249.09£249.09£252.73£258.18
32125£207.27£209.09£211.82£216.36£219.09£222.73£224.55£227.27£232.73£234.55£239.09£245.45£250.00£250.00£253.64n/a
42250£209.09£210.91£214.55£220.00£221.82£224.55£230.00£230.00£235.45£240.00£240.00£251.82£251.82£256.36n/an/a
52350£210.00£211.82£215.45£221.82£223.64£228.18£230.91£231.82£241.82£240.91£242.73£252.73£257.27n/an/an/a
62500£216.36£218.18£222.73£228.18£230.91£237.27£237.27£242.73£245.45£247.27£251.82£265.45n/an/an/an/a
72750£220.91£224.55£228.18£233.64£236.36£244.55£244.55£250.91£253.64£254.55n/an/an/an/an/an/a
82960£226.36£229.09£233.64£239.09£241.82£248.18£251.82£254.55n/an/an/an/an/an/an/an/a
9
10
11WIDTHHEIGHT
12QUOTE25002125
13
NEW BRACKET SIZE27502250
14
15
16ANSWER214.5455
17
18ANSWER ROUNDED214.55

<tbody>
</tbody>

ABCDEFGHIJKLMNOPQ
1 2250250027503000325035003750400042504500475050005250550057506000
22000205208211214217220223225231233238244249249252258
32125207209211216219222224227232234239245250250253n/a
42250209210214220222224230230235240240251251256n/an/a
52350210211215221223228230231241240242252257n/an/a n/a
62500216218222228230237237242245247251265n/an/an/a n/a
72750220224228233236244244250253254n/an/an/an/an/an/a
82960226229233239241248251254 n/a n/a
n/a n/an/an/an/an/a
9
10
11WIDTHHEIGHT
12QUOTE='Main Sheet'!B10='Main Sheet'!B11
13
NEW BRACKET SIZE=SMALL(IF(B1:Q1>F12,B1:Q1),1)=SMALL(IF(A2:A8>G12,A2:A8),1)
14
15
16ANSWER=INDEX(B2:Q8,MATCH(G13,A2:A8,0),MATCH(F13,B1:Q1,0))
17
18ANSWER ROUNDED=ROUNDUP(G16,2)

<colgroup><col><col><col><col span="2"><col><col><col span="10"></colgroup><tbody>
</tbody>

Thank you!
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top