All Questions
92
questions
1
vote
2
answers
11k
views
Excel INDEX MATCH looking between two dates of my choosing
I'm using Excel to log a list of items (name of items in column D) and then how long that item took to produce in column G.
I'm doing an INDEX MATCH to search for the worst performing product of the ...
CommunityBot
- 1
0
votes
1
answer
3k
views
Need to extract multiple matches from a list of values
I am trying to extract data from a worksheet using an array formula but it will only return one (the first it finds) value. The formula I am using is:
=INDEX($C$2:$C$8, SMALL(IF($B12=$B$2:$B$8, ROW($...
CommunityBot
- 1
0
votes
3
answers
75
views
Functions based on multiple criteria
I want to do some count functions based on multiple criteria. I have a sample dataset here:
I want to find the number of Males if BD = MCD or FSGS.
For this I tried:
=COUNTIF(Gender,"M")+...
- 8,931
1
vote
1
answer
61
views
Nested IF functions in Median causes error in array
I have looked at a few solutions in this forum and tried their answers, but not been able to reach a solution. I have a data set I want to determine a conditional median for.
Sample Dataset:
Desired ...
- 159k
0
votes
1
answer
138
views
Join/Mix dynamic and static values on Array = alternative to HSTACK that works in older MS Excel version (No VBA, please)
I need to join some value(s) from some cell(s) with a "static" bunch of other values into an Excel array... BUT I'd like to avoid using VBA for it and I need something that works also with ...
- 1,996
0
votes
0
answers
52
views
Identifying a Specific Table Hierarchy... Don't Know Where to Start
I am trying to identify/mark records where an entry exists in a specific column. While that may be pretty straightforward using a loop, I must also mark those records representing the "Parent&...
- 8,931
0
votes
2
answers
7k
views
How to append a value to an array in Excel
I have a formula, which returns an array. I would like to append another value to this array. Is there any way to do this with a formula without VBA in Excel?
e.g.
{1, 2, 3} -> {1, 2, 3, 4}
or ...
0
votes
0
answers
96
views
Referencing and evaluating another cell's array formula to create master array using VSTACK, in Excel without VBA/Macros
I'm trying to accomplish this task in Excel without VBA, Macros, or Power Query.
Here is a workbook with example sheets of data, an intermediate sheet, and a sheet showing the desired outcome: https://...
- 1
0
votes
1
answer
297
views
Dynamic Array Subtotal that changes its column sizes without using VBA or volatile functions
I need to make a dynamic array subtotal that changes its column size by another dynamic array. The example sheet, challenge, conditions, and attempts I've made are below.
[Example Sheet]
Challenge:
...
- 361
3
votes
3
answers
24k
views
MS Excel: Sumproduct only visible rows (use sumproduct with subtotal(9,array))
Pretty straightforward.
I want to use a SUMPRODUCT but only for visible rows.
I've tried =SUMPRODUCT(A2:A10, SUBTOTAL(9,B2:B10)) ... didn't work, even as an array formula.
I'm probably missing ...
- 7,360
0
votes
1
answer
117
views
look for empty cell between non empty cells in one row and then put 0
enter image description here enter image description here
A B C
abc 30 40
def 4646
ghi 45 3234
So, I want to write a macro code where it takes all three columns into consideration at once....
- 1,898
0
votes
1
answer
2k
views
Indirectly compare two arrays in Excel
Let's say I have the following two differently sized arrays in Excel:
array_1
array_2
A
A
B
C
D
E
E
I want to write a function COMPARE(array_1, array_2) where this returns (as an array) all of ...
- 23.4k
0
votes
1
answer
2k
views
Excel use formula to produce a conditional comma-separated list
I have a dataset in which the right 5 columns indicate with a Y/N if each row applies to that option of 5 potential options:
I am looking to add one final column that uses a formula to check for each ...
- 737
0
votes
2
answers
377
views
How do I create an array in excel using the "coordinate" values of each cell?
Sorry if this is trivial, but I can't seem to get this to work. I have a column of values, say from A2 to A10, and a row of values, say from B1 to P1. I want to fill out the values of the array from ...
- 21
0
votes
1
answer
383
views
Using INDEX/XMATCH to pass an array through to AGGREGATE in Excel resulting in #Value! error when trying to get MEDIAN
I have a row of companies in an array to the right. Using INDEX() + XMATCH(), I check the company names against a table and pull out the corresponding ratios (decimal number). This creates a spilled ...
- 26