=ROUNDUP Formula Help

[AJYoung]

Hi, I am working on a maintenance schedule. Every 250, 300, 500, 1500, and 3000 hours a PM is due. I'm trying to make it so as you input the hours for each peice of equipment there is a check for each type due and at what hours it is due. Hours due is where my trouble begins. I can use the roundup formula =250*ROUNDUP(C4/250,0) for the 250 but what about the 300 and 500 hours? Is there a way to roundup several numbers at multiple intervals at the same time in one cell? If so I would appreciate any help.

I'm aiming for something like this.

Serial # Hours Type Due Hours Due
12345 854 300 900

However, when the hours reach 1000 the type due will not say 500 because I can't figure out how to round up to 300 and 500 at the same time.

Thanks in advance.

 

[pbornemeier]

I think the VLOOKUP formula will do what you ask. The table (F2:G6 can be located anywhere, you just have to modify the formula in column C to point there.
Sheet2

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 19px;"><col style="width: 19px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-size: 8pt;"><td> </td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>Hours</td><td>Next PM</td><td> </td><td> </td><td>Table</td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="text-align: right;">6</td><td style="text-align: right;">200</td><td> </td><td> </td><td style="text-align: right;">0</td><td style="text-align: right;">200</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td> </td><td> </td><td> </td><td> </td><td style="text-align: right;">200.1</td><td style="text-align: right;">300</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td> </td><td> </td><td> </td><td> </td><td style="text-align: right;">300.1</td><td style="text-align: right;">500</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td> </td><td> </td><td> </td><td> </td><td style="text-align: right;">500.1</td><td style="text-align: right;">1500</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td> </td><td> </td><td> </td><td> </td><td style="text-align: right;">1500.1</td><td style="text-align: right;">3000</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-family: Arial; font-size: 10pt; background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>C2</td><td>=VLOOKUP(B2,$F$2:$G$6,2,TRUE)</td></tr></tbody></table></td></tr></tbody></table>
Excel tables to the web - Excel Jeanie Html 4

 

[sanrv1f]

Hello and Welcome to the board,

not very clear about the requirement

However, when the hours reach 1000 the type due will not say 500 because I can't figure out how to round up to 300 and 500 at the same time.

what does this mean?

assuming A1:D2 houses the data

D2

=CEILING(B2,C2)

if this does not solve the problem, post back with few more lines of sample data with the expected result

 

[AJYoung]

Phil,

I have been racking my brain for 2 days trying to figure it out myself. It works like a charm with a little modification. I appreciate the fast response. Also, I noticed you were from Va Beach. I lived in Norfolk for some time.

 

[pbornemeier]

Small world - glad I could help