dynamic array

Here is my current formula: AVERAGEIFS ( INDEX ( $H$10:$X$505, , MATCH ( [SKU], $H$10:$X$10, 0)), $E$10:$E$505, [City], $F$10:$F$505, [Store Name]) I am trying to summarize the average prices (H11-X505) by product (header cells H10-X10), city and store (Columns E-F) in a separate table using...

Hi Everyone, I have attached a spreadsheet and highlighted in yellow the column K I would need to be filled with a formula (spill formula only so that it is completely automated). Column I and J: This is a spill formula using Unique, Filter and Choose function. Column K: I would need a spill...

Hi Everyone, I have an issue with a formula and I have been told we have all the experts in the world to answer to this question. I am trying to get a list of unique values for 2 non-adjacent columns. I have tried the formula below but it doesn't work. It only spills the result for one single...

Hello, I'm betting there is a better way to run this formula that references a block of dates by month, and returns the table rows based on the short date related to the selected month. In the example below, I have a dynamic array showcasing the rows of data from a table based on the month...

Hello, Given the table (Table3) representing the 1:1 slots between 2 participants with the following data: JKLMNOPQRSTU2TIMESLOTGroup 1Group 2Group 3Group 4Group 5Group 6ParticipantSlots to participateGroup309h00-09h15X8_1A - X8_BX8_1E - X8_2BX8_1B - X8_5X8_1C - X8_3X8_1D - X8_AX8_2A -...

Hello, Given the table (Table3) representing the 1:1 slots between 2 participants with the following data: TIMESLOTGroup 1Group 2Group 3Group 4Group 5Group 609h00-09h15X8_1A - X8_BX8_1E - X8_2BX8_1B - X8_5X8_1C - X8_3X8_1D - X8_AX8_2A - X8_609h15-09h30X8_1A - X8_4X8_1C - X8_2AX8_6 - X8_3X8_2C...

Dear Members, I need assistance in creating a CAGR array from periodic returns vector. I wish to create a Dynamic Array holding only calculated CAGR (compound annual growth rate) values derived from periodical return on investment vector. This is easily performed in standard Excel but...

Hi everyone, I have been searching for a way to calculate a range of cells that meet two date criteria, however the problem I have is figuring out how to use SUMPRODUCT or similar as the sum range is dynamic. My goal is that users will be able to enter a range of figures into an 'Hours' Column...

I have a few inputs in A1:B3: months 20 Price per month 30 cost 180 then in: A5 =sequence(1,b1,1,1) A6 =sequence(1,b1,B2,0) A7 =If(A5#=1,b3,0) This gives me a useful and dynamic table. However, I would add a rolling P&L from A8 for the length of the number of months? Below is the result that...

I am trying to use a sumif with a sheet that has been set up with dyanmic arrays (see below image) I need to sum each column based on the flags "A,B or C". Is anyone able to suggest a formula that will spill and allow me to do this?

Hello (again) mrexcel forums, I'm working on a formula which outputs an array of values in an n*m grid, where the size of the grid may vary a lot between use cases. I've been getting good mileage with using arrays as named variables in equations, but i'm having trouble with the MIN/MAX...

Hello mrexcel community, Unless i am completely inept at using the search function, this doesn't seem to be a redundant question, and i would be grateful for any help with solving this puzzle. The problem is performing a lookup on a function which outputs multiple values (and thereby spills...

Hello, I'm needing help to find a way to filter data without using the updated filter and sortby functions released in excel 365. Is it possible to filter data using multiple criteria with basic excel functions? I've attached a sheet with cell A4 using the filter function to produce the desired...

There is a well know solution to use dynamic ranges as source of a pivot table (name using offset). In my case I want to take it one step further. I want to put the name of a sheet into a cell and derive the source of the pivot table from that. Here is what I tried: 1: I have defined a name...

Good afternoon, Is there anyway, using VBA, to export a particular range of data that's been populated with a dynamic filter from my current workbook to a new workbook? My current workbook has 5 sheets. I want to export a range of data from Sheet1 to its own workbook. In Sheet1, I have Columns...

Hi, I'm not sure if this is possible so would appreciate any responses. I'd like to be able to use a named range in an index but would like the named range to be dynamic. For example ideally the formula would be like =index(cell reference to dynamic array, etc). The problem is I don't...

I am new to VBA. Currently I am using this, lastColumn is last column of worksheet. lastRow is last row of worksheet. It is working fine for: ActiveSheet.Range(Cells(1,1), Cells (lastRow,lastColumn)). RemoveDuplicates(Columns:=Array(1,2,3), Headers:=xlNo) - But it will work only for 3 columns...

Let’s create a dynamic calendar in Excel the quick and easy way. We can use the clever new Excel function SEQUENCE in Excel 365 Insider. Mr. Excel (Bill Jelen) sparked this idea with this video and I enhanced the concept so that all we need is a date to make the calendar matarialise in the...

I have two different but identical computers with Office 365 (up to date to 1911, monthly channel) + Win 10 (both x64). In one computer Excel dynamic arrays work as expected, but not in the other. Should this happen and is there any way to "fix" it?

We can use Excel's new calc engine and dynamic array functions to create a loan amortisation schedule in a way that omits the need to create the correct number of rows or complex formulas to account for the correct number of rows. The formulas go in one row and they spill down to the correct...