Tough Formula Question

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.

Hi

Try =CEILING(number,order_size)

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

--
Regards

Roger Govier


"shelfish" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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
--
AP

"shelfish" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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.
>

Maybe you could use =ceiling().

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



shelfish wrote:
>
> 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.

--

Dave Peterson

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

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

Cheers,
--
AP

"shelfish" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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.
>

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ardus Petus" <[email protected]> wrote in message
news:[email protected]...
> I did not even know the existence of CEILING function!
> Thanks to Roger & Dave!
>
> Cheers,
> --
> AP
>
> "shelfish" <[email protected]> a écrit dans le message de news:
> [email protected]...
> > 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.
> >
>
>