Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

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!

Posted on Jun 28, 2020 2:44 PM

Reply
Question marked as Top-ranking reply

Posted on Jun 29, 2020 3:08 AM

Hi d,


I've not read your lengthy formula very closely, but it appears to be something that coule be solved more simply with INDEX and MATCH.


Implied in your 'blanks' question: You need to purchase the smallest quantity equal to or more than the required quantity.

VLOOKUP can be set to require an exact match for the value searched, or to accept a 'close value'. 'Close value' in this case means "the largest value less than or equal to the searched value, which is the oposite of what you need.


MATCH is more versatile. It can be set to requirea n exact match, to accept the largest value less than or equal to the search value, or to accept the smallest value greater than or equal to the search value.


For your cypress order, your lookup table for cypress would look like this:



Table 1, on the left, is a portion of your order table showing only amounts needed (in mL) in column A, the smallest quantity avaiabe that will fit that order in column B, and the price (code) for that quantity.


The forula below the table is in B2, and filled down and right from there. Filled into B2, the first rfernce to Table 2::A changes to Table 2::C where INDEX will find the 'price' of the quantity shown in column A of Table 2.


Regards,

Barry

5 replies
Sort By: 
Question marked as Top-ranking reply

Jun 29, 2020 3:08 AM in response to dfelsteadfx

Hi d,


I've not read your lengthy formula very closely, but it appears to be something that coule be solved more simply with INDEX and MATCH.


Implied in your 'blanks' question: You need to purchase the smallest quantity equal to or more than the required quantity.

VLOOKUP can be set to require an exact match for the value searched, or to accept a 'close value'. 'Close value' in this case means "the largest value less than or equal to the searched value, which is the oposite of what you need.


MATCH is more versatile. It can be set to requirea n exact match, to accept the largest value less than or equal to the search value, or to accept the smallest value greater than or equal to the search value.


For your cypress order, your lookup table for cypress would look like this:



Table 1, on the left, is a portion of your order table showing only amounts needed (in mL) in column A, the smallest quantity avaiabe that will fit that order in column B, and the price (code) for that quantity.


The forula below the table is in B2, and filled down and right from there. Filled into B2, the first rfernce to Table 2::A changes to Table 2::C where INDEX will find the 'price' of the quantity shown in column A of Table 2.


Regards,

Barry

Reply

Jun 29, 2020 4:15 AM in response to Barry

That's a far simpler formula to the one I was trying! Thank you very much Barry.


My next query;


Not all of the ingredients are available in all quantities.


EG: I know that I need at least 6ml, but I can't purchase 10ml. I have to purchase at least 20ml.


How can I get it to return the next available quantity, in this case Price code c? Do I need to use the ISBLANK function?



Likewise, if I know I need 46ml of an ingredient, can I get it to jump to the price code for 200ml?


Thank you

Reply

Jun 30, 2020 5:37 AM in response to Barry

I've been experimenting with workarounds and have found something that works for this purpose.


I simply populated all cells with the closest relevant cost.


Green cells represent the ingredients that are available in given quantities.

Orange cells represent quantities aren't available from suppliers but represent cost of purchasing equivalent amount using smaller units.

Light green represents the price based on the minimum quantity available

Red cells are if no ingredients are required (this helps populate other cells in other tables)




The table below is populated with information linking to various other tables and cells.


Ingredients in green correlate to 9 separate tables indicating which ingredients are needed to create that specific product.


I can see that both Juniper Berry and Lemon are required for the desired product (indicated by a single cell table with the product list in a drop-down menu; not shown here). I took the same single cell drop down approach to indicate number of products needed. The table below is for 50 units.


It looks up and returns the column containing the ml per unit (col 3), ml required for desired number of products (col 4), the lowest amount of ingredient available to purchase (col 5), (Thanks to your code Barry) and total cost for that amount (col 6); price per unit (col 7)




I also have a separate table of which ingredients are used for which product, that returns a TRUE or FALSE statement, in a hidden table to the left of the ingredients in the table above. I hope that all makes sense.


This all works for my needs, but wondered if there is a simpler way? I feel like I've made it all too long winded and complicated.


With regards to the ignoring blank cell, that would be useful when using INDEX & MATCH, to just move along the row to the next populated cell if the current cell is blank. Perhaps that's a function Numbers can introduce!


Thanks again Barry,



Reply

Jun 30, 2020 12:56 AM in response to dfelsteadfx

"Likewise, if I know I need 46ml of an ingredient, can I get it to jump to the price code for 200ml?"


Only if you eliminate the 50 and 100 values from the MATCH column.


Will take another look at this. Meantime, can you provide a list of three ingredients and the list of container sizes in which those ingredients are available. The set of container sizes should be different for each ingredient, but be consistent in the unit used.


Regards,

Barry

Reply

Jul 1, 2020 1:55 AM in response to dfelsteadfx

"I've been experimenting with workarounds and have found something that works for this purpose.


"I simply populated all cells with the closest relevant cost."


I think that was the solution I was gravitating towards as well, but ran into a couple of full days where I got to the computer quite late.


"With regards to the ignoring blank cell, that would be useful when using INDEX & MATCH, to just move along the row to the next populated cell if the current cell is blank. Perhaps that's a function Numbers can introduce!"


MATCH does ignore empty cells when searching for a non zero value. I've not tested it when searching for zero, though.


Using that knowledge to make a two column lookup table for each ingredient would allow filling in only the rows where the volume was available, and leaving both columns empty for volumes which were not available. The complications there arise is telling MATCH which column to search and INDEX which column is being indexed.


Using a single search column (or row, as in your example) with price entries for only those rows where the volumn number is an available volume for that ingredient requires recalculation of the 'MATCH' + adjustment value to provide to INDEX for the second (and possibly third, fourth…) attempt. Doable, but the nested IFs necessary make filling ALL cells in the price column (or row) with the price of the smallest available quantity required to fill the amount needed of that ingredient.


Regards,

Barry



Reply

Skip blank cells to next non-blank cell/column. Calculate costs depending on certain values

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.