Forum Widgets
Latest Discussions
Insert Options button on Mac
Hi, I want to insert a new column in a table. However, I want that column to be blank. Thus, I must use the "Insert Options" button after I have inserted the column. I cannot find this button / it doesn't show. I have checked so that "Show Insert Options" is turned on in settings. I use Mac. How can I find this button? //FilipColler une valeur sur des données filtrées est désormais également collé dans les lignes masquées.
Bonjour, Je viens de changer d'emploi et dans Excel lorsque je copie des informations de cellules filtrées et que je les colle dans la colonne d'à côté les données se collent sur les lignes masquées et non pas les lignes visibles uniquement. J'ai compris que je peux passer par une fonctionnalité Atteindre/Cellules visibles uniquement pour coller, mais cela prends du temps. Dans mon ancien emploi, mon Excel ne réagissait pas comme ça. Je pense que c'est un sujet de version d'excel ou de paramétrage de base. Quelle version ou quel paramétrage peut me permettre de revenir au fonctionnement que je connais. Merci
Need to return col A if another col contains the text in col B (array) anywhere
So, here's my array in columns A and B: Column L has a journal description that COULD contain one of col B descriptions. So, column L will have things like "3M LO(a)N 2024.12" OR "3M LO(a)N 2024.11", etc. But they could also have "TRU UP COLORADO INTEREST #3126" - basically, the description in col B could be anywhere (together or separated) in Col L. What I want to do is, in Column C write a formula that will look at column L and compare it to the array in columns A & B, and then return column A if the value in col B is found. So, for "3M LO(a)N 2024.12", col C would return "1". For "TRU UP COLORADO INTEREST #3126", return "5". The difference being "3M LO(a)N" is found in the same sequence as it is in the array, but "COLORADO #3126", though contained in col L, the two parts are separated. I'd like to leave these descriptions as they are to help in identifying if/when I need to change the array, but if it's too difficult, I could just use "#3126" as the description in col B and add a vehicle description in another column. The array is named "CATEGORIES" to ease formula creation. Thanks for any help in advance. *** apparently the full word LO AN is not allowed in the text. So, LO(a)N it is.What do you think of thunks?
OK, so the most likely response by far is going to be "I don't". However, I tried one of Omid Motamedisedeh's regular challenges and found it a suitable problem for exploring some of the lesser known byways of modern Excel. The challenge is to pick out locally maximum values from a rolling range. What I did was to write a function that used MAP to select one cell at a time, using DROP to remove the initial cells and TAKE to return a range of 5 cells with the active cell in the middle. The direct route to solving the stated problem would be to calculate the maximum value within each range immediately, but I was interested in the more general problem of "could I return the array of ranges in a form that would support further analysis?" As shown, the following formula ROLLINGRANGEλ = LAMBDA(values, n, LET( rows, SEQUENCE(ROWS(values)), MAP(rows, LAMBDA(k, LET( m, QUOTIENT(n, 2), rng, TAKE(DROP(values, k - m - 1), MIN(k + m, n)), rng ) ) ) ) ); gives and array of ranges error, but simply by enclosing the 'rng' variable within a further LAMBDA ... LET( m, QUOTIENT(n, 2), rng, TAKE(DROP(values, k - m - 1), MIN(k + m, n)), LAMBDA(rng) ) will cause Excel to return an array of functions, each one of which would return a range if evaluated. In the attached workbook, a number of formulae are based upon this array of functions = ROLLINGRANGEλ(dataValues, 5) = MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, ROWS(ϑ()))) = MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, ISREF(ϑ()))) = MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, AVERAGE(ϑ()))) = MAP(ROLLINGRANGEλ(dataValues, 5), dataValues, LAMBDA(ϑ,v, MAX(ϑ()))) = LET( rollingMax, MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, MAX(ϑ()))), FILTER(Data, rollingMax=dataValues) ) The first simply returns #CALC! errors on the worksheet which is the normal response to a Lambda function defined on the grid. The second formulas uses ROWS to show that the ranges are not all the same size, the third shows the returned objects to be range references and not simply arrays of numbers, the forth is a rolling average while the fifth is a rolling MAX. The final formula returns the solution to the problem, being a filtered list. The purpose of this post is to demonstrate that Excel, which started out as a 'simple' spreadsheet program, now contains a very different programming environment that shares a function library and uses the grid for input/output but, other than that, has very little in common with 'normal' spreadsheet practice! A related survey can be found at https://www.linkedin.com/feed/update/urn:li:activity:7285432559902068736/
IFS or anyother function
Dear Experts, I have a data like below:- So, Column "B" - sfn can go from 0 ~ 1023, and Column-"C", can go from 0~19, Column "G" has 3 values(rnti's) - Now, we have only 2 situations like below in Column"E", where I need the formula:- Either all these 3 rnti's can be Multiplexed in the same sfn.slot So, in below snip all 3 rnti's are FDMed in same sfn.slot - 394.6 and should be continuous, so Column "E" should have fdm-3 But in below instance, in 395.2 we have only 2 rnti's multiplexed(so fdm-2 should be populated) Attached is the spreadsheet. Thanks in Advance, Br, Anupam
