33,119 questions
0
votes
1
answer
44
views
Incorrect currency being displayed using Stock Data Type
The inbuilt stock data type in Excel is not correctly displaying the price of the stock. The issue is with stocks on the LSE as they are reported in pence and not dollars, Excel is reporting it as ...
- 1
1
vote
2
answers
103
views
Searching for a range of dates in Excel Using VBA
I have a problem in making a search for a range of dates with VBA. Using the start date as the default search in the date range and when find the same date start to count (x) in the range between the ...
- 23
3
votes
1
answer
101
views
Trying to populate a list from another sheet by a single reference number
I am working on a handy reference sheet for my DnD Games, that includes 10 pre-planned encounters of varying difficulty that I want to reference on an Initiative Sheet by referencing the Encounter ...
Best practices
3
votes
17
replies
278
views
SpellNumber Simplified Using LAMBDA() function
Here is the custom function to spell number to words using LAMBDA(). I would appreciate any improvement of the function or simplification if possible. Also suggest best practice for the function.
=...
- 38.1k
0
votes
1
answer
111
views
Excel INDEX MATCH with single row and multiple columns reference
I need to find openings in a group of people's schedules because I can't merge them easily with any native web program we use the calendars in. I will be looking at each person's calendar at a time ...
1
vote
2
answers
97
views
Return each unique combination from 2 columns and return other information from the same row
I have a tool I am working on using the latest version of Excel (whatever it is) and I have a table which looks like this:
I want to take each unique combination of values from the Code and POBox ...
- 35
1
vote
2
answers
107
views
How to check if two cells in the same column contain matching text in Excel?
Is there an Excel formula that can detect whether two cells in the same column contain similar or partially matching text?
For example:
F3 contains: "regulation BI, how to stop exploitation"...
Advice
0
votes
8
replies
141
views
Excel conditional formatting: how to format entire RELATIVE columns based on single row value
My question is quite straightforward - or at least, it should be!
I have an Excel sheet with dates and weekdays laid out across the top three rows. I want to highlight all columns in a particular ...
- 1,516
-4
votes
2
answers
176
views
Move Labor Cost Formula from Sheets to Excel
Problem
I have created quite a convoluted formula in Google Sheets that Sheets doesn't seem powerful enough to handle for more than ~80 rows of data, so I wanted to try moving it over to Excel. Seems ...
- 27
Advice
0
votes
4
replies
105
views
How to mark two largest values?
I need advice on how to mark the two largest values among a set of values.
1
3
2
4
FALSE
TRUE
FALSE
TRUE
- 5,607
Advice
1
vote
3
replies
126
views
COUNTIFS - Excel Date Matching
Simply, I have two values to lookup
1st is a name as "A"
1st is a date that I should look for its month given as "9/1/2025"
Apparently, I used these values as conditions in a ...
- 39
0
votes
5
answers
147
views
Finding Max Value and Returning Horizontal Array belonging to the Max Value
I attempted to use a mixture of filter/index/xlookup with multiple criteria.
I am given 4 rows of data (Option 1-4) for 3 different groups (A-C). I need to compare the max V1 value for each group for ...
- 39
-4
votes
1
answer
52
views
Unable to recreate an Excel formula in Power Query [closed]
I am trying to recreate this Excel formula in Power Query. I have very little idea what I am doing so I need some help.
=\[@\[DOC ID\]\] &"-"&COUNTIF(\[DOC ID\],\[@\[DOC ID\]\])
I ...
0
votes
3
answers
145
views
Array Functions
I am currently learning the new array functions in Excel. I've been learning them for a bit, but still learning.
I am trying to create a dynamic YTD type report off some dummy data however I can't ...
- 8,576
0
votes
3
answers
152
views
Workaround for #spill! in Excel when creating random integer array of random size (with constraints)?
How to construct a random size, random integer array with (exact) target sum value with a single Excel function (or 'best' viable/working solution given the 'known' volatile issue with this sort of ...
- 2,644
-1
votes
2
answers
145
views
I want to add a minimum run time for an Excel engine simulation
I'm new to stackoverflow and was looking for some help.
I'm building an Excel formula (using LET) to enforce a minimum run length for a CHP engine. The logic is:
If today's suggested load (row 8) >...
0
votes
1
answer
121
views
Shift cells from right to left side
For automation purposes, I want to reflect the cells from right side to left side from the active cell.
Example: In H2 cell ={"A","B","C"}. This result will spill in H2, ...
- 491
0
votes
2
answers
171
views
Tracking elapsed time for a task in Excel — resume timing after status changes (formula or VBA)
I have an Excel sheet used to measure the time spent on individual tasks. The sheet has these columns:
Task
Start Date (timestamp when the task was first started)
Status (data validation with three ...
- 17
1
vote
1
answer
72
views
Aggregate sum in a table (instead of a worksheet)
I was wondering if there is a way to have aggregate sums in a column of a table, as it is easily done in a worksheet. In a worksheet, the following can be performed using =sum(A$1:$A$1), and so forth. ...
- 3,566
0
votes
4
answers
146
views
MakeArray to dynamically Offset rows by a factor on the source row
I have a table of invoice which we have monthly amounts we expect to bill. We also have a factor on how long many months we expect to actually collect per invoice.
What I am attempting to do is take ...
- 2,866
3
votes
2
answers
150
views
How to calculate Adjustment amount with conditions
I have a table as below:
Branch Code
Target
Actual Profit
Adjustment 1
Adjusment 2
Adjusted Profit
Progress Rate
Completion Rate
(A)
(B)
(C)
(D)
(E) = B + C + D
(F)
(G) = ((E - C)/F+C)/A
100
8,031.00
...
- 1,793
1
vote
4
answers
126
views
Why doesn't getting a value from a named range work, only for this specific value?
I have a named range called A2Bounds25:
Grade
Percentage
A*
80
A
71
B
60
C
49
D
38
E
27
I pull the percentage from A2Bounds25:
=VLOOKUP(F74, (INDIRECT($K$3)),2)
where F74 contains the grade I want to ...
- 89
1
vote
1
answer
151
views
Excel - Complex IF Question - Not sure if possible in excel
I have the below matrix where the Y axis consists of 'Vol' + 'Sub' and the X axis consists of 'Sub', with a rating corresponding to the level of each e.g. if Vol + Sub is 10.1% and Sub only is 4.3% ...
- 79
0
votes
2
answers
93
views
Check Active Cells from Multiple Column in Excel Online
I have an excel online sheet that I am building. I have list of projects in a sheet and another sheets contain working hours on each of these projects in each week. What I am looking for is a code ...
- 21
3
votes
1
answer
105
views
Dynamic Dropdown List
I have a live DB sheet in Excel, and I want to create a sheet where I can fill new rows using dropdown lists based in the DB sheet.
In other words, I have Client | Product | Requester | Piece/...
0
votes
3
answers
108
views
Counting multiple ranges based on a single criteria [closed]
I have a data set where I have my criteria in Column A which is an alpha numeric code, and my values in Columns C through H. There are duplicates in Column A and I need these to be captured and not ...
- 23
0
votes
0
answers
68
views
Circular Reference with countIF
why does this report a circular reference in the last 3 columns I use?
=COUNTIF(INDIRECT(CHAR(COLUMN()+64)&$B$51&":"&CHAR(COLUMN()+64)&$B$52),"2")
$B$51 and $B$52 ...
- 1
3
votes
4
answers
144
views
How can I filter zeros from a dynamically generated array in Excel?
I'm trying to generate a list of all the factors of a number in MS Excel. I have the following formula:
=NOT(MOD(12,SEQUENCE(1,12)))*(SEQUENCE(1,12))
This gives me an array with factors and zeros, as ...
1
vote
1
answer
120
views
Dynamic Excel formula to group items by category into 4 columns (with optional description rows)
I’m trying to build a dynamic formula in Excel 365 (French version). I’ve been struggling for hours and I’d really appreciate some help.
Data structure
I have a sheet named Pictogrammes with around ...
- 11
1
vote
1
answer
186
views
Record the date of certain events by using Excel formula
We want to record the date of certain events by using MS Excel.
For example, we have borrowed 5 books from a library.
When we renew each of them, we should log the date, by using dropdown as a data ...
- 29
1
vote
2
answers
145
views
Excel incorrectly processes formulas cross-referencing each other
I have generated an Excel file, with the following columns (cf. minimal code below):
ID (col. A): set via hardcoded values (i.e. not via Excel formula)
Manually-set resolved (col. B): a column that ...
- 1,314
0
votes
1
answer
61
views
Ignoring zeros or empty cells in Excel LINEST function
I am trying to fit two seperate curves to a data set. One is a quadratic curve and the other linear. One will use the first X points in the set and the other will use the remainder. I cannot guarantee ...
3
votes
1
answer
106
views
Create an array of filtered values (SUBTOTAL) and if there is no filter set include empty rows between the data into the array
0 | A |
--|----------|-----
1 |Product |
2 |Product_A |
3 |Product_A |
4 |Product_B |
5 | |
6 | |
7 |Product_C |
8 | |
9 |Product_D |
10|Product_D |
11|...
- 5,565
2
votes
4
answers
137
views
Excel function to extract certain values from a complex string
I need to pull the 7 digit number, 5 digit number and 9 digit (including a hyphen)
example string:
Headers
User ID: 12345, Claim No: 1234567, Invoice No: 1-12345678
Claim No: 1234567,User ID: 12345, ...
- 935
0
votes
3
answers
91
views
Excel: VLOOKUP/TEXTJOIN to get comma-separated result in cell for multiple matches in comma-separated cells in a column
I am trying to match a single value in a cell with content of a comma-separated list in a cell in multiple rows and get all matches returned in a comma-separated list.
The source data sheet format ...
- 95
1
vote
2
answers
92
views
excel search array for partial string match; return cell adjacent to string
I would like to search an array for a cell in the function's row and return the cell adjacent to the matching array row. The array cells contain partial matches with the function's row.
clients
...
- 11
0
votes
2
answers
95
views
Excel Table Formula not incrementing properly after several thousand lines
I have an excel table that has 25 columns the first 7 are data entry while the rest include formulas. The table is used for managing a variety of stock portfolios. In three columns I have formulas ...
3
votes
7
answers
242
views
Using spreadsheet to make a pairwise comparison matrix
I want to use a spreadsheet to take a column of rankings and turning into a pairwise matrix. Suppose I had a column like so:
Candidate
Ranking
A
1
B
5
C
3
D
2
E
4
I want to create a 5x5 matrix where ...
1
vote
1
answer
91
views
How can I add columns to an Excel sheet when the other data is referenced from another sheet?
I have worked on some excel assignments in the past where it was a requirement to save a raw data excel file and add a new worksheet which is identical to the raw data, but the second sheet must be ...
- 11
3
votes
2
answers
119
views
Only Consider Calls to Client if All Calls Made by Same Agent
I have an Excel sheet where each row represents an individual connected call made with a client. Most of the time, just one caller calls the client until we make a successful sale, but sometimes there ...
- 27
3
votes
3
answers
246
views
Stack Integer Sequences from a List
Intro
I wrote an answer to Expand records for each month between two dates. A part of my solution was to stack integer sequences from a column of integers. There were only 4 integers in the column, ...
- 56.8k
5
votes
3
answers
249
views
Expand records for each month between two dates
I have a few members lists for donations for a campaign. Individual members will to contribute for a duration.
Here is an example:
ID
Name
Donation Amount
Donation Start Date
Donation End Date
Status
...
- 38.1k
1
vote
1
answer
103
views
UNIQUE and FILTER formulas cannot be used as a named formula and/or data validation list? [duplicate]
I'm afraid I already know the answer, but I just want to make this completely clear. In my case, I have a Table16 with some columns. If I enter the following formula:
UNIQUE(FILTER(Table16[type]; ...
2
votes
1
answer
60
views
Assign value New to column if date is within 10 weeks of Min Date
I want to assign a column to a metric table for new consultants.
If the date of a recorded metric is within 10 weeks of their start date (MIN date) then assign a value of New to a column called New ...
- 7,188
2
votes
1
answer
71
views
How can I highlight cells which contain a substring from column A AND a substring from column B on the same row?
I have a web form where people can nominate others for good work. I have an excel workbook collecting responses. I want to check for entered names that do not match a pre-existing list of validated ...
3
votes
3
answers
125
views
Comparing values from a string to a list to find the highest valued item
I'm currently working with a CSV file which is returning values from a query in MS Defender reporting on device statuses. In this file, I am getting back one column, which refers to custom tags that ...
- 41
1
vote
2
answers
108
views
Rewrite COUNTIFS for dynamic array criteria
I want to get the count of names with diagnosis 2 and drug A or any combination of the two from the data set, but the criteria column is a mix of different criteria. I know that what I basically need ...
- 29
1
vote
1
answer
107
views
How to write a formula to display a message in column when checking if text in multiple cells AND dates in two columns match & overlap
To explain, I am creating an employee contract placement tracker in excel for desktop.
I have the following columns - Nurse Name, Overlap, Region, Site, ID, Start Date, End Date, Submitted Date, ...
- 11
2
votes
1
answer
80
views
Average First n Columns with Values Even if Not Enough Values
I am trying to average only the first n values of a row but am getting an error if I don't have enough cells with values.
How do I have the equation only look at the first 4 cells with values?
I've ...
- 107
2
votes
2
answers
136
views
How do I find a cell that has a specific value from an existing list?
Using Excel 2019, I have a sheet with two columns where the first will have an Order ID and the second will have a list of products purchased on that order. Out of all the products that could show up ...
- 141