# aggregate

1. ### Power Query: Best way to duplicate data, flip signage, change business unit

Hi, I have some table data where I would like to generate an aggregate reciprocal position. This relates to modelling allocation of costs within an organisation. E.g. 5 departments a, b, c, d, e receive costs of -1, -2, -3, -4, -5 respectively. I would like to generate in the cleanest and...
2. ### suppress dialog boxes vba NOT applicatoin.displayalert = false

Here's my code: aggregate = "'" & ThisWorkbook.Path & "\" & counter & "\[aggregator.xlsm]" & ws.Name & "'!percent" prior = "[" & counter & ".xlsm]" & ws.Name & "!" & counter & "MBO" If counter = qt Then rng.Formula = "=iferror(" & aggregate & ",""oddly...
3. ### Formula to split and update master tables to sub tables - per customers (aggregate, replace )

Hi all, Sorry I did submit this to a wrong post. Here I come. I have been trying to work out this formula to auto populate per customer's status with a few steps, by implementing multiple excel functions e.g: replace, cell, aggregate, index: B1 Formula =...
4. ### Aggregate with OR argument within

Hello everyone, What is working: 25th Percentile for 1 criteria -AGGREGATE(18,6,\$C\$1:\$C\$10/(\$B\$1:\$B\$10="Green"),0.25) 25th Percentile for 2 criteria - AGGREGATE(18,6,\$C\$1:\$C\$10/((\$B\$1:\$B\$10="Green")*(\$A\$1:\$A\$10="January")),0.25) What I am trying to do: Select January or March for the month...
5. ### Need help on Auto calculating aggreggates

Please, I made a simple template to take grades of my pupils. In the templates, some subjects on a continuum are compulsory (H - K) while the remaining (L- P) are optional, but 2 of them (best 2 with good grades ) to be added to H-K to give the aggregate in Q as indicated in the table below...
6. ### Array Variables in Excel Formulas

Hi All, I recently discovered a way to use the aggregate function in conjunction with index / match to identify min / max values for like records in a table (i.e. if I have a table of animals and their weights, I could use this function to identify the smallest dog, or the largest cat, etc.)...
7. ### Swapping AGGREGATE with SMALL for backwards compatibility with Excel 2007

Hi, I'm trying to make this formula compatible with Excel 2007, I think I just need to replace AGGREGATE with SMALL but I'm getting errors. The INDIRECTS are referring to Data Validation cells where I have user-selectable filter criteria. Here is the original formula that works in modern...
8. ### Need Help creating formula: COOLING CONCRETE USING ICE

Hello, If anyone is up to the challenge.. I have this formula below that I need to modify but don't know how... And I really need the boards help.. What I need is instead of the formula returning: "T = final temperature of concrete mixture (deg F)" I need it to return: "Wi, Weight of the Ice...
9. ### Best Method to Aggregate Monthly Data

Hello! I have monthly financial data with the months across the row and the financial metrics down the column. I need to aggregate these months into quarterly, annual, or quarterly/annual displays for the user. I wrote formulas to generate the correct headings based on the user selection...
10. ### Crosstab - Sort on Aggregate Sum

I added the Aggregate Sum to my crosstab query and the user has asked me to sort the columns by this Sum, but I can't see any way to do this. Any help is appreciated!
11. ### Conditional formatting: Two formats on one row, one with precence over the other

I have a long list of expenses, with accounts in the A column and amounts from different business entities in the columns to the right. The last column contains a formula that ranks the aggregate expense amounts. The rows are conditionally formatted so that rows with the 10 highest aggregate...
12. ### Using aggregate function with sum-product to avoid hidden rows

Good day experts, I'm using a sumproduct formula =(sumproduct (Handle time * contacts)/(sum contacts) How do I use aggregate with this to avoid hidden rows?
13. ### Hidden Rows and AVERAGEIF formula

55 year old newbie - please be gentle. I have a spread sheet in which I wish to use the formula AVERAGEIF on a column of data which includes +ve, -ve, zero and errors. I wish to present the data in a series of cells relating the average to conditions in several columns. So one average could be...
14. ### Aggregate Function with Text - Take 2

Hi. I am looking to be able to when filtering a list on Sheet2 which is created/populated by using index/match which looks to a database contained on Sheet1 to count/sum up TEXT results ignoring hidden rows which correctly are filtered out. Numbers work but text I am having a problem with -...
15. ### Aggregate function use with text

Hi all. I am trying various combinations of the Aggregate function to count the number of time the letter x appears in column that adjusts when you filter the list. I want it to be dynamic as when i filter the list by other criteria I only want to count the filter list where x appears. So, in...
16. ### Aggregate, Min, Exclude 0's

Hi. I am trying to use the aggregate to find the minimum fantasy football player score for a single game. I am using the function as shown in this video https://youtu.be/Oxj-wMGjAW0?t=340 . My code as of this point is: =AGGREGATE(15,6,ActData!\$Q1:\$Q20000/(ActData!\$B1:\$B20000=[@Player]),1)...
17. ### Aggregate Var.P

I have been trying to get Var.P to work in an array formula and I haven't been able to figure it out. I tried using Sumproduct and kind of got it to work, but it was incorporating 0s for the non-matches and gave the wrong answer. So, I wanted to use aggregate to ignore errors. This is the...
18. ### find longest duration between 2 dates based on specic criteria

Hello, this formula works great, (=MAX(TODAY()-MAX(Log!\$D\$5:\$D\$482),AGGREGATE(14,4,Log!\$D\$6:\$D\$482-Log!\$D\$5:\$D\$481,1))-1)) to find the longest duration between all incidents i'm just wondering if I can add an "if" to the beginning of the formula, i am wanting to find the longest duration...
19. ### Aggregate function ignoring 0

Hello, Is there a possibility to use the aggregate function such as the below and ignore a certain values (in my case 0) of the range investigated? =Aggregate(5,4,range) Thank you in advance. Shiro
20. ### use wildcard to return multiple items from one lookup value

I have a large dataset, rows and columns. Column 1 (name), column 2 (another name), column 3 (email). I have a single lookup that spans the 2 "name" columns, and returns the 3 matching columns. Currently it works but you have to plug in the exact name to get a return. Here is the formula...