-1

I need help urgently for a work problem.

So basically, the summation of opening stock and production give total stock. While closing stock is ((opening stock+production)-sale)) and stock cover is Closing stock/Sale.

I need help in creating a formula using excel to make sure my stock cover equates to 1.5 without manually inputting production. For example, I want a formula that determines production with values rounded up to the nearest 500 which result in a stock cover of 1.5.

Improve this question
3
  • What have you tried exactly? You can calculate the summation by using Sum formula.
    – Ramhound
    Jul 2, 2015 at 13:32
  • Theres already a summation. and unfortunately I cannot post the picture
    – Lordnonso
    Jul 2, 2015 at 13:32
  • Well, you could post all the equations, one at a line.
    – Dmitry Grigoryev
    Jul 2, 2015 at 15:20

1 Answer 1

Reset to default
0

Substitute the cell reference for the names, so "Sales" below is the cell where this is, which might be something like A1, similarly "Open Stock" might be B1.

production = sales * 2.5 - opening stock

This does not fix the rounding up to the nearest 500. For that try

rounded_up_production = ROUNDUP(production/500,0)*500

And for the zero bounds

if(rounded_up_production<0,0,rounded_up_production)

Or combine them into one, fairly horrible formula

rounded_up_production = IF(ROUNDUP((sales * 2.5 - open stock)/500,0)*500<0,0,ROUNDUP((sales * 2.5 - open stock)/500,0)*500)
Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .