All Questions
92
questions
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
1
vote
0
answers
111
views
Excel : How to write everything I want on a single array formula
On Excel, I have a table with many numeric results on B column obtained by a array formula.
For example:
+---+------------+-------------+
| | A | B |
+---+------------+----------...
- 73
0
votes
2
answers
9k
views
Array formula only returns the first value
I'm trying to use an array function in Excel, the formula is to return the value of filtered table. This is my formula:
{=INDEX(A:A,MIN(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A31)-ROW(A2),0)),ROW(A2:A31))))}
...
- 25
1
vote
1
answer
170
views
Excel - N/a In cell causes formula searching range to break
I have a formula in excel checking a range and returning only cells which contain Monday.
However, some cells in the range have an #N/A and this is causing my formula to break.
If I remove them ...
- 145
0
votes
2
answers
568
views
Checking if sum of arbitrary amount of cells in a column matches a cell in another
The result I would like to achieve is Column 'C':
To make this spreadsheet easier to copy/paste into your own, here is copy-pastable data:
ID
1
2
3
4
5
6
7
8
9
Number
1000
300
800
300
200
ID
1
1
...
- 119
0
votes
1
answer
1k
views
Need an array index/match formula for multiple duplicate results
Here is the effect that I am attempting to achieve, in Column C:
I am trying to make Column C "detect" which entries from D/E are present in A/B. I think one of the problems is that I am not allowed ...
- 119
1
vote
2
answers
3k
views
Excel - array function to remove spaces
I have a column of text items that can contain spaces (empty cells) in between items. On another sheet, I'm trying to use an array formula over the existing range to filter out those empty spaces.
...
- 13
1
vote
2
answers
100
views
Use Excel formulae results in array for nested function
In Excel, is it possible to have multiple formulae slot together into an array for a nested function?
Consider
=SMALL({10\15\20\25\30},1)
The formula returns the smallest value in the array. Now I'...
0
votes
1
answer
300
views
Excel Multi Conditional Formatting Questions
I need a formula that will look at the text in one cell and compare it to three other columns and highlight it a specified color.
Here's the example:
Data
Cell A2 contains "This is the sample text ...
- 1
1
vote
2
answers
2k
views
Excel: using a formula to only sum from above row until next blank cell?
I'm a bit of a noob so bear with me. I'm making a spreadsheet of various products bought and sold at various prices. The table is sorted by product, with a blank row separating each complete buy-sell ...
- 11
0
votes
4
answers
206
views
Multi-cell array furmulae, nested
My data is on the first 2 tables (A1:B6 and D1:F6):
My intended final result is on I1:I4 for every corresponding value in H1:H4.
For instance, you take the value of "A", you find every ...
- 2,487