1. J

    Combining multiple collumns to create a single consolidated list

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

    Update Query With Aggregate

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

    Two Disparate yet Overlapping Matrices (Proportional Equivalencies)?

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

    Aggregate Function showing #DIV/0!

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

    Manipulating Pivot Table via VBA

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

    Sumproduct equivalent for Aggregate dealing with #VALUE results in column

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

    Unable to aggregate numeric or currency fields

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

    Vlookup and Aggregate Formulas

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

    Aggregate and Vlookup formula problems

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

    Consolidate data from mltiple spreadsheets into one.

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

    Seemingly incorrect sum of records when grouped. What's happening?

    Hello, I'm having a problem that is throughout my database, but I will limit it to one example in this post. I'm running a query on records with ID '100' which returns two pieces of data both with EARNED_PREMIUM_USD ($360.01). Logically you could expect the sum of these to be ($720.02), but...
  12. P

    Make Pie Chart Aggregate Data from Formatted Table

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

    Aggregate and Products of Arrays

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

    aggregate array based on condition

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

    Complex excel calculation with a small problem

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

    Find items 1 spreadsheet & show them on a different spread sheet-

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

    Summing a column with certain criteria and filtered rows

    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!
  18. blackorchids2002

    Excel MAX - Help

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

    Percentiles for two columns of data/weighted percentile?

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

    MODE.SNGL works, AGGREGATE does not. Why?

    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?

Some videos you may like

This Week's Hot Topics