Forum Widgets
Latest Discussions
Pivot Not Grouping
Hello Experts, I am trying to group a simple data set. I have 2 Columns: Deposits and Withdrawals I need to Group on SWAP. It doesnt group for some reason. Do you see why its not grouped? I assume its not ideal to have 2 columns. thank you. Please see attached file.Tony2021Apr 30, 2025Steel Contributor27Views0likes2CommentsUtilizing Excel's turing capabilities to create Conway's 'Game of Life'
The Background It's been said with Lambda (and LET and a wealth of functions in 365) Excel has become 'turing-complete'. To quote the article linked below: "You can now, in principle, write any computation in the Excel formula language." https://www.microsoft.com/en-us/research/blog/lambda-the-ultimatae-excel-worksheet-function/ The Challenge I thought it would be fun to create Conway's 'Game of Life' in Excel 365 to see how far I could push things. Conway's Game of Life - Wikipedia The rules are simple: A 'cell' has up to 8 adjacent cells (less if the cell is on the edge of the board). A 'neighbor' is a cell with a 1 while a 'dead' cell is empty. An 18x18 board Multiple iterations Bigger boards! more (it's relaxing to create new shapes and designs) The Approach My first thought was to use MAKEARRAY because I could use 'r' and 'c' coordinates and there would be no stacking. I devised a recursive function that worked for 1 iteration but failed on subsequent iterations because the use of TAKE/DROP was slowly shrinking the board! The revised approach is essentialy a recursive MAP that uses 3 arrays: the input matrix, the 'r' array (row numbers) and the 'c' array (column numbers). It's my way of using r/c without using MAKEARRAY. For Discussion I welcome any improvements to the existing function and any different approaches someone may have to creating Conway's Game of Life. Conway Lambda follows: Conway =LAMBDA(matrix, iterations, IF( iterations = 0, matrix, Conway( LET( height, ROWS(matrix), width, COLUMNS(matrix), r_arr, SEQUENCE(height) * SEQUENCE(, width, 1, 0), c_arr, SEQUENCE(height, , 1, 0) * SEQUENCE(, width), CheckNeighbors, LAMBDA(lattice, r, c, LET( RCx, LAMBDA(row, col, IFERROR(CHOOSECOLS(CHOOSEROWS(matrix, row), col), 0) ), N, RCx(r - 1, c), NE, RCx(r - 1, c + 1), E, RCx(r, c + 1), SE, RCx(r + 1, c + 1), S, RCx(r + 1, c), SW, RCx(r + 1, c - 1), W, RCx(r, c - 1), NW, RCx(r - 1, c - 1), compass, VSTACK(N, NE, E, SE, S, SW, W, NW), neighbors, SUM(compass), IF( AND(lattice = 0, neighbors = 3), 1, IF( AND(lattice = 1, OR(neighbors = 2, neighbors = 3)), 1, 0 ) ) ) ), MAP(matrix, r_arr, c_arr, CheckNeighbors) ), iterations - 1 ) ) )Patrick2788Apr 30, 2025Silver Contributor2.2KViews3likes26CommentsHow to filter JSON data to a specific result?
Hi all, I am trying to get JSON data from the IEX API and have used the WEBSERVICE() function, but I can't seem to filter it down to the values I need. For example: If I want the "total cash" for the first year financials of Apple; I have tried this formula: =VALUE(WEBSERVICE("https://api.iextrading.com/1.0/stock/AAPL/financials?period=annual/0/totalcash")) Which gives an error. I think the incorrect part is the "/0/" but I don't see how to specify which year I want. Any help is really appreciated. Thanks,RayMaN1Apr 30, 2025Copper Contributor2.9KViews0likes1CommentMove cells automatically in Column
I have two parallel columns of names, both in alphabetical order. Let's call them A & B. in A the names are duplicated; in B they are not. I wish to have a formula to compare B with A and, if they ae the same, take no action. if the are not the same,, I wish to insert a blank cell in B to move all cells below in Column B down one. The result should see names in column B aligned with the first cell of A having that name. Before that I need to mark (maybe colour) any names in B that are not listed in A at all. They are few, if any, so I can extract them (to C, perhaps) and by hand move up the reminder of B to fill the space. I would be glad of advice, please.GeoffreyBHApr 29, 2025Copper Contributor24Views0likes2CommentsHighlighting values across a book
I'm not certain if this is something that requires a formula, special coding, or what. I have a excel book with a series of sheets regarding assignments for roughly 400 employees. I need to highlight any name that occurs more than twice across the entire book - so I need the values to be individually counted between each sheet and then highlight on all sheets if they are listed more than twice. Please and thank you.macklemicApr 29, 2025Copper Contributor28Views0likes2Comments- PatDorApr 29, 2025Copper Contributor24Views0likes1Comment
Comands not visible under some tabs unless I select arrow
The Alignment tab does not display the various commands like wrap text unless I select the arrow under alignment. I found that I am supposed to have a “Ribbon Display Options” arrow in the upper right corner of Excel, but I do not have this arrow. How do I restore this arrow? This is also true of the Number tab.DracarysApr 29, 2025Copper Contributor84Views0likes12CommentsLook Up for multiple sheets?
I wonder if someone would be so kind as to help me with a little problem... I have 12 different workbooks, one for each store, for labour and sales forecasting. On each workbook there are a few different sheets - one for each period and then a final sheet which shows the total for each week and period, giving a quarterly summary. The only problem with this is when I want to review these I have to open individually 12 different workbooks. Is there a way that I can create a new workbook, which shows the 'Quarterly Summary' page for each store. I initially thought a look up formula, but I think this only applies to particular cells and not an entire sheets? Thanks in advance!jmcintosApr 29, 2025Copper Contributor67Views0likes5Commentsgraph
I need help creating the last graph. My computer will not format the x and y axis the way I need them to.Jess9790Apr 29, 2025Copper Contributor1.6KViews1like14CommentsFormula Help
I am using Excel for Mac version 16.96 (Office Home & Business 2021). I am trying to create 2 different formulas within a cell. The first one calculates based on another cell. So, if E2 has "Y" in it, I want F2 to turn red. I used conditional formatting, classic formatting rule, use a formula, and then put =E12="Y" and formatted it with light red fill and dark red background. It does exactly what I want. I followed the same steps and put the formula =F2="Y" and have it turn green. It works. But they can't both work. If I fill E2 first with a Y, then it turns red. But when I then type Y in F2, it does not later turn green. It stays red. I'm trying to highlight a box red to tell me that the item has arrived (E2 Y is for yes, it has arrived) but I still need to do something with it. The red makes it easier to notice. But once I put a Y in F2 (because I did something with it), I need the box to turn green so I know I'm done with that item. Is there a way to accomplish this? Since I know you can make a cell turn red or green based on the information in that particular cell, I know Excel is set up to handle this. I just don't know how to make it work with looking at the data in a different cell for one color and looking at the data in that cell for the other color. Thank you for any assistance. For now, I've been keeping the formulas and then deleting the red formula once I need it to turn green, which is not sustainable.SolvedPaigeApr 29, 2025Occasional Reader33Views0likes1Comment
Resources
Tags
- excel42,614 Topics
- Formulas and Functions24,725 Topics
- Macros and VBA6,408 Topics
- office 3656,029 Topics
- Excel on Mac2,656 Topics
- BI & Data Analysis2,373 Topics
- Excel for web1,917 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,635 Topics