1. A

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    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 . My code as of this point is: =AGGREGATE(15,6,ActData!$Q1:$Q20000/(ActData!$B1:$B20000=[@Player]),1)...
  17. lrobbo314

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

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

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

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

Some videos you may like

This Week's Hot Topics