aggregate

Over the last few days, I have been teaching myself a few things in VBA in order to create a more efficient personal finance tracker that allows me to enter a few items into a user form and automatically populates a Formatted Table. Along with this functionality, I am wanting to have a Pie chart...

It seems strange to me that AGGREGATE doesn't return the expected result when using the product of two arrays. I have the following example: =AGGREGATE(9,6,(F26:F880)*NOT(ISERROR(H26:H880))) where cols F and H have values and errors. I simply wanted to get the sum of the values in F ignoring...

I am trying to get this done with a single array formula: For row in a column array, if the array row tests positive for a condition, calculate the average of the next N rows in a different array of equal size. Each of these averages would be placed in an array, and then the average of this...

Hi All, I have, at least what I consider, a complex calculation that I have discovered a small error in... problem is, I can't figure out how to solve it. It's driving me crazy! I have the following table: <tbody> COL B COL C COL D COL E COL F No. Name Views Fails Success Rate 1 Name1 3...

I have an "expenses" spreadsheet, an "income" spreadsheet and a "Shipping Account" spreadsheet. In the 'expenses' spreadsheet: Column B3:B200 contains descriptive text which is selected from a drop down menu I created using Data validation. This contains several descriptions and 3 of them all...

I have a large spreadsheet in which I want to sum the values in a column, as long as A) The row is not filtered out, and B) the corresponding value in another column equals something (eg. A1="Approved"). I've played around with SUBTOTAL and SUMPRODUCT and can't get it to work. Ideas? Thanks!

Hi Masters, I'll be needing your help again. Just to give you a quick overview, I have a file that list all our shipment orders. It has way bill number and we charge our customers based on the billable weight multiplied by the rate. The problem with the file that I was trying to consolidate...

Hi, First post here. I found the board by googling various problems and this board came up a lot. I'm using Excel 2010. Here's my problem: I have two sets of data. Column B is the price per ton paid for grapes in a year. Column C is the number of tons for each price paid. For example: B...

Hello, Can anyone tell me why this function works: =MODE.SNGL({1,2,3,1,2,1}) and this one does not? =AGGREGATE(13,4,{1,2,3,1,2,1}) I've tried to use [k] = 1 in AGGREGATE, I've tried CTRL+SHIFT+ENTER but still no luck. Anyone?

Good Morning all, I am very stuck on a problem and hope to get some help. I want to create a DAX measure that aggregates each total by day. Example <tbody> 1/1 8000...

Hi everyone, Here is my problem: Lets say I have 600+ separate excel files containing 3 tabs. The primary sheet is formatted as a form that has 100 questions on it. All the sheets follow identical formatting. The idea is to have some kind of tool/function/query/macro that allows me to answer...

Really hope you can help me on this.</SPAN> </SPAN> I`m using Excel 2013, I need to plug into another formula an array that differentiates hidden and visible rows (filtered and not filtered rows) </SPAN></SPAN> The way I was approaching this was creating a dummy column with the aggregate...

I have a sample workbook (attached by link) that has 5 worksheets of raw data. Column A contains dates (9/3/2013 - 10/24/2013). column headers are categories/variables (ie: sales, units, etc...) I need a formula that will will give the me the rank for a date (10/24/2013) of of all the...

Hello, Does anyone know of a way to get the rank of an aggregate of multiple columns across sheets in a workbook please? I found a non-workable but good formula: =SUMPRODUCT(--(A$2:A$10+B$2:B$10>A2+B2))+1 BUT! it doesn't allow for a lookup/search variable - the columns are "locked" it does...

I have never before had to perform this simple objective with PT. How can I make a rule that determines which values from the original data are used in the aggregate? For example if MyCol is the aggregate, how can I only include values from MyCol that are >10? Note that MyCol is used as the...

Hey guys, I need to get rid of the aggregate sum and modify it to show just the sum of one column on a pivot table: Sum of INV $ is a formula, which multiplies sum of AVG * cost (which is not shown). The sum of INV numbers are correct, however if I collapse the category (blank), I get an...

Hello, I have come close to what I need but need help on the last bit. I hope someone can please help me solve this. I need to produce a gantt chart for batch jobs that run multiple times a day and show that out for a week. So job1 will have multiple rows for each start and end time for each...

Hello, and thanks for providing this forum. I use Excel 2010. I do not understand VBA. I am looking at the Excel Aggregate command video on You Tube at Excel 2010 Magic Trick 797: Check Items In List And Have Them Appear On New Sheet - AGGREGATE - YouTube (Excel Trick 797). I want to gather...

Hi, I'd like to create a report that displays the values according to 3 criteria. I have 3 drop down boxes that one can select a customer's name, brand, and date (format of Jan 2013 Week Ending 1/26/2013). My drop downs are in a separate sheet under a5, b5, and c5 respectively. I will then need...

Hi, I have done a bunch of searching but just can't really figure out how to phrase what I am looking for, so hopefully someone here can help me out. I have an array that has a bunch of names with dollar values of an account of theirs next to them. People have multiple accounts and come up on...