Skip blank cells to next non-blank cell/column. Calculate costs depending on certain values
I'm relatively new to Numbers, and have a very basic understanding of a a few of the regularly used functions, but I'm struggling to get my head round something. Please bear with me whilst I try to explain, as I don't know all the correct terms. I have several things I'm trying to solve,
I'm looking for a way to calculate the amount of materials required to order, dependant on the quantity of items that need to be produced.
I have compiled a table of suppliers with prices to each of the ingredients. The header indicates the volume in ml. For instance; 10ml of Cardamom is £4.99. Obviously, the larger the quantity, the better the value for money.
In the table below, I've begun to calculate how many mls are required to produce a desired number of units. What I'm looking for is a simplified way of finding the lowest amount to order and price.
As an example, if I were to make 49 units, I'd need 198.45mls, and will have ti buy at least 200ml for production.
I've used this incredibly lengthy code below to calculate the minimum amount to order for production. Apologies!
IF(ml required for production cypress≤2,CEILING(ml required for production cypress,2),IF(ml required for production cypress≤5,CEILING(ml required for production cypress,5),IF(ml required for production cypress≤10,CEILING(ml required for production cypress,10),IF(ml required for production cypress≤20,CEILING(ml required for production cypress,20),IF(ml required for production cypress≤30,CEILING(ml required for production cypress,30),IF(ml required for production cypress≤50,CEILING(ml required for production cypress,50),IF(ml required for production cypress≤100,CEILING(ml required for production cypress,100),IF(ml required for production cypress≤200,CEILING(ml required for production cypress,200),IF(ml required for production cypress≤250,CEILING(ml required for production cypress,250),IF(ml required for production cypress≤500,CEILING(ml required for production cypress,500),IF(ml required for production cypress≤1000,CEILING(ml required for production cypress,1000),"no")))))))))))
1) How do I simplify this formula?! Have I gone about it in the wrong way?
2) How can I use VLOOKUP or INDEX/MATCH to calculate the price of each ingredient depending on the amount I need to order?
3) Is there a way of skipping blank cells if that volume is unavailable? i.e. if I can't purchase 20ml, can it automatically skip to 30ml? I've read about ISBLANK but not sure how to incorporate it correctly
There will be instances where the amount of ingredients I need is higher than the largest amount I can purchase. Is there a way that can be calculated for the best possible price?
Any guidance in minimising my formulas is greatly appreciated. I feel like there's most certainly a more straightforward way of going about it!
Thank you in advance
PS, It is very unlikely I'll ever be ordering 5L of chamomile!