# Formula issue Microsoft Excel

by Lordnonso   Last Updated July 02, 2015 16:00 PM - source

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.

Tags :

#### Answers 1

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)
``````
Saulysw
July 02, 2015 14:00 PM