optimization

Hello, MrExcel Team! I've written the below VBA, which works. However I would like to optimize to the MAXIMUM !! :) :) Any suggestions are more than welcome, especially those regarding SPEED !! The idea behind the code is : I have a Database "source" table, that has 1 row per reservation. I...

Example data below to explain a little further- Basically what I need to do is figure out a macro or a formula column which will calculate out the possible outcomes based on the data set that will find the consolidated versions possible. Maximum weight for this example is 20,000 Maximum...

Hey guys, I have a button on one of my worksheets for which the code is related a calculator that is necessary to recalculate returns every time you re-enter assumptions. My question is, in order to use solver to solve the nonlinear optimization problem (I have to solve for two percentages...

Hello, I am trying to solve a customer optimization problem where I have 3 different kinds of customers, each of whom bring in a different amount of revenue and have different costs associated with them. I am really stumped on this and I'm not sure where to begin. The goal is to find the...

I am working on building a tool to filter through and optimize data quickly and simultaneously. At a very high level, my data has vendors that all serve different stores and I am trying to choose which vendors should serve which stores at a lowest cost level, while minimizing the total number of...

Hi, We are a small hair/beauty salon in Brasil. With a lot of researches and useful help (thanks to Peter_SSs and vogel997 from this forum), I have created a Monthly Sales spreadsheet to record the sales, receive payments and give appropriate change (for cash payments). All services are listed...

I am currently working on a project to minimize variance and need some help to see if Excel can optimize this. The way I'm defining variance is (MAX-MIN)/AVG. I have a set of 60 numbers being input into the spread sheet. These 60 numbers need to be separated into 10 groups of 6 such that the...

Hi there, I have created an excel spreadsheet with some fairly extensive VBA code (for my standards anyway), and it seems there are some significant propagation delays. I believe the biggest delay is stemming from a range of cells that have a timestamp recorded when data is entered into the...

Hi Everyone, I can't seem to figure this out. We use four different box sizes - XSmall, Small, Med, Large. I want to figure out how many of each carton we will use in 2015 based on the number of units we're shipping. Each box size can hold the following # of units. X-Small: 4 Units Small: 6...

I have three types of egg a) small b) medium c) large and two types of basket a) $100 which can fit 4 small/medium eggs but only 1 large eggs b) $250 which can fit 12 small/medium eggs but only 4 large eggs. For given number of small , medium and large eggs how can I fit those with least amount...

I am trying to create a Macro to optimize the lengths of one dimensional (1D) stock pipe using VBA and Solver. Hopefully any methods discussed in this thread will address not only my inquiries, but to the rest of the forum as well. In an attempt to keep this thread organized, here is a link to...

I'm trying to create a basic 1D cutting optimizer using Solver in VBA. However, I keep getting hung up when I try to implement Solver in a loop. I've allowed myself a few criteria to follow by, which I believe I have achieved in my code so far. Any help at all would be greatly appreciated...

Dear Excel experts :) I have made a macro that sorts my data in one sheet based on a value in a second sheet and then deletes the rows that doesn't contain the given value. I have tried to enable the macro to consider more than one value when sorting but to no success :confused: The macro...

Hi guys, I have a problem that I find hard to solve and I will thank you for you advise: -I have X options and I need to pick 3 out of them for 1st, 2nd and 3rd place. -Each option is valued as: rate*place probability. -If I chose option A in the 1st place it's unavailable for the 2nd and 3rd...

Hi all, I would like to reach out to your expertise for a very complex problem of which I'm not even sure whether it's solvable using Excel. I work for a logistics company, and have this database of shipments containing a certain amount of pallets. The shipments are divided into groups that...

I am trying to do some constrained optimization in excel with the Solver tool, but I am encountering the following error: "Solver encountered an error value in the Objective Cell or a Constraint Cell" - which is pretty ambiguous. I think I see where the problem is, but I'm not sure why its...

I have a sheet that will have between 50,000 to 80,000 rows. I started writing a macro but it's very slow and I've only processed a few columns of data so far. I'm thinking that perhaps instead of writing the values one at a time I should read them in one column at at time, or put formulas in...

Hello all, I've got a tricky problem for you that requires a clever solution. I am using the LP Solver to maximize the financial outcome of a factory. The quantity produced, Q, is the variable. Constraint: Q >= 0 and Q <= 200 The Revenue, R, is to be maximized. Very simple, but here's the...

Hi all, I am attempting to do some financial analysis for a redevelopment project my company is currently working on - I say attempting because a lot of my modeling skills seem to have escaped me. My first problem is a loan with a change in interest rate after year 5. So for the first 5...

Hello everyone, I am having an issue with optimization and I was wondering if anyone had any suggestions they could throw out to speed my code up. I need to run a Vlookup against 4 massively large spreadsheets. I am using Ozgrid's UDF found here: Vlookup. Excel Vlookup Across Excel...