All Questions
50
questions
11
votes
4
answers
8k
views
Spurious 0.0000000000001 added to formula result
My wife and I have a spreadsheet to work out our finances at the beginning of each month and this month has a strange figure in cell F9.
The formula in cell F9 is ="+ £"&-'Sue''s ...
- 1,490
5
votes
2
answers
1k
views
Excel - Live filter of list based on another column?
In Excel, I'm looking to get a breakdown of the values in one column (IDs) by the values in another (status).
Input
| ID | Status |
|----|----------|
| 1 | Complete |
| 2 | Active |
| 3 | New ...
- 6,525
4
votes
1
answer
18k
views
How to change a cells color depending on the value of the cell above it/before it for hundreds of lines
I have hundreds of lines with varying values (currency) and want the cell to change color depending on the value above it eg. Greater than = Green, Less than = Red = Equal to = Yellow. ie:
A1 is $100
...
- 43
3
votes
2
answers
4k
views
How to sum non numbers by number value
My question was nearly answered by How do I assign a number value to a non-numerical value in Excel
We use a RAG status (red, amber green), and I'd like to average a group of these values.
EG, if ...
- 1,943
3
votes
0
answers
49
views
Changes to User Defined Functions in Office 365 Subscription based
I've seen issues a couple of times when using User Defined Functions with Excel from subscription-based Office 365.
When a workbook is opened with these formulas, they are automatically updated. ...
2
votes
1
answer
66
views
Proper Syntax for AND OR NOT Excel Formulas
I was practicing some basic formulas in excel. The last 3 I worked on are as follows:
=IF((OR(C4="Hourly",C4="Contract")),"Yes","No")
=IF((AND(B4=&...
- 23
2
votes
1
answer
310
views
How would you count the number of matches between a comma-separated list and a reference column in Excel?
I have a set of numbers separated by commas. Each set represents a series of unique numbers that relate to a project. How can I write a formula to search a reference sheet for any number of the items ...
- 21
2
votes
0
answers
305
views
Excel - Lambda's helper MAP() function not valid
Ever since 2021 I've had acces to the newer lambda's helper functions among which was the MAP() function. All other helpers like BYROW(), BYCOL(), SCAN() and REDUCE() amongst others are working fine. ...
- 2,237
1
vote
2
answers
2k
views
Formulas `A1:A` and `A1:Z` give invalid name error in Excel?
Selecting a range of definite size (e.g. A1:A2) works just fine, but selecting a range to the last row does not. If relevant, I am using Office 365.
- 11
1
vote
2
answers
1k
views
Excel how to deactivate "Most Recently used function" suggestion from drop down list
I am currently using Office 365 on Mac.
For the "Drop-down Function-suggestion list", Excel have started suggesting the "most recently used" function, as opposed to JUST suggesting it in alphabetical ...
1
vote
1
answer
2k
views
Filter function with IF statement in 1st parameter to perform fill down multiplication OR division
Link to my previous related question for more background if needed: Transpose unique rows to columns while IF statement conditions are met but do not consolidate
My table headers start on row 20 ...
- 45
1
vote
1
answer
1k
views
Excel column auto-populates in newly inserted rows
Hopefully this makes sense.
In Excel, I have a table with 12 columns. Column A is a file number hyperlink that takes you directly to the specified job folder on our server. When I insert new rows ...
- 11
1
vote
1
answer
268
views
autofill a reference of a column to a row in steps
I have the formula =IF(Jan!$L6="w",Jan!$L6,"") in the cell A2.
in the cell right next (B2) to it I'd like to have the formula 3 rows further down so
=IF(Jan!$L9="w",Jan!$L9,"") and then in C2 ...
- 115
1
vote
0
answers
44
views
Last filled row Function behaves strangely based on column it's in
Function is acting very strange depending on column it's in.
Data start at row 7 (headers in row 6)
Column A has item name B Max Qty. C and D have simple functions and everything afterwards is the ...
- 11
1
vote
0
answers
70
views
How to have Excel return column and row headings based on / alongside an ordered list of values?
A measurement of ion transport numbers went wrong when an unorthodox concentration was accidentally used. An analysis of what could have happened yielded the following table. The values have been ...
- 111