Forum Widgets
Latest Discussions
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.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/Which graph is best to show positive and negative relationship of different variable to one output?
Hi everyone, I am working on a business case study where I have to show a realtionship of 6 x (x1,x2,x3,x4,x5,x6) to a single output (y) . now x1,x2 & x3 have positive relationship with output y for example if x1,x2 and x3 are higher the better for output y but x4,x5,x6 have negative relationship with output y for example if x4,x5,x6 are lower the better for output of y I am struggling to understand which graph should I used or which technique should I used to show on a dashboard that why Y was not achieved based on different behaviors of x1,x2,x3,x4,x5 and x6. Waiting for an answer. thanks
File for work. please help!
Hi all, i have a file that i use for work. Right now there are 2 different pages. a list of the items with their code and an info about their situation. For Example if they are out, lost, etc. a list of numbers that i send next to the date. if they come back they are yellow, if not they are red. i do all of this manually every day, but when i am not in the office no one does that because it's long, boring and probably very confusing. Ideally i need something that keeps track of what goes out and what comes back and the dates. i attached the two pages. any help, advice, would be highly appreciated!! Many thanks!
How to search multiple names across entire excel workbook and return data location data.
Hi I will like to find out how I can search for multiple data (listed in a column) across an entire workbook (either as a data in a single cell or even as the searched term is present as a word in a paragraph of details in a single cell) and return the cell position data (multiple, duplicate is alright as intention is to be comprehensive). An example as follow: 1a. Enter multiple search verbiage in a column “abc co ltd” “Def co ltd” ”ghi inc” 1b. Expected outcome for each input verbiage from above (1a) “abc co ltd” worksheet 1, Cell a2, cell z5 “Def co ltd” nil ”ghi inc” worksheet 2 cell f5, worksheet 5 cell i4 thanks a million
ScrollArea not working
I am creating an Excel dashboard and I trying to lock the screen area. This is the first time I am trying to use this feature. My file is Macro-enabled and saved as .xlsm and I am using Microsoft 365 Personal on Windows 10 Pro. Below are the options I tried to lock my screen, however, none of them are working: Went to Developer -> Properties -> In Scroll.Area, I entered A1:W25 which is where I want to lock, then closed the Properties box. However, it didn't work Used VBA, added the following code in ThisWorkbook editor: Private Sub Workbook_Open() Worksheets("MyWorksheetName").ScrollArea = "A1:W25" End Sub Then saved the file, and reopened it, however, this didn't work as well 3. I tried contacting Microsoft Support, and we tried diagnosing this problem, tried both of the above solutions, updated Microsoft Excel, checked all the settings, however, this didn't work as well. Can someone help me with this issue?Date Tracker - Calendar View
Hi, I'm looking to create a yearly calendar view that for example shows all the Bank Holidays by country thats listed on another tab. Tab 1 is the overall view that has all the dates across the top and each row represents a country Tab 2 is the data view. Have all the BH dates listed by country Tab 3 is black out periods - between 2 dates that all have to go onto 1 row. Example in yellow on the cal view How do i pull these into the overal view to highlight the days on each row to give me a yearly view of everything. Here is the Example File I've looked at gant charts to calendar view i'm not sure if i'm over complicating this or if its not possible. Thank youCodice.caratt(10)
Ciao, mi scuso già perchè ho visto tante conversazioni a riguardo ma non riesco a trovare una soluzione. non conosco VBA. la mia domanda è la seguente: Uso Excell in MAC. devo concatenare un elenco e devo andare a capo in ogni parola A1 Cane A2 Gatto B1 deve riportare Cane Gatto, ma non di seguito, a capo ho provato a scrivere =Concatena(A1;Codice.caratt(10);A2) ma non succede niente (CaneGatto) ho provato a scrivere Codice.caratt(13) ma non funziona, ho provato a formattare a capo ma non funziona avete suggerimenti? grazieExcel Displays Dates as Serial Numbers on Referring to a Cell, but Google Sheets Handles It
I’m encountering an issue with date formatting between Excel and Google Sheets: When referring to a cell with a date in Excel, it displays as sequential serial numbers instead of the expected date format. If I open the same file in Google Sheets, it correctly recognizes and formats the dates. Strangely, when I download the file from Google Sheets and reopen it in Excel, the dates display correctly in Excel. Why is this happening, and how can I ensure Excel consistently interprets these values as dates? Any tips for resolving this issue would be greatly appreciated at code level ( Ruby on Rails ).
