Forum Widgets
Latest Discussions
Selecting MultipleCells on Microsoft Excel using an External Touch Screen Monitor
I recently attached an external touch screen monitor to my laptop and initially I was struggling to select and highlight multiple cells on Excel Randomly the cells started appearing with a circle on each edge making it easier to highlight more cells up, down, left and right Now again randomly its changed again without me changing any settings at all and I can no longer highlight multiple cells using the touch screen monitor The only workaround is to use the select objects function but this does not always work nicely to highlight the cells Any ideas as to how to get the circles back again on a selected cell to make it easier to highlight multiple cells? This is how it looks now but havent got a picture of how it used to be when it worked wellWhat 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/
Transpose in arrays of n columns
Hello all and thanks in adhance for your help. I am tryting to find a formula or shortcut that would allow me to transpose arrays of n columns into matrixes of n rows and n columns, to plot the data in a different software. In this example I have two arrays, each is 3x3: The software where I need to plot, asks for three arrays of 2x3 as shown: In reality I'm working with three different softwares, the first one gives me raw data that I compute in Excel, obtaining up to 56 groups as shown in the first picture; however, the third software requests the data as the structure shown in the second picture. Any suggestions are welcome.
COUNT UNIQUE VALUES WHILE FILTERING
Hi, I need some help regarding my file. I have managed to count total unique values in column L which is 4,075, however if I apply filter on the dates that are not blank, it still showing total unique values of the whole column L. (screenshot only, I filtered it by date of welding.) this is the formula I use: =SUM(IF(FREQUENCY(IF(LEN(L10:L12194)>0,MATCH(L10:L12194,L10:L12194,0),""),IF(LEN(L10:L12194)>0,MATCH(L10:L12194,L10:L12194,0),""))>0,1)) Looking for some assistance. Thank you
Name Manager does not list all names it has remembered.
There was a table in my workbook called Category. I deleted the table, then created a set of cascading drop-down tables, which the app named as Table1 thru Table6. I then set out to rename the tables with their column headers. This worked fine except for Table1, which I was unable to rename as Category to replace the deleted table, so I renamed it as Categories. My workbook now has only the names shown in this screen shot: When I try to change the name of the Categories table to Category, the app consistently returns: Note that this set of tables is the only content of the workbook, There are no cells anywhere, and never have been, which reference the Category label. I have been programming since 1970. No compiler or interpreter I have ever worked with has ever exhibited this behavior. If I delete a named variable, the name should immediately be available for reuse. How do I work around this defect?Countif Formula Help
I am tracking employee errors on transactions for my organization and am trying to provide calculate how many times each error has occurred over a 12-month time frame. The error reasons are programmed into the employee tracking logs and I have compiled everyone's log. All of those logs have been compiled into one giant list (about 1300 entries). They went super granular when creating these tracking logs and there are 68 different "Error Reasons" - Below is an example of a handful of them. Previously when I have done this, I have manually input CountIf formulas for each of the error codes, which is doable but very time consuming. Is there a way to get Excel to auto populate the error reasons?
Search for Highlighted Cells
Hello, I am an occasional Excel user working on a special project where I created an Excel spreadsheet whose data consists of a large number of comments. What I am trying to do is find any cell that contains a specific word, highlight that cell, and then create a new worksheet that contains all of those cells with the word in it. The best way I thought of to do this was to conditionally format the spreadsheet containing the data. So now, every cell that has the magic word in it is highlighted yellow. How do I pull only the highlighted cells into a new spreadhsheet? Thanks in advance.
French keyboard accounting issue
My Excel document is refusing to format the cells of a row of numbers in French euros format. Anyone have any suggestions on how to fix this? I have contacted support and just spent an hour on this. It will not accept the commas which French format uses instead of the decimal point. Even when the version of the app is put into the French language. Format cells using French or euros do not work either. The only fix seems to be to use decimal points. But this means my software would be incompatible with the Excel file downloads taken from my French accounting system online... the idea of having to change every comma to a fullstop is frankly depressing!! There's gotta be a better way...
If formula problem
Hi all! I am new to using excel so I appreciate your help in advance :) I created a spreadsheet to track multiple choice answers for a quiz. This is the formula to track if people got the answer right: =IF(F3="a",1,IF(F3="b",0,IF(F3="c",0,IF(F3="d",0,IF(F3="-",0,NA))))) However, someone answered "bd" instead of only selecting one and the formula resulted in an error. How do I correct the formula so that the answer would be "0" in that case? Thank you?
