array function

  1. G

    Making use of array formulas in UDFs

    I love the power of array formulas in spreadsheets, however my question is: Can I utilise this same power in a pure VBA User-defined Function? I have had a good look but can't seem to find how to do it? In my current use case, I want to do an INDEX/MATCH multi-value lookup. This screen shot...
  2. S

    Small function with multiple conditions

    I have data (on a table aptly named 'Data') for region, department, sub-department and profit/loss. I've made a drop-down list in cells A1, A2, A3, which filter respectively for region, department, and sub-department. Each drop-down list includes an option for no filter - an "All" option. I'd...
  3. 4

    Can some expert help in MATCH() array form?

    This seems to be an Excel 2013 bug ... <tbody> A B 1 something {=MATCH(FALSE, ISBLANK(A1:A1), 0)} 2 </tbody> (Use Ctrl-Shift ENTER to enter the B1 formula after typing without the curly braces) B1 should generate 1 whereas it returns #N/A. Change the range to A1:A2 and it works...
  4. T

    Array Function Alternatives

    Hi All, My question is as follows: I am trying to match all the values in Column A of Worksheet 2 with values in Worksheet 1 that may be in Columns A, N, P, or X I want my formula to return the cell adjacent the matching cell so I have: {OFFSET(INDEX(NamedRange, MIN(IF(NamedRange = A2...
  5. E

    Populate table given 2 criteria (array function)

    Hi Guys. This is my first forum post, so please bear with me if I ask a bit of a dumb question. I have a dataset that consists of nine headings: (1) Programme; (2) Tracking Area; (3) Indicator; (4 - 9) Countries (e.g. South Africa, France, Columbia, etc.). The dataset is populated with...
  6. M

    Using Evaluate method in VBA to write the formula result to worksheet cell

    I can use "Evaluate" method to write the formula's result into a worksheet cell for a function like: ActiveSheet.Range("C27") = Evaluate("MAX('Test Data'!K:K)") which works fine and I get the value in the cell "C27" but when I try to use it for a formula like this...
  7. A

    "Merging" Columns while ignoring blank cells in Excel 2010 for Mac

    I am having trouble moving data from one column (I am using this column as a helper column for data based on another sheet) into a different column on the same sheet, but without bringing along blank cells. This is the situation: <!--StartFragment--> <colgroup><col width="65" span="2"...
  8. I

    Array function to display list horizontally rather than vertically.

    I have a really great formula (not my own design) which does 99% of what I want but falls at the last hurdle. My spreadsheet is basically a 2 column list of things, for example fruits, with a corresponding attribute, such as colours. So A:A might contain apple, apple, banana, banana... and the...
  9. Z

    Copy/Paste Excel VBA using Array

    Hello, I am trying to copy about 100 formulas from a row, and then paste them 2 rows below where I copied, and then have it filldown til the last row.The issue is that the 100 formulas are not in the immediate columns, it can go A4 - F4, and then M4-N4, P4-T4... and so on. Here is what I have...
  10. B

    IFNA not working on FILTERXML in an array function

    I have XML data delivered to cell B3 from a web service. When I extract the data using FILTERXML the cells in the array that go beyond the length of data delivered in the XML display #N/A. If I encase the FILTERXML in an IFNA or 'IF(ISNA...' it still displays #N/A. I am trying to not use VBA in...
  11. P

    max of sumifs of multiple columns

    Hi All, I'm new here, so sorry for any newb mistakes... I'm trying to find the max between sumifs of multiple adjacent columns in a range, meaning something like this: =max(sumifs(A1:A5, criteriaRange1, criteria1...), sumifs(B1:B5, criteriaRange1, criteria1...), sumifs(C1:C5, criteriaRange1...
  12. R

    Using offset in an array function

    My data is laid out in a large grid (194R X 50C) E6:BB6 is top row and E199:BB199 is bottom row. I want to check if some element is in each row of the grid and create an arrray of 1s and 0s. This is to multiply with another array of 1s and 0s that I already have so I can sum up the result...
  13. G

    Problem with Indirect function in an Array Formula

    Hi, i am trying use Indirect function in an array formula. ="{="&INDIRECT("J1")&"(IF((Sheet 1'!$B$2:$B$3761=$A9)*('Sheet 1'!$H$2:$H$3761=1)*('Sheet 1'!$J$2:$J$3761=1),'Sheet 1'!M$2:M$3761))}" Where the cell J1 contains a cell dropdown with options such as product, average, min, max and the user...
  14. R

    Help with an array that requires "and" + "or"

    Hi, I am trying to create an array formula that does the following: Counts the number of times "Expression of wish" appears in 1 column and then "p" or "f" appears in another. So if I get Expression of wish appearing and P it will count it If in another row Expression of wish appears and F...
  15. T

    User Defined Functions and Arrays

    Two questions: 1) How can I pass two arrays into a user defined function? 2) How can I access single members of an array within a function? I'm trying to write a function that will automate my data-work up. I want to pass two arrays to this function, a set of x values, and a set of y...
  16. S

    Sum months in range without pivot table or array function?

    After a day and a half of digging, I haven't quite found the answer I am looking for here. I have a large dump of monthly data related to properties which is monthly for 10 years. About 13k rows of accounts and 120 months I want to quickly sum the months in each year, and since I need to use...
  17. B

    Using VBA Arrays

    I have build an excel file with VBA code that looks at the below matrix (which can adjust both on the x and y axis (larger or smaller matrix) and will hlookup the first "x" in a particular row and returns the associated Company (C1, C2, C3 etc). So I end up with another matrix this size with a...

Some videos you may like

This Week's Hot Topics

Top