array formulas

  1. N

    VBA - Executing array formulas

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

    Create Matrix, exhaust the excess funds and avoid circular references

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

    Counting Unique Values in Filtered List That Meet External Criteria

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

    Filter Array Formula Results

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

    Macros to replace array formuas?

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

    Populate expanded list based on compact data

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

    VBA Loop to convert Different Existing Formulas to Array Formulas and Increment a Value

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

    Fun Index array formula based on multiple criteria

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

    Array function with N(Range)?

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

    Return all values

    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">...
  11. K

    SumProduct CountIF DateRange

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

    Dynamic Named Ranges and Pulling from Data Sheet to rest of Workbook

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

    Search text with array formula and find minimum, if the text contains specified text

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

    Pull the first non blank cell data

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

    Count of entries below cumulative threshold without helper column

    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...
  16. 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...
  17. D

    Array formulas containing functions that take arrays as formulas

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

    Array formula to list all People with certain eye color returning blank cells.

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

    Moving Data From One Worksheet to Another Worksheet using VBA

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

    Help streamlining Alternatives (Syllogism) Matrix.

    Hi All, I originally posted a question here about Syllogisms (If A = B and B = C then A = C). Here is the Spreadsheet I need help with. !Please note that to reduce calculation time, I’ve removed all but a text version of most of the formulas, which can be found at the top of their respective...

Some videos you may like

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top