aggregate

Hi, Greetings for all! I have a formula at Sheet2 as ...something(from this beautiful forum)..Sheet1!$A$2... When I insert 10 rows in Sheet1, formula in Sheet2 changes into ....Sheet1!$A$12.. How to prevent/fix formula to $A$2, how to fixate it to A2 Thank You in advance!! New Micro Excel...

Hi. I have large database and i have to find median of only selected cells. I filtered and used Median formula but it is not give correct. Then i tried Aggregate (=AGGREGATE(12;1;etc) formula but now when i unfiltered all data changed. Can you help me?

Hi I have a counter with a minimum value of 17. As long as this value is not reached, then extra dates should be added to an array of dates. The extra dates should be workdays: if for example 2 extra dates are needed to reach the 17 value, but the 24th of December is not a workday, yet 23rd and...

I have a set of data (see picture attached), where different projects have several dates, even within same month (A3:B16). My goal is to build a ''calendar'' that will show Projects and their first date that will fit within the selected month. E.g. in the picture: I want to see the projects...

Hi everyone I have a set of data with different notification dates for several projects (some different projects have the same notification date) and different owners (table in A7 column, as a Source). My goal is to filter and list all the projects that fall within a certain date range (column...

Hi everyone, Understand my problem can be complex. Allow me to explain in detail. If you refer to my excel file, there were a number of different types of (MI) Measuring Instrument types (TPG, PG, CMM SRT etc) Using my current existing array formula in cell F111:F145...

I have a sheet which keeps a tally of profit, drawdown and the like and I am trying to do a simple calculation, but the answer seems way off. In column CU, drawdown % is calculated. At present there are only 59,200 rows and row 22 is the first row of data. This is the formula to work out the...

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...

Hello, I've tried small, large, aggregate.. I just can't get it right. My goal is to have a list on a separate sheet, but I tried on the same sheet as the data to see if it can be done first. Look for ID within the list and match, check if that ID matches a criteria in another column (other...

I am struggling to find any way to insert a column into the MAIN tab pulling from the INV tab. The MAIN tab is one sales order for 1 customer with 29 rows, intentionally order qty of 1 in each. The INV Tab is the list of invoices for the same sales order. Some of the invoices were for...

Hi all, I'll try to be as clear as possible so please do not hesitate to ask for clarification. I'm working with a text file data extracted from CATIA V5 that replicates the tree structure of an assembly with its components and subcomponents. From that text file I arranged the source data in...

Hello, I have a Data-Validation Drop Down on Cell A1 that contains Board Game Categories and whenever I click them, I need excel to pull all the Board Games associated with that category and list them down in COLUMN A. So far, my code is only pulling Board Games that has that specific category...

I have a formula that compares 2 columns and returns the largest value: =INDEX(AH2:AH500,MATCH(MAX(AI2:AI500),AI2:AI500,0)) I would like this formula modified to return the 2nd or even 3rd largest value instead of just the MAX but am not sure how to modify it/apply Aggregate. I am hoping...

Hello, I'm trying to find names based on multiple identical criteria. For clarity I'm looking to find specific NFL players from an entire league. For instance, there are 3 columns: Players/Position/Team. I want to pull each wide receiver (WR) from the Arizona Cardinals (ARI), subsequently...

Hi All, I need to read a txt file and dump the data into a spreadsheet. each txt file with contain two different data sets always starting with DXE and ending with DXS Id like to extract the first set in to column A and second set into column B the data needs to be inserted row by row...

Hello Board: I have created a dynamic crostabulated table using the new XLOOKUP function with SORT and UNIQUE so that I can get dynamic toggle between rows and columns. It worked pretty good. The problem I am having is when I am trying to use SUMIFS to dynamically aggregate both row and column...

I have a table as below. The goal is to extract only first two shop i.d.s from each country having the status either "Active" or "Completed" or "Discontinued". If there are no shops in the country with that status do not extract. If only one than extract only one. Finally the answer should be in...

I helping a friend design a model that consolidate data from several sheets (+6) into a master sheet. His client asked him to design the model so that this process will be automated i.e. the user will fill the data in the input sheets and all the data will be pulled into the master sheet. The...

I have this formula that works but I can seem to convert it to a VBA code. When I run the code, there is no error, but no value appears. Appreciate your help. My formula: INDEX('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78:CR298,AGGREGATE(14,6,(ROW('[Balanced Portfolio...

Hi, Working on a project to aggregate monthly reporting revenues for a company using Excel. Outline: User inputs data and revenue for 1 team (4 additional teams need to be able to do this) An aggregate output needs to show all user inputs across all teams Before month-end, user can...