array formulas

A while back I discovered an Excel Add-In called FRED Data provided by the Federal Reserve Bank of St. Louis. What it does is allow you to retrieve data from various Federal agencies such as the Consumer Price Index from the U.S. Bureau of Labor Statistics. The problem is that the data is...

Hello, The code below was written to update formulas in F5:N and array formulas in H5:AA, and replace the formulas in F6:A6 and below with the output as values. I am using VBA code to avoid a slow workbook, forcing the formulas to run only after I have updated other data in the workbook. The...

Dear All, below is by far the most complicated case I've ever encounter. Hopefully, that is not the case for you and you will be able to help me solve this. Table 1 and 2 are supposed to be Matrix of Stand Top Tool Type and Stand Code Description. Table 3 is the data set where the relationship...

There have been numerous posts about how to count the number of unique values existing in a specific column of a filtered table. Responses often point to array formulas that use a combination of the FREQUENCY, SUBTOTAL and MATCH functions, for example...

Hi, A B C D Starting Balance 120.00 150.00 200.00 Net Cashflow 0.00 -80.00 500.00 Ending Balance 120.00 30.00 700.00 I know I can get the whole row returned {0.00...

A new question. I have another spreadsheet which is a conglomeration of data regarding truck trips. Unfortunately the design of the sheet is not conducive to getting data. I have been working on a new concept for this which would allow for sorting of different portions of the trips (basic...

Hi All, Thank you for taking time to look over my excel challenge. I have a compact data, which should be extended in a larger sheet. below are both charts. I have given first chart and I would like the second chart to be populated. Prefer formulas, but if it's VBA, that's fine as well...

Hello, I am new to VBA and have run into an issue with an array formula over 255 characters. The array formulas are different for each cell and are already present in each cell, so I would like to loop through a range, increment a number by 1, and then apply the cntrl+shift+enter to each cell...

Hey guys, I'm sure this is possible, but I've had quite a time trying to figure out how to do it. Any help would be awesome. Currently this is basically done manually and it's very time consuming: I know there is a better way. I'll describe a simplified version of what I need below, and then...

I have the following array formula which finds the minimum reference year (H6:Q6) which corresponds to any of the cells below (in H8:Q12) having a non-zero value: ={MIN(IF(H8:Q12<>0,H6:Q6,9999))} However, the formula above mistakes a text string (e.g., "a") as a non-zero value, so I tried to...

Hello everyone- I'm not quite sure how to phrase my question, so I'll ask by way of an example. Let's say I have a dynamic data set like the one below. (Assume people are always adding, deleting, and changing entries.) <colgroup><col width="79" span="2" style="width:60pt">...

I have two books. Book ONE: has separate tabs by month. Each individual inputs the products they sold each day of the month. Book TWO: has separate tabs by week. Each individual is manually adding the products sold and typing them in the respective category. I think a SUMPRODUCT Array...

Hi all, I have seen many advanced spreadsheets that make use of a single Data sheet, where data is only pasted or imported from some data source and drives the rest of the workbook. I have set up a data sheet that records the type of customer, their status (i.e., recent customer, not-recent...

Hello, I'm still conducting my own search of trying to find the answer to this, but in the meantime thought I'd do a post to see if anyone has a ready answer. Difficulty in conducting the search is the multiple conditions. Basically, I need to search an array for a specific string and then...

Hi,I am using an array formula to pull up the first non-blank cell data using this formula =INDEX('Raw Data'!MB2:MB1048576,MATCH(FALSE,ISBLANK(MB2:MB1048576),0)), however I need this to be add onto merged cells and its not working, Excel 2010 does not allow array formulas in a merged cell, I...

Hi, I have the following little table. I'd like a formula that finds the number of values where the cumulative total (of column G) is less than some threshold, tx. I tried {=MIN((SUM(OFFSET(G3,0,0,F3:F19))>tx)*F3:F19)-1} but OFFSET doesn't seem to be arrayable like that. Obviously, this...

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

Is there a way to force excel to take formulas that normally input arrays and instead only take scalars? As an example, say I have an array formula over a number of cells, but I only want to return 0 or greater in any given cell So like {=MAX(0,FunctionThatReturnsAnArray())} say input...

Hey everyone, It seems to me that I am getting strange output of an excel formula and for the life of me I can't figure out why. Could someone explain why I am getting these results? The goal of this array formula is to list all the first names of the people with a certain eye color. In this...

Hello - I'm attempting to convert column data to row data. I currently have 16 columns and I would like to shrink this down to 6 columns. I have about 20 hours experience working with VBA and I'm stuck. Any guidance would be most appreciated. The current headers and layout is as follows...