optimization

Hello, Im currently looking to improve the calculation speed of my worksheet either through formula optimization or alternative formulas (No complicated VBA or PQuery). I have a workbook "File A" that contains Tabs "Main" and "01" through "19". "Main" Tab is an interface which includes...

Hello, I have used solver to optimize the sum of numbers given certain constraints, however I am now trying to optimize the product of numbers given certain constraints. When finding the optimal sum I can use a binary list to select the combination of item to sumproduct together to find the...

Hi everybody. I'm trying to make my workbook lighter to calculate, but I have a question. Is lighter to use a named formula a few times in the same row, or should I use helper columns and compare the result in the other cells? For example 1: Using named formula in all cells "XYZ" that it's...

Hello all. I am trying to speed up some code that I have written. What I have works perfectly fine, but is quite slow. I would like to see if there is a way to increase the speed. My workbook consists of 3 sheets. The first tab (named "Access Data") contains a table (named "tblData") with 5...

I have a relatively long macro which I am trying to optimize for efficiency. In a number of places, there is code such as: ' Format Name header If ActiveSheet.Name <> "Toezicht" Then With Range("A1") .Interior.ThemeColor = xlThemeColorDark2...

Hi, I am quite new here so I am not exactly sure how everything works. I just wanted to know if anyone could help me with Solver? -Given some information, I need to construct a spreadsheet and use Solver to resolve a constrained optimization problem (may be simple for some but it is my first...

Hello, I am trying to generate all combinations of sums taking 5 values from one set of numbers, and 1 value from another set of numbers, with an additional constraint. I am trying to determine the combinations of 5 "Drivers and 1 "Team" that will generate the highest sum of 'Value' with the...

I have a table with Bill of Material information in four columns; Parent PN, Child PN, Quantity, Units of Measure. Given a parent part number as an input I am returning the "exploded" or "indented" bill of material. Essentially that means I am returning all the children of the parent, all the...

Hi, I am trying to calculate the optimal way to load containers. I have the current amount of fuel in each container and I have the amount of fuel I know will burn in aggregate prior to reaching my terminus where I will refuel the containers. I can choose which container I burn fuel from. I...

I have two VBA codes that contain multiple formulas for different cells, including array formulas. These codes work, however they run VERY slowly. Any tips on how to speed this up? The first code essentially enters formulas into a row of cells, and then copies the formula down (there are no...

I currently have two if then VBA codes, that are running extremely slow for my large data set and am looking for ways to optimize and speed them up. The first formula is looking in a range of cells in column J that have a value in column A, and if they are blank in J then entering in a formula...

Hi excelers, I am here to post a problem which is beyond my excel skill level. I need to plot a dataset within a 5x5 cell window made up of "1" (see attached screen). Criteria: The formula should be easily copy/paste down to calculate previous below rows of data, and be the simplest & fastest...

I use solver a lot in my career. However, solver breakdowns when the function is a step function. I am attempting to write an optimization for a step function (a function that increases or decreases abruptly from one constant value to another.) and need some help with the loop. Function_Range =...

Hi, I'm working on a project relating to times that cars leave for a hub route. The cars are carrying ordered products from one store to the other within the route. I have data with the current times the cars leave, 6 times per day, and I have data of what time were ordered by the individual...

Hello, I have a quite big file of 30,000+ rows,. It's full of sumproduct formulas, It basically checks prices in sheet1 and adds them up in sheet2 to check how much employee earned and based on that what is going to be his salary(10% above minimum to earn + add on). My only problem with...

The picture below shows a simple example of the problem I'm facing. I'd like to maximize a cell that is the sum of other cells that are not linear. In the spreadsheet, I have Z scores for categories I and II (I hardcoded in a mean of 30 and stdev of 15 for category I and 40 and 10 for category...

Hello fellows, this is my first post so even though I read the recommendations I am sorry if I make any posting mistakes. I was assigned to optimize a very old Excel 2003 / VBA file. This is a huge file with literally thousands of controls (Labels, Textboxes, ListBoxes, CommandButtons, etc). It...

Colleagues, I have faced the following issue: I need to perform similar calculations on different inputs, saving the output. Is there any way to use Excel workbook as framework for those calculations? For example, I need to perform A + B calculation. I created workbook WB1, which contains on...

Hi, I am in a tough spot for a deadline next week:( and would so greatly appreciate any help with this optimization problem. I think using Solver is the way to go but I'm stuck. I have to come up with the most cost effective way to store items in any assortment of warehouses over a 15 year...

Sub Testingloop() Dim endrown As String Dim ex As String Dim ez As String Dim eh As String Dim eg As String Dim el As String Dim ee As String Dim es As String Dim ef As String Dim ei As String Dim i As Integer Dim LastRowColumnA As Long: LastRowColumnA = Sheets("looping").Cells(Rows.Count...