Rounding based on looking up decimals in an array

[mikeronni]

I have a table of numbers:
00 minutes 0
05 minutes 0.08
10 minutes 0.17
15 minutes 0.25
20 minutes 0.33
25 minutes 0.42
30 minutes 0.5
35 minutes 0.58
40 minutes 0.67
45 minutes 0.75
50 minutes 0.83
55 minutes 0.92

As you can see, each decimal reflects the portion of minutes per hour.
If I have 36.39 hours and I want to round that to the 5 minute increment (36 hours and 20 minutes), it needs to be 36.42. I always need to round to the closest number (up or down). Also, it may mean rounding to the nearest hour, which may increase the hour value.

I tried =round(cell containing 36.39, vlookup referring to cell and array above) to no avail.
Can you suggest another method?

 

[mgirvin]

Here are a few options. Maybe these will help:

Data and formulas (formulas in green):

Excel Workbook
	A	B	C	D
1	1	60	minutes
2	0.92	55	minutes
3	0.83	50	minutes
4	0.75	45	minutes
5	0.67	40	minutes
6	0.58	35	minutes
7	0.5	30	minutes
8	0.42	25	minutes
9	0.33	20	minutes
10	0.25	15	minutes
11	0.17	10	minutes
12	0.08	5	minutes
13	0	0	minutes
14
15	source	Normal Rounding To 5 Mins	Round Up to 5 Mins	Rounded according to lookup table
16	36.39	36.42	36.42	36.42
17	39.95	39.92	40	40
18	36.125	36.17	36.17	36.17
19	37.124	37.08	37.17	37.17
20	38.167	38.17	38.25	38.17
21	40	40	40	40
22	0.33	0.33	0.33	0.33
...

Excel Workbook
	B	C	D
16	36.42	36.42	36.42
...

Cell Formulas
Range	Formula
B16	=ROUND(INT(A16)+MROUND(MOD(A16,1)*60,5)/60,2)
C16	=ROUND(INT(A16)+CEILING(MOD(A16,1)*60,5)/60,2)
D16	=ROUND(INT(A16)+INDEX($B$1:$B$13,MATCH(ROUND(MOD(A16,1),2),$A$1:$A$13,-1))/60,2)

 

[shg]

=MROUND(36.39, 1/12) ~ 36.417 (requires the Analysis ToolPak for Excel 2003-)

Or

=ROUND(36.39*12, 0)/12

 

[mikeronni]

Sheer Excel Genius!
I'm in awe right now.

 

[mgirvin]

If you are after the integer and decimal part rounded to five minutes, this is Excel mastery:

=MROUND(36.39, 1/12)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
=ROUND(36.39*12, 0)/12<o></o>