Forum Widgets
Latest Discussions
#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
Copying range of cells referencing named cells with a sequential change
I have a large dashboard with many named cells. I want a summary table of 36 rows and 15 columns to reference the named cell values without having to manually insert each reference. Example: I enter =A1_A1 into cell D6 and want the adjacent cell D7 to dynamically be =A1_A2 and D8 to be =A1_A3, etc. Example: I enter =A1_A1 and the next cell down to become =A2_A1. then =A3_A1, etc. I know there are functions that can address it, I just have not been able find what works. I appreciate any assistance with this. It would save me a tremendous amount of time.
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!
