Formula issue Microsoft Excel

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

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.



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

Related Questions


Listbox, mouse wheel scroll issue

Updated October 22, 2015 08:00 AM

If Difference Higher or Lower than Display

Updated August 03, 2015 15:00 PM


How to fix Excel Formula

Updated July 12, 2017 09:01 AM