Is there a way to pick out cells that contain text from multiple columns and aggregate them into one single list?
All - I currently have this query (which works fine):
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 =...
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...
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>
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...
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...
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...
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...
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:
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...
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:
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?
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...
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.
Can anyone tell me why this function works:
and this one does not?
I've tried to use [k] = 1 in AGGREGATE, I've tried CTRL+SHIFT+ENTER but still no luck.