Help combining formulas or suggest a better way?

**Cidona** · 01-07-2012, 05:14 PM

Dear Forum,

I was wondering if someone could help me combine formulas that I currently have in the attached worksheet or maybe suggest a better formula. In the sample attached I am trying to find the cheapest way to order paint. There is slight decrease in cost per litre as you order in bulk.
Currently I have a very akward way of getting there (and it hurt just coming up with it

).

The intent is for the user to enter the required #of litres in cell A1 and for the least expensive option be returned in F15 with the breakdown of varies container size quantities in cells E12:F14

I have used fill colors to help see where the 'relevent' results are currently linked. Idealy I could simplify the process by having a formula in E12:E14 that would have the same result.

I think it wil make sense what I'm saying when you see the worksheet.

Thank you for any suggestion you can offer.

**TMS** · 01-07-2012, 10:27 PM

I think this does it:

=($A1-MOD($A1,$A$9))/$A$9*$B$9+(MOD($A1,$A$9)-MOD(MOD($A1,$A$9),$A$8))/$A$8*$B$8+ROUNDUP(($A1-((A1-MOD($A1,$A$9))+(MOD($A1,$A$9)-MOD(MOD($A1,$A$9),$A$8))))/$A$7,0)*$B$7

Regards, TMS

**Ron Coderre** · 01-07-2012, 10:55 PM

Perhaps this approach can be used:

Please Login or Register  to view this content.

Now the extended total is this:

Please Login or Register  to view this content.

Or...if you want one formula that calculates all quantities of all sizes and the final price:

Please Login or Register  to view this content.

Does that help?

**Cidona** · 01-08-2012, 03:19 AM

TMShucks and Ron, Thank you both very much for your solutions. Both work and have helped me gain knowledge on some very useful functions I previously did not know were there, never mind know how to use (namely MOD, INT, CEILING (and now could use FLOOR in the future also).

Funny how you guys can just string these formulas together, I get dizzy

.

TM, I had to break your one down (into cells E12:E14), and use the evaluation formula tool to understand yours properly, but see what your doing now.

Ron, that's a really nice clean way of getting there. I am looking for the break down of containers so was looking for the solution provided in your first 'Code box'.

Thank you both again for your help and hope you have a great 'weekend'

**TMS** · 01-08-2012, 06:11 AM

You're welcome. Thanks for the rep.

Sorry if it was confusing; I thought you wanted one formula?

Regards, TMS

**Cidona** · 01-08-2012, 09:59 AM

Not at all TM. There was good learning in the breaking down for me anyway

. Sorry for the confusion.

I'm afriad I had a couple of issues with the solution and as I have marked this thread as solved and as I think the situation will require other than the combination of formulas I had provided, I have started a new thread titled 'Find best combination of containers to determine least expensive paint order' which can be found here
http://www.excelforum.com/excel-new-...int-order.html

The problems are outlined in the new post so I won't double up here. If anyone can provide further assistance on the new thread, I would be very grateful.