Forum Widgets
Latest Discussions
Excel - Pivot with permutations & combinations
Hello everyone, I've got a problem with excel that I've not managed to solve and search has not been too helpful either since I'm not sure how to put the exact issue into words. I will be inspecting laptops and noting down the issues with them in the columns in front of it. The issues will be noted in a random order based on physical observation of the asset. The format of this will be something like this: So here in column F I have the count of the model number having the exact same set of issues even if the order of nothing these issues is different. I've highlighted the ones having the same issues with the same color for ease of understanding. The sheet could contain hundred of rows so a summary like this would be really beneficial. I've tried to put in writing the problem the best I can and hopefully someone can help me with this. If you guys need any more information for this please let me know and I'll provide the same.
No clustering in clustered bar chart when secondary axis is added
Hi there, I've got a clustered bar chart with two data series, one of which needs a much smaller scale than the other. It looks fine as long as I don't add the secondary axis (see pic). Note that here I've made the y-axis range so small that you can see the second series. Displaying the full range of the first series results in the second series being invisible. Here you can't see the full range of the first series, so this won't work for my purposes. When I add the secondary axis, I get this (see pic) and I don't see any way to avoid it. It seems like it might be a bug. Adjusting the overlap or the gap width doesn't help. Anyone have any suggestions? Thank you!!
SUM is not adding up in one cell, but it works on the copied cell
So if you look in my screenshots, cell AC10 has got the function in the correct cell. But it's saying the sum is 0. which makes the other two 0. When I copied it to the cellar AC11 through AC26, they all computed correctly. Why isn't the first one not computing at all? Any advice would be appreciated!! I'm still in the learning foundations phase.VBA Coding Help - Multiple Criteria 'If Range'
Hi, I'm relatively new to VBA in Excel and working on some improvements to a form I have created. As part of this, i would like a panel on the bottom of the form to unhide certain rows based on sets of criteria in other cells. I have written out coding that i thought would work but it does not produce the required result so hoping someone here can help me correct it and show where I am going wrong please? AIM: Approval panel rows 47:49 and 51 unhide when any of the following is true - Cell D9 <450001 or cell D10<32000001 or cell H9<75001 or cell H10<50001 (rows 50 and 52:53 remain hidden) Or Approval panel rows 47:51 unhide when any of the following is true - Cell D9 >450000 but <1000001 or cell D10<32000001 or cell H9>75000 but <200001 or cell H10>50000 but <100001 (rows 52:53 remain hidden) Or Approval panel rows 47:53 unhide when any of the following is true - Cell D9 >1000000 but <7900001 or cell D10>32000000 but <39400001 or cell H9>200000 but <3900001 or cell H10>100000 but <236001 The current code i have is: If Range("D9").Value <= 450001 Or Range("D10").Value <= 32000001 Or Range("H9").Value <= 75001 Or Range("H10").Value <= 50001 Then Rows("47:49").EntireRow.Hidden = False Rows("50").EntireRow.Hidden = True Rows("51").EntireRow.Hidden = False Rows("52:53").EntireRow.Hidden = True ElseIf Range("D9").Value >= 450000 And Range("D9").Value <= 1000001 Or Range("D10").Value <= 32000001 Or Range("H9").Value >= 75000 And Range("H9").Value <= 200001 Or Range("H10").Value >= 50001 And Range("H10").Value <= 4100001 Then Rows("47:51").EntireRow.Hidden = False Rows("52:53").EntireRow.Hidden = True ElseIf Range("D9").Value >= 1000000 And Range("D9").Value <= 7900001 Or Range("D10").Value >= 32000000 And Range("D10").Value <= 39400001 Or Range("H9").Value >= 200000 And Range("H9").Value <= 3900001 Or Range("H10").Value >= 100000 And Range("H10").Value <= 236001 Then Rows("47:53").EntireRow.Hidden = False End If If it makes any difference, the cells containing the criteria (D9, D10, H9 & H10) are all formatted to Currency £ with 2 decimal places, and this needs to remain. I'd really appreciate if anyone can help me work this out. Thank you in advance for your time and help.
Reformating data from pivot tables
I have a data table that contains several pieces of information. It contains informations about the type of certificate received in each year for different education types. Unfortunately, I can only download the data in the long format as in the first added screenshot. I tried fixing the issue by using pivot tables but this has several problems, I can't removethe sum collumns and it gives issues in trying to make comparative time series. I only managed to make a table as shown in the second screenshot, however, I need to get rid of everything resembling subtotals and I need to be able to make comparative time series. All suggestions are very welcome and much appreciated!
Utilizing 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 ) ) )
How 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,Move 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.
