All Questions
92
questions
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")+...
- 15
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 ...
- 15
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&...
- 1
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:
...
- 3
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
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 ...
- 7
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 ...
- 9
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 ...
- 103
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 ...
0
votes
1
answer
854
views
How to Create a Spilling Array that Takes a Rolling Average
Reversing the order of the dates works, but I'm more interested in knowing how to do it in this specific way. I'm trying to take a rolling average, ideally it would be nice to change the depth from a ...
0
votes
2
answers
861
views
Excel nested dynamic array formula, filtering distinct differences only
I'm working with a big dataset with many types of products to be uploaded to Shopify
In order for the correct dropdown(s) to be displayed, I have to let Shopify know what the "children" (...
- 21
0
votes
2
answers
691
views
Excel VBA - Dynamic Array not Working
I am trying to make an array that stores all of the values in Column A from "Workbook B" so I can then reference and see if a cell's value is in that array in Column A from "Workbook A&...
user1462614
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
1
answer
519
views
Return column headers as array (no blank rows) if column sum is not zero
I have a table with columns of components needed to create various products. This table is populated automatically as output data from another table.
For this example, we'll use this one:
S Widgets
M ...
0
votes
1
answer
1k
views
Excel: AGGREGATE function with an array
I'm referring to the data below, and I would like to calculate a conditional mean:
2
0
#N/A
0
0
0
0
-2
20
0
25
0
0
0
0
0
0
0
0
0
154
25
0
120
0
0
0
0
0
-2
2
0
0
0
0
0
0
0
0
0
57
0
-57
1299
0
0
0
0
0
#...
- 103
0
votes
0
answers
169
views
Array formula to return true across multiple dates combined with if "sepecific text"= true
I am trying a formula that is sets out dates for a week and lists jobs on each day based on the days they are booked in. the array formula is designed to look up data on my current sheet (cell A1) on ...
0
votes
1
answer
1k
views
How do I count the number of times a value appears in a 2D array in excel while also having a date condition that varies by row
I'm trying to create a function that calculates the number of times a specific text value appears in a 2D array, while also meeting a conditional that is unique to each row.
Essentially its just a ...
- 1
1
vote
0
answers
87
views
Sum of array in one cell produces different result from sum of the array elements
I want to do a simple calculation (say, addition) on a set of values on the basis of some keys, as in:
Col A Col B Col C Col D Col E
Row 1 **Key** **...
- 11
0
votes
1
answer
514
views
Inserting Array Formula with VBA
I am trying to insert an array formula using VBA but I keep getting the error
"Unable to set the FormulaArray property of the Range class"
Sub Insert()
Range("J2").Select
...
- 1
0
votes
1
answer
42
views
Can MS Excel be used to reiteratively check intervals in multiple lines?
I want Excel to check intervals in all the lines in another sheet, finally returning true if my value is in any of those intervals, and fetching a value in that given line if true.
Let's call the ...
- 1
2
votes
2
answers
3k
views
In Excel, can I "splice" multiple Dynamic/Spilled Arrays into a single Dynamic/Spilled Array?
IN A NUTSHELL
I have four (say) columns of data, each a Dynamic/Spilled Array. I'd like to "stack" them into another column so as to produce a single Dynamic/Spilled Array comprising the "splice" of ...
- 464
0
votes
1
answer
59
views
Excel to return matched value in rows from a column array
Super stumped, I am trying to return the values of column based on the value of a cell found in a number of columns. I have attached an image to better describe what I mean. Table So, I'd like to ...
0
votes
1
answer
81
views
Can someone help me change this array formula to normal formula?
I am having problems in terms of speed with a file using this formula.
{=IFERROR(INDEX(Query1[COGSDetailSKU.RecipeCode], SMALL(IF(($B$1=Query1[COGSDetailSKU.SKUName]), MATCH(ROW(Query1[COGSDetailSKU....
- 1
0
votes
3
answers
185
views
Excel - Best way to structure lists of lists?
I have a sheet of cooking recipes. Each recipe has a name and a list of ingredients and their quantities. For example
Apple Pie
x4 Apples
x8 Flour
x2 Butter
Scrambled Eggs
x2 Eggs
...
- 113
0
votes
1
answer
152
views
How to count unique value in a table with other condition?
I have this (sample) source of data where I want to count the number of unique Client/ID in total and from a specif location.
I am good at counting the number of unique clients in total using MS ...
- 103
0
votes
3
answers
110
views
Excel array operation: how to get this array?
I have the following two arrays:
{a,b,c}
{d,e,f}
How do I get this array?
{a*d,b*e,c*f}
For example, one array is {0,1,1}, another is {255,122,0}. I need this array:
{0,122,0}
It is really ...
- 297
0
votes
1
answer
35
views
Excel - Using Two Data Tables to Build Output Table
I am trying to compare the locations of numbers in two different data tables and create an output summing the total revenue booked per day of the week based off the location of the day of the week in ...
- 1
1
vote
1
answer
380
views
Dynamic Arrays for earlier Excel versions
Is there a way to have an array formula "spill over" in earlier Excel versions? I have heard that the new Excel will bring dynamic arrays, but need this functionality on an older version of Excel.
I ...
- 139
0
votes
0
answers
331
views
Why are array formulas not filling adjacent cells as they should?
When using formula arrays with multi-cell results, the returned array is outputted in the adjacent cells as the formular arrays (as I've experienced in the past and as documented).
Why is it that if ...
- 1
0
votes
1
answer
272
views
Excel: Array formula to sum values based on matching month and year results in value error
I am trying to sum a bunch of numeric values in
'Product Detail zPart (ALL TIME)'!$O:$O
using formula
{=SUMPRODUCT((MONTH('Product Detail zPart (ALL TIME)'!$F:$F)=MONTH(Summary!C$2))*(YEAR('...
- 19
0
votes
2
answers
1k
views
Formula for finding longest string match in a cell from a list and returning the matching string
In an Excel workbook, I have a formula that searches for matches to a cell from a list. It works, but doesn't find the longest string match. How do I modify the formula so it returns the longest ...
- 101
-1
votes
2
answers
323
views
Excel Sumif Array
I'm trying to use an array formula with sumif and know the correct format is along the lines of
=sum(sumif(A:A,B:B,{"hello","hi"})),
However when I try to use > or < than operators, the ...
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 ...
- 11
0
votes
1
answer
51
views
Excel array (2 variables)
I have the following sheet https://1drv.ms/x/s!Ai6fM6lT5gt6h00EUCmpnuwlgNtr
It consists of table 1 that is populated by a list (with an array).
Goal: I want to populate a second table based on the ...
- 1
1
vote
0
answers
30
views
Using an array to output results from a lookup criteria
I've tried searching the site, so sorry if this is a repeat. I'm not sure exactly how to phrase what I'm trying to do in excel.
Essentially what I have is a data sheet and a summary sheet. On the ...
- 11
0
votes
3
answers
223
views
Simple Array Formula in Excel not comparing the right values
I'm trying to calculate with an Array Formula (in Cell D2) what i'm calculating in cell D11. The issue i'm having is that it appears my Array Formula is not evaluating the inputted array one-by-one. ...
0
votes
0
answers
213
views
Excel: Ignore the last value in series 1 if COUNT(series1) > COUNT(series2)
I am currently using excel to do some trading. In one series I have a column of "buy prices" and the column next to it is "sell prices".
I want to divide all of my sell prices by all of my buy ...
2
votes
1
answer
3k
views
Find position of first uppercase character in excel string
I need to find the position of the first uppercase character in an Excel string. There are lots of resources on the web that have the same (or effectively the same) solution for this:
=MIN(IF(ISERROR(...
- 191
1
vote
2
answers
202
views
Count max values compared with previous array
I try to count the number of values in a row that are equal are greater to MAX() value of a (part) of the column above.
For example: compared to the 3 cells above itself. In E comes the 'count'.
i ...
- 21
0
votes
1
answer
111
views
Excel - Find the document of a given type, with the closest due date
I am operating on 2 Tables.
"Table 1" is a list of current documents that I import and want to keep unchanged. The file is unformatted in any way.
Table 2 is a list of customers that I operate upon ...
- 1
0
votes
1
answer
3k
views
Return list of all values that match any of a few criteria
I'm new to this site so hope I'm doing this right!
I've made use of @BruceWayne array formula in the answer to the follow question:
Return list of all values that match criteria
My question is, can ...
- 1
1
vote
2
answers
298
views
LookUP Value returning multiple concatenated values
I have two columns, one is a 'Category' with a simple code and the other column named 'Tree/group qualities' with one, or many of the following categories that are, if multiple, concatenated using ...
G.Spanish
1
vote
0
answers
74
views
MAX Result of multpiple results from INDEX and MATCH
I am working with 3 columns, fname, lname, & date. Where there will be multiple duplicates of fname and lname. I am trying to write an INDEX & MATCH that will display the MAX result or the ...
- 11
0
votes
2
answers
2k
views
How do I use a conditional array with more than one condition to calculate percentiles?
I have a table in excel with the following data:
+-------------------+----------------------+----------+
| Contribution Type | % Contribution Match | % Salary |
+-------------------+-----------------...
- 35
2
votes
2
answers
244
views
Using GCD Function With Arrays in Excel
I'm having trouble determining GCD from a dataset. I'm trying to determine the quantity of items in a box of any given item in a warehouse without physically looking at each item on the shelves.
...
- 25
0
votes
0
answers
166
views
How do I enter values instead of cell range into the Matrix Functions?
I have a simple system of equations that needs solved repeatedly, based on slightly different input each time. The user is comfortable with Excel, and solving a system of equations is easy within ...
- 101
2
votes
1
answer
2k
views
Excel Formula To Show Top X Percentage Of An Array
Using the simple data set below, starting in A1, with headers:
Order Time
a 1
b 1
c 2
d 4
e 4
f 5
g 6
h 7
i 500
j 600
I want to know in ...
- 7,485
0
votes
1
answer
238
views
Get column header of first cell of longest consecutive set of cells below a given threshold
I have a formula that calculates the length of the longest group of consecutive cells in a row of data that fall below a certain threshold.
I would like to show the column header of the first cell of ...
- 1