Find best combination of containers to determine least expensive paint order

Thank you for the reply TM!
Afriad though while your formula does take care of issue (a), issue (b) is still at hand.

As an example, (working with a 950 litre required paint) if the supplier was to have a blow out sale on the 19 litre containers, selling them off at 19 euro a piece (so the difference can be easily seen), then the cheapest option would be to buy all the paint in the 19 litre containers which would be 50 of the 19 litre containers for a total of 950 euro. With your formula the result is 7,533 euro.

Any ideas on how to rectify. This solution will work for the most part as it would be unusal for such a discount, however it would be nice to have a 'fool proof' method.

Thank you again for your consideration.

Thank you very much for the suggestion shg. I have incorporated a manual selection into my worksheet (slightly different configuration but same concept, i.e. multiplying price by user quanty input). I was thinking it would be good to have this for situations where it is not practical to bring a 1,000 litre container into a given project (in the phyical world). However I am trying to make it to prevent users making the wrong selection. There are several other aspects that I have configured for use with this portion of the 'tool', where the user enter's various inputs to dertermine the correct ammount of paint to input in this section. Therefore this is one aspect that if posible I would like to automate/fool proof.

I have attached the updated workbook. This has the user input option incorporated. However in cells B10:B12 the formula is my broken down version of TM's original. If posible I would like to have this populate with the correct optimum numbers for the various ammounts. My only thought on how to approach would be posibily with nested ifs into TM's section of formula for each of those cells (B10:B12), but it seems almost like infinite IFs and I'm not great at stringing together.

Please note the workbook configuration has changed, due to the user input section and a general cleanup. If anyone is able to respond and it is easier to reference the previous workbook please let me know which one you are referencing. I have named the new workbook as 'FindCheapestPaintAdjTMShucks2' rather than .....1.

Any ideas on what to input in B10:B12 to get the automated quantities?
Thank you for your imput.

Had posted inbetween reading shg's 1st response and your last one TM. So hadn't read your response when I posted. I'll will review your solution. Thank you again for your assistance.

Hi TM.
Thank you for very much for your reply.

Let me first say that I didn't mean to be bringing you on such a trip here. When I posted I didn't know if maybe Excel had some built in function for performing such an evaluation or how involved it would be. Alot of what you experts can do with this program seems straight up like magic to me. It seems that there is not such a pre-packaged solution to this and to have the program do such requires some very careful creative math.

I am mentioning this therefore not in expecting a solution (though it you can that would be great), but rather to inform you/others in cases you/other are wishing to utilize this method in a similar situation....In certain circumstances it is still not finding the true cheapest method of ordering (sorry). (Unless I'm missing something).

Your formula are a bit too involved for me to troubleshoot and I'm not sure with what parameters it gets caught off guard. However if you see the attahed worksheet (which has your latest workbook incorportated, the formula is determining the for 2800 litres the least expensive method would be €24,540.00. However by using the 'User Selection' where the quanities are manually input, it would seem the least expensive way of ordering would arrive at €24,400.00 (€140.00 saving).

In the attached workbook I have inserted your 'tool' into rows 1-9, utilzing the lower sections for anaylsis and the user input. The intent would be to hide rows 1-9.

Again I am mentioning this for your information rather than expecting a solution as I see it got quiet involved so far. As your sign off says 'Behind everything simple is a huge tail of complicated'.

BTW, in cells E2:E4, how are you getting the text in there with the number (as it is referencing cells with just numbers)??? Hadn't come accross that before.

Thank you again for your time in this matter.

TM, Thank you so much for your patience over the weekend.

Home free now. The INDEX structure allows me to enter the formulas several ways now (using my newly aquired INT, CEILING, ROUNDUP, ROUNDDOWN and MOD function

Thanks also for the Custom Format tip, I had previously only formatted cells for number/date reasons.

I'm a little surprised that excel doesn't have a function built in to do this type of 'function'. Would seem to be a worthwhile facility for many people/companies. Would seem to really twist the melon if you were dealing with say 10 different container sizes, entering 'custom catch all' formulas for the range in posibilities that could exist with larger number of containers.

I mostly only get to do this excel learning in spare time; until a few years ago I thought excel was just for aligning things in straight columns. Wish someone had opened my eyes way back.

While it can be frustrating trying to get the hang of it, thanks to you and the other generous contributors on this forum the likes of myself can still have fun learning it and can come away with useful worksheet and a bag of tricks for the next one.

Thank you so much!

Hum, I guess I completely missed shg's proposal. I was thinking the intent was for the user to input (manually)in cells C7:C9 with the constraints he mentioned at the end. Upon further investagation, there seems to be more to it. However the Solver add on doesn't let me input shg's 2nd constraint 'C7:C9 = integer'. I get the message 'Constraint must be a number, simple reference, or formula with a numeric value.

Further investagation needed.

Thanks again.

Thanks for the response shg.

However in my attept this doesn't find the least expensive option.

I'll start a new thread and provide a link to this as I have already marked this thread as solved.

Hope you can respond to the new one.

Thanks again!

Thank you very much for your reply shg.

As I had previously marked this thread as solved and since it may be useful for others to search with 'Solver' criteria, I have started a new thread named 'Find cheapest paint with solver'. The link for said new thread is provided below.

It would be great if you can provide further guidance on the new thread as it seems that this could be a powerful tool for dealing with multiple sizes of containers...

Thank you for your previous input and any further direction you can provide.

http://www.excelforum.com/excel-new-...html?p=2680219