I need a formula that can work out the total price an item will cost me based on - the qty i require, the package sizes the item comes in and the minimum order qty.
So, i may need to buy 50 x item but the package size available is 20 item per package and the minimum buy is 20 x item so i need a formula to work out the total that works out, if the item qty required is more than MOQ and fits the package size then times the unit price by the qty required.
but if the qty required is less than MOQ then times unit price by MOQ
and if the qty required is more than MOQ but doesnt match the package size then it would have to work out the next round up from package size and times that by unit price
so the above example total price would be the unit price x 60 as the qty required is 50 but the pack size is 20 so the next amount up in that pack size would be 60 (3x packs of 20) and 50 is greater than the MOQ (being 20)
and i cannot do this manually as i have hundreds of rows of data across multiple worksheets
I've found a few suggestions elsewhere, being IF, COUNTIF, SUMIF, CEILING formulas etc but nothing seems to take everything into account and i have no idea how to put anything together myself
attached example:
Example.xlsx
Bookmarks