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.
Match/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!
#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 appreciated
Formula 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! :)Full 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!
Conditional 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?
