Forum Widgets
Latest Discussions
Edexcel solver help
Hello, I'm working on a scheduling model in Excel using Solver and I'm encountering issues with setting up the decision variables and constraints correctly. My workbook is divided into several sheets, and my goal is to maximize student satisfaction based on their subject preferences. Here’s an overview of my model: Students Sheet: Contains Student IDs and four columns for subject preferences (one per block). Additionally, I have helper columns (for example, F, G, H, I) that sum the number of assignments for each student per block. Classes Sheet: Lists all class offerings with columns for ClassID, Subject, Maximum Capacity, and a Block column. The Block column is a decision variable where Solver assigns each class to one of four blocks (integer values 1–4). Assignments Sheet: Features a grid where each row represents a student and each column (after StudentID) corresponds to a class (using ClassIDs from the Classes sheet). These cells are binary decision variables (0 or 1) indicating if a student is assigned to a particular class. Satisfaction Sheet: Uses formulas (typically SUMPRODUCT with IF statements) to check, for each student, whether they are assigned to at least one class in each block that matches their corresponding subject preference. It then calculates a “Total Satisfaction” value (for example, 1 if the student has a valid assignment in every block, 0 otherwise) and sums these to form the objective cell. What I'm Trying to Achieve: Objective: Maximize the total number of satisfied students (for example, the sum of satisfaction flags in cell I1 of the Satisfaction sheet). Decision Variables: The Block assignments for classes (for example, the range Classes!D2:D21). The binary assignment grid (for example, the range Assignments!B2:U101). Constraints to Implement: Block Assignment Constraint: Each class’s block must be an integer between 1 and 4. Binary Constraint: All cells in the assignments grid must be binary (0 or 1). Capacity Constraint: For each class, the total number of students assigned (using a helper cell that sums assignments per class) must not exceed the class's maximum capacity. Student Assignment per Block: For each student, helper cells (using SUMPRODUCT) calculate the number of assignments in each block; each must equal 1 (ensuring that every student gets one class per block). Issue Encountered: When I try to enter the “By Changing Variable Cells” in Solver, I get errors stating that my range reference is not valid—likely because I'm trying to specify noncontiguous ranges (Classes!D2:D21 and Assignments!B2:U101) in one entry. I'm not entirely sure how to correctly input these ranges and constraints in Solver's interface without causing errors. My Request: Could someone please provide detailed instructions or corrections on: The exact syntax for entering noncontiguous ranges (or a workaround) in the “By Changing Variable Cells” field. How to set up each constraint in the Solver dialog, including the correct references and options (for example, ensuring variables are marked as integer or binary). Any help or sample screenshots of how you set up a similar model in Excel Solver would be greatly appreciated!
Filter Function
Morning I am using Microsoft Office 365, suddenly the Function "Filter" is Excel is not working. There is an expression (_xlfn._xlws) appeared in front of a formula. I searching on this expression and appeared that the function is not supported. What is the alternative? I have this formula in my excel file currently: =IFERROR(SUM(_xlfn._xlws.FILTER('BB'!C12:C104,'BB'!E12:E104='CC'!B4)),"") ThanksWhy is MS-Excel adding an "@" to my formula and breaking it???
Hello Excellers, This is so annoying and it is breaking my formula. I hope someone has a good solution: In VBA I have these two lines: Range("N1").Value = "3/24/2025" Range("N2").Formula = "=INDEX(" & Range("E3:E" & lLastRow).Address & ",MATCH(MIN(ABS(" & Range("E3:E" & lLastRow).Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False) & "-$N$1)),ABS(" & Range("E3:E" & lLastRow).Address & "-$N$1), 0))" Now in N2 I get this formula: =INDEX($E$3:$E$1029,MATCH(MIN(ABS(@E3:E1029-$N$1)),ABS(@$E$3:$E$1029-$N$1), 0)) And I get an error in cell N2 and I will need to remove the @s and it works ad needed. This works and this is what I need and this is what the VBA code is supposed to put in cell N2. =INDEX($E$3:$E$1029,MATCH(MIN(ABS(E3:E1029-$N$1)),ABS($E$3:$E$1029-$N$1), 0)) This formula is going to give me the closest date after what I put in N1, and it works very well, I just need to tell Excel not to add the unwanted @ signs! Is that because E3:E $ lLastRow is considered an array? Thanks for any help! GiGi
Got in over my head, now I need formulas to get the right data.
A program creates an excel spreadsheet that has 2 columns, I need to know out of the 51 possible location points in column A, which ones are missing. And list those in say column D. This sample is obviously missing -001 and -002 so I'll know the result is correct when at least those 2 are there. Thank You, Walt -028-Floral Shop Wooden Post-028 37 -014-Sub Shop/Pizza Area-014 35 -021-Bakery Cold Case Area-021 34 -026-Produce Cold Case Salad Pkg Area-026 34 -025-Produce Cut Fruit/Veggie Area-025 33 -023-Produce Cut Veggies Case-023 33 -027-Produce Cold Case Organic Case Area-027 33 -024-Floral Portable Cutting Machine-024 32 -015-Marche/Soup Area-015 31 -010-Indoor Seating Area #2-010 29Power Query by Default Excludes First Blank Column
I have researched this down the rabbit hole long enough without any answers. In Excel I created a Power Query Get Data from Sharepoint Folders using the Contents method. There are about 10 files where the data is in the same format, columns match sheets are all the same name, etc.... FYI, None of them are setup as Tables for reasons I won't go into. They just can't. When I import the files 5 of the files have data in column A while 5 do not. When I import the data the files that do not have data in column A Power Query is automatically removing those columns from those sheets. Which then screws up the column order when the data is appended and loaded to the table. How can I force Power Query to bring in Column A even if some files column A are blank?
Calculate "The Wednesday" two weeks before due date
We currently run checks on Wednesday of each week. To show an "anticipated pay date", I want to somehow use the "due date" of the invoice and return the date two Wednesday's prior. Is that even possible? For example, if the due date is Thursday, May 1st, I want it to return Wednesday, April 23rd. If the due date was Tuesday, April 29th or Wednesday, April 30th, I want it to return Wednesday, April 16th. I know how to return "two weeks ago", but since I only want Wednesday's that makes it a little more confusing. Is there a "2 (day of the week) ago" -type formula?use SUMIF for cell in immediate row above
Can someone please help me? I've been using this formula for 2 years and now I need to add another word and row/path to the filter (not sure of correct terminology) =SUMIF($B$2:$B$6,"Tithes*",$G$2:$G$6) Now, I need to add OR "processing*" to "tithes" and it can only be for processing fees in the row directly below tithes & offerings. If "processing*" is in a cell directly below a "tithe*" cell, then it should be included in the formula in K25. (I can figure out K26 & K27 if I can get K25.) Can someone please help me create the correct formula? I would be extremely grateful!
