Assistance with excel formula

[marabona]

I am creating a P+L and need a formula for this instance. I am reasonably
profficient on this software but have never had to generate a spreadsheet
like this.

Could someone advise a formula for this instance:

I have a units sold column which generates the rest of the P+L, but there
are some variable fields. 40,000 units are ordered no matter what, and then
any amount over this would require a reorder, but this can only be done in
multiples of 2,500. so if I enter 51,000 in the units sold field, i need the
order costs to calculate an order of 52,500 as it would not be possible to
order any less. The unit costs are not variable (ie there are no economies of
scale benefits) so it is just a matter of making sure the units sold amount
generates an order amount which is possible due to the minimum reorder level.

Any assistance on this, even a link for further advice would be much
appreciated.

Lewis

 

[Ron Rosenfeld]

I am creating a P+L and need a formula for this instance. I am reasonably
profficient on this software but have never had to generate a spreadsheet
like this.

Could someone advise a formula for this instance:

I have a units sold column which generates the rest of the P+L, but there
are some variable fields. 40,000 units are ordered no matter what, and then
any amount over this would require a reorder, but this can only be done in
multiples of 2,500. so if I enter 51,000 in the units sold field, i need the
order costs to calculate an order of 52,500 as it would not be possible to
order any less. The unit costs are not variable (ie there are no economies of
scale benefits) so it is just a matter of making sure the units sold amount
generates an order amount which is possible due to the minimum reorder level.

Any assistance on this, even a link for further advice would be much
appreciated.

Lewis

=ceiling(51000,2500)

--ron

 

[Dave Mills]

=ceiling(51000,2500)

Or
=MAX(CEILING(A2,2500),40000)
if the minimum reorder is 40000 and A2 is the units sold

 

[marabona]

Or
=MAX(CEILING(A2,2500),40000)
if the minimum reorder is 40000 and A2 is the units sold

Thanks guys: made my life easier

 

[Andrew Rowan]

Hi
I have created a quote form in Excel and only add data in the "Quantity"
column
if a quantity if required. Trouble is the whole form is 6 pages long and
sometimes
I only need to show a few lines that are quoted on.
I would like a formula that would delete the rows that have no quantity in
that field.
e.g "if quantity is 0 (or <1) then delete row".
I cannot sort on "quantity" then delete all rows below as this would upset
my product grouping
At the moment, I am deleting rows manually, which takes too much time.
any help appreciated
Andrew
Wellington Point
Australia

 

[Don Guillett]

To do this manually
Data>filter>autofilter>value is>>>>>