Tough Formula Question

[shelfish]

Scenario: Sheet with db output regarding product sales. Second sheet w/
trend line formulas to predict how many to order for the next month.
Works fine.

Here's the poblem. Lets say it predicts I sould purchase 4 bottles of
coke. Coke is only sold in incriments of 6 bottles. How to I make it
round up by incriments of order number.

The order incriments are part of the db output so that should help. I'm
pretty sure I'll always want to round up. This cannot be a vb problem.
It needs to be a formula.

Thanks for any help and good luck.

 

[Roger Govier]

Hi

Try =CEILING(number,order_size)

=CEILING(8,6) will yield an answer of 12

 

[Ardus Petus]

Assuming Product name is in col. A in both sheets
db output sheet column B contains increment
trend line sheet column B contains predicted quantity

Enter formula in trend line col. C:
=ROUNDUP(B2/VLOOKUP(A2,'db output'!A2:B20,2),0)*VLOOKUP(A2,'db
output'!A2:B20,2)

See example: http://cjoint.com/?fDsboKsmJT

HTH

 

[Dave Peterson]

Maybe you could use =ceiling().

=ceiling(a1,6)
or
=ceiling(yourformula,6)

 

[shelfish]

Many thanks to you both. I wasn't aware of the ceiling function but it
does exactly what I need.

 

[Ragdyer]

Then you also might be interested in Floor().<bg>

 

[Ardus Petus]

I did not even know the existence of CEILING function!
Thanks to Roger & Dave!

Cheers,