aggregate

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

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?

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

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

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

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

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

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

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

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

Hi guys, Is there a way to pick out cells that contain text from multiple columns and aggregate them into one single list? eg: Problem: <tbody> red circle table square book Blue triangle pencil green </tbody> Result needed: <tbody> red Blue green circle...

All - I currently have this query (which works fine): UPDATE END_INSPECTION AS e INNER JOIN qa33EndInspection_link AS q ON e.Batch = q.Batch AND e.Material = q.Material SET e.QI_End_Inspection = q.[End of Inspection] --(I want the MAX() of this!) WHERE e.Batch = q.Batch AND e.Material =...

Hi All Struggling with an issue that I am sure someone must have faced before, but I can't find an answer when I searched the forum, so hoping someone can help me. Essentially I am trying to convert an OD matrix into another OD matrix, by which I mean I have OD matrices from two disparate...

Dear All, I use an aggregate function to determine the average of the non-error values in a list, once the list is populated it works perfectly, however while the list is empty it returns "#DIV/0!". In such instances I want the formula to return a blank, however, as much as I try I can't get a...

I have daily data that looks like this: <colgroup><col span="2"><col span="3"><col span="3"></colgroup><tbody> Date Code Filter 1 Filter 2 Filter 3 Var1 Var2 Var3 5-Jan-14 3050 Yes No Yes 15.9 15.9 5-Jan-14 3050 No No Yes 15.9 15.9 5-Jan-14 5470 No Yes Yes 24.75 21.1 21.1 6-Jan-14...

How would one write a formula to do what, for example, =AGGREGATE(1,7,NovDataTable[Inc Closed Date - Inc Date]) does? I have a data table called NovDataTable and want a formula to get the average values in the table column [Inc Closed Date - Inc Date], without resorting to the aggregate...

Hi All, I have a large spreadsheet and am just learning Power View and PowerPivot - I have a load of numeric and currency fields in my spreadsheet, but only one (the unique identifier) is coming up with the sigma sign next to it meaning that I can aggregate the data. I want to be able to...

I have a rather large spreadsheet and in one worksheet I put hours associated with certain labor tasks. I would like the hours to translate to another spreadsheet in another format without having to re-type them. I have a formula that was working, but does not seem to be picking up all the...

I have a spreadsheet where I enter codes that labor hours will be charged to. I also have a spreadsheet where I would like the codes to transfer over to. I have formulas in that are working ... somewhat. It isn't capturing all the codes that have labor. This is the spreadsheet I input the...

Hello Folks, I have approximately 1000 sets of 80 excel spreadsheets. I am trying to combine each of these sets of 80 spreadsheets to produce one main spreadsheet for each of the 1000 sets. Basically at this stage I would need to copy and paste data from each individually, onto one central...