Forum Widgets
Latest Discussions
Solving an 0 1 Knapsack Problem with LAMBDA
A Knapsack Problem is defined as: Given a set of items, each with a weight and a value, determine which items to include in the collection so that the total weight is less than or equal to a given limit and the total value is as large as possible. -Wikipedia After studying 0 1 Knapsack Problems (A given item may only be selected once) and the techniques used to solve them, I sought to build formulas to solve a standard problem consisting of 5 items with various weights and benefits. I considered generating a value matrix (array of arrays limitation - MAKEARRAY could only go so far) and creating a recursive Lambda (I couldn't conceptualize one and I didn't think it would be efficient memory-wise). The solution I went back to a previous project's technique: LAMBDA Halloween Challenge - Microsoft Community Hub That challenge was all about generating the COMBINA results. The way I see an 0 1 Knapsack is essentially generating COMBIN results and then determining which combination is the greatest benefit without going over the capacity. First, I created the 'Shuffle' function which takes a value produced from SEQUENCE and excludes any numbers >5 (total number of items) and removes values where a number repeats (e.g. 122 and 123 vs 321). Shuffle =LAMBDA(scalar,LET( arr, 1 * MID(scalar, SEQUENCE(, LEN(scalar)), 1), filtered, FILTER(arr, arr <= 5, 0), sorted, SORT(UNIQUE(filtered, 1), , , 1), IF(TAKE(sorted, , 1) = 0, 0, sorted) )) Then I created a function to be called within REDUCE: GenerateCombin =LAMBDA(a,v,VSTACK(a, EXPAND(Shuffle(v), , 5, 0))) This is the most resource intensive part of the project because if SEQUENCE goes into 5 digits, REDUCE will struggle to stack. I added a check to determine if picking all 5 items exceeds the limit so there's no need to go into 5 digits with SEQUENCE! =LET( r, IF(SUM(weight) > cap, 2345, 12345), distinct, UNIQUE(DROP(REDUCE(0, SEQUENCE(r), GenerateCombin), 1)), DROP(SORT(distinct), 1) ) 'Solve' to be called within BYROW. This function checks the CombinMatrix and totals up the Benefit and Weight for each combination. It strings together the results in each cell. =LAMBDA(row,LET( w, SUM(XLOOKUP(row, item, weight, 0)), b, SUM(XLOOKUP(row, item, benefit, 0)), IF( w <= cap, "Items: " & TEXTJOIN(", ", , TEXT(row, "0;;;")) & CHAR(10) & "Benefit: " & b & CHAR(10) & "Weight: " & w, NA() ) )) At the sheet level: =LET(results,TOCOL(BYROW(CombinMatrix,Solve),2),WRAPROWS(results,4,"")) The results: The question for the community: Can you conceptualize an easier way to do this? Formula-based solutions only, please.SolvedPatrick2788May 20, 2025Silver Contributor7.5KViews1like14CommentsMatch/Index referencing incorrect row
I have a spreadsheet that I am working on in MS365. On sheet "A" I am drawing the following information: AGE (=G63) into cell S65 Weight Lifted (=F68) into cell S66 From sheet "B" (Reference data) Row 2 are the age categories (17 - 21, 22 - 26, 27 - 31, 32 - 36, 37 - 41, 42 - 46, 47 - 51, 52 - 56, 57 - 61, 62 Over. These are grouped as MDLAGE. Column A are the scores - 100 - 0 descending order. these are grouped as MDLPOINTS In cells B3:K49 are the weights lifted. The values vary by age and there are cells that are blank or have a "---" in them. These are grouped as MDL. The formula I am using is: Cell S67 - =MATCH(S65,--LEFT(MDLAGE,2),1) This formula is returning the correct column. Cell S68 - =MATCH(S66,INDEX(MDL,0,S67),1) This formula is referencing the INCORRECT row. It is returning row 40 instead of row 31 which is where the correct weight is listed. If I change the 1 to a 0 or -1 the return is N/A. I have tried multiple formulas to "skip" the blank or --- cells but nothing has worked. Any help would be greatly appreciated!Scout50May 20, 2025Occasional Reader45Views0likes3CommentsNeed help with a formula
Im building an employee data sheet and one of the columns is HIRE DATE, the next column is 90 DAY REVIEW DATE. Both columns are date formatted. The formula in the 90 DAY REVIEW DATE cells is simply the hire date + 90. The problem is that if there is not a date in the hire date cell, the date that is being displayed in the 90 DAY REVIEW DATE is 3/30/1900, (I assume this is the default beginning date in Excel?) I do not want anything displayed in the 90 DATE REVIEW DATE column is there is not a date in the HIRE DATE column. Can someone help me with this please?SolvedSunnymom1263May 20, 2025Occasional Reader24Views0likes1CommentSum from cell across multiple sheet, depending on hire date
I can not figure this out, I am trying to make a summary sheet that will count the vacation days across multiple sheets from "Start" to "All" (these are timesheets between those named sheets) for employees. But I need the count to reset on their hire anniversary month and day. Employee 1 - Hired on 1/2/2001 (found in cell B5 on the sheet named "Summary") Employee 2 - Hired on 4/9/2020 (found in cell B6 on sheet named "Summary") In this example, any vacation days in cell F5 (employee 1) across multiple sheets between sheets named "Start" to "All" will count until the anniversary month and day arrives, then it will reset and start counting forward again until that date arrives again. Any help will be much appreciated!SolvedLisaB1009May 20, 2025Copper Contributor225Views0likes10CommentsHighlight Duplicates but only from one column
Hello, I have two columns of values in my screenshot. Column D and Column E. Column D has some duplicates and Column E does not. How do look at both columns but only highlight the duplicates in column D, only IF they appear in column E? Thanks!Solved1162646322May 20, 2025Occasional Reader22Views0likes1Comment#unknown! displayed in excel where an inserted picture should be located
A supplier is sending me an excel template which contains pictures in a cell The picture can be viewed in web based outlook and web based excel but when I download the file the cell states #unknown! I believe the supplier is using a personal 365 account and I am using 365 Apps for enterprise I think they are adding the picture by completing the below (taken from MS site) this is what I see when I open it up on web based outlook But this is what I see when I download an open in excel desktop Any help is greatly appreciatedAButler1910May 20, 2025Copper Contributor51KViews3likes17CommentsShapes Moving Even When set to Do Not Move
I use Excel 365 Enterprise 4 edition. I created a file that is stored on a Teams site that contains shapes I use as buttons for my macros. Every time I open the file the shapes have moved and/or resized and I can't figure out how to prevent this. I have the shapes set to Don't move or size with cells set on each one of them. Users are not moving them. No one is playing any pranks, I wish they were it be a lot easier to fix. Anyone have any ideas?Solved34Views0likes2CommentsFormula needed to retrieve % of correct reviews for an employee, where reviews are on separate tabs.
Hello everyone! Thank you in advance for any assistance you are able to offer. I'm working in MS 365 for web on a windows cloud environment through a Citrix network. Looking for a formula that will populate one employee's evaluation results as a % correct for each of multiple worksheet tables of reviewed items (pass/fail). See attached workbook for details on what I'm trying to do. I would like to be able to choose an employee's name from a drop down on the RESULTS tab, select the months to begin and end a date range and have this return the total reviews and number failed for each 'Review Name' for that employee. Thanks again! :)Marcus_BoothMay 20, 2025Brass Contributor63Views0likes2CommentsFull Dark Mode in Excel
Currently you can set Office Theme to Black which acts as a dark mode. In Word, setting this to Black makes the page dark and the text white when it is set to “Automatic”. I was wondering if anyone had an idea if Microsoft is planning to release this same thing for Excel too? Right now Excel when using the dark theme makes everything dark except the cells. Which kind of makes no sense to me. This is where your eyes are most of the time. It needs to be a proper fully dark theme. Is there a way to get a full dark mode without manually setting the background color of the cells dark and the text white? Can a plugin be made to do that while keeping the actual cell background “empty” and the text “Automatic”? Is there already a plugin that does that? Thanks!JulienTheriaultMay 20, 2025Brass Contributor410KViews26likes53CommentsConditional formatting not reading dates
Good morning, I have a spreadsheet in which dates have been formatted as dates in the Home tab, and where I already tried using "Text to Columns" in the Data tab to make sure the dates are formatted as dates and not text. Nevertheless, when I try to apply conditional formatting to highlight dates that are less than TODAY(), it doesn't work as intended - it just highlights everything. If the data is formatted correctly - as dates - what else could be going wrong? Or is the data still not formatted correctly?AS2021May 20, 2025Copper Contributor11KViews0likes6Comments
Resources
Tags
- excel42,709 Topics
- Formulas and Functions24,778 Topics
- Macros and VBA6,421 Topics
- office 3656,052 Topics
- Excel on Mac2,658 Topics
- BI & Data Analysis2,382 Topics
- Excel for web1,929 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,647 Topics