Questions tagged [worksheet-function]
Questions about using in-cell functions/formulas in spreadsheet software.
8,102
questions
0
votes
0
answers
7
views
How to change a cell colour based on input of 3 other cells
I am looking to create a conditional format for a name for when 3 columns (E,F,G) all display the value of "Y". I have tried following a similar query from within StackExchange but i get a ...
0
votes
2
answers
45
views
Best way to define a 2D lookup in excel using lambdas?
I'm surprised that Excel still doesn't have a simple function 2D_LOOKUP(array, row, col). For instance, I have a complicated excel sheet I'm managing and I frequently want to pull the value for a ...
0
votes
1
answer
15
views
How to use excel lambdas and/or array formulas to generate the cross-product of two arrays?
As a late entrant to using lambdas with excel, I"m extremely excited to see how they can simplify my company's sheets.
One area we would love to see simplified: we have a list of values that look ...
0
votes
0
answers
10
views
Editing Calc formula, when clicking cell, it does not enter cell reference
When I am writing a formula, I can type =A1+A2 and it works, but if I type = and then click on A1, the system does not put A1 in the formula bar. It just enters A1 in edit mode.
-2
votes
1
answer
31
views
What's the best formula to use to total number of hours on excel spreadsheet [closed]
I need to total the number of hours attended per child per day. A morning is 3 hours, lunch 0.75 hours and afternoon 2.75 hours. If a child attends, the cell has '1' in it (see the screenshot). ...
1
vote
1
answer
47
views
Excel: SUMPRODUCT and cells containing ""
In cell N30 I have this formula:
=(SUMPRODUCT(A2:B2;$A$29:$B$29)/SUMPRODUCT(--(A2:B2<>"");$A$29:$B$29))
This is a simplified version, hopefully it still makes sense. If the cells in A2:...
2
votes
2
answers
46
views
Excel xlookup formula
I'm trying to come up with a formula that will yield the position of two criteria. Per below, looking for Product B from column A and then the position of the first non-zero value. In this case, the ...
-3
votes
0
answers
36
views
Excel: How to find length of sublist in list. Sublist are separated by blank cell [closed]
I have a list with sublists in Column A consisting of a Name followed by 1 to 10 Dates. The sublists are separated by a blank cell.
Example:
Jones, Bobby
1/5/24
1/9/24
3/12/24
(blank cell)
Smith, John
...
0
votes
1
answer
22
views
How to return a list of values from a filtered column?
I'd like to return a list of visible values (including zeroes) from a filtered column from one sheet into an another one so that the list will update if a new value is added into the original dataset ...
-2
votes
0
answers
33
views
Excel formula to show two months? [closed]
Trying to see if there's a formula that would pull data from a combination of cells and return showing two months?
someting similar to the following.
Cell showing the two months since its the end of ...
0
votes
0
answers
32
views
How do I run a specific formula in Excel but have it ignore the formula if blank?
I'm currently running this formula =ROUND((AVERAGE(B3:AX3)/2)*100, 0) but if B3:AX3 has no values at all, I get #DIV/0! appearing in the calculation cell.
How do I adjust my formula so that excel will ...
-1
votes
1
answer
31
views
Excel formula to track streak
I've been using a table to give myself a daily grade on some tasks, each row is laid out like
Do stretches:| A | A | B | C | A did yoga | F forgot | A | B | A | B | A
I would like to add a cell that ...
-2
votes
0
answers
10
views
How to sum across columns automatically? [migrated]
Would like your help to solve the problem as file attached.
https://docs.google.com/spreadsheets/d/1E3KTOYHuYIJy9uQKsvlXwbCpwL6cU1IHAQ-Yirb-AfQ/edit#gid=0
I would like each column to sum automatically ...
0
votes
1
answer
36
views
How to compare two columns in Excel with a conditional IF statement?
I have three columns that have some expense figures: Actual (column A), Budget (column B), Variance (column C):
For these expenses, if Actual is greater than Budget, I want the variance column result/...
0
votes
2
answers
74
views
Error using countifs function in Excel
I'm trying to use the countifs function to count occurrences of text within a multi-column range when another piece of text appears in a different range.
In the table below, A1:A12 is named colors and ...
0
votes
0
answers
59
views
Excel returning a non zero value for something that should be zero
I am using this formula:
=IF(Data!F26>=0.875, "aaa", "")
The value in Data!F26 is blank or ""
The cell displays aaa when it should be blank because 0 or blank or &...
0
votes
1
answer
74
views
Formula for Matching Header to Value
This is a question that I may have difficulty explaining, so bear with me.
On one sheet I have a column which has several cells, some of which are blank, some that have a single value, and some that ...
0
votes
1
answer
17
views
How can I move a set of columns that are duplicated down under the matching section?
I have an export of CSV data that repeats in the columns. What I need is a clean sheet that has all the columns back together. I made a mockup; the actual data is much more complicated, and there is ...
0
votes
0
answers
5
views
Counta and Array_constrain formula in Google Spreadsheets [migrated]
I am creating a sheet in google sheets to look like a dashboard from the below data. I am asked to create this with formulas. I am getting the filter part correct. But changing the values from filter ...
0
votes
1
answer
66
views
Excel formula to seek if the last value falls within a certain period
I have a spreadsheet of data full of blanks and would like a formula that is able to tell me how many and the total value of those values.
For example there are several rows that have values in 202402 ...
0
votes
1
answer
54
views
Calculating total sales with multiple criterias across two tables
I'm currently working with Table 1 and trying to calculate the total sales [Column C] of products with the same ID and matching stock units and sales units in Table 2. I've attempted to use the SUMIFS ...
0
votes
1
answer
23
views
How do I find the uppercase letter in the middle of a word, in the middle of a sentence in Excel?
I'm trying to find an Excel formula which will tell me where in a sentence in a cell, does an uppercase letter appear in the middle of a "word". I want to then use that position to pull the ...
1
vote
1
answer
134
views
Trying to find Date Differences in Excel between multiple enteries
I have a table of Invoices and Date modified. This gives multiple invoice entries with multiple dates by person. What I am trying to do is find the dates between the first and last entry to give me a ...
1
vote
2
answers
121
views
How do I return the max value from multiple values in a single cell?
I have some cells in my spreadsheet that have multiple dates separated by a semicolon ;.
After some googling, I found that TEXTSPLIT is probably the best formula to break this apart, but it produces ...
0
votes
0
answers
38
views
Identifying "bad" data combinations in Excel 365
I have a spreadsheet of employee data (time charged to projects and customers) that is tens of thousands of rows long. Each month, the file is updated to include the most recent month data. Each row ...
0
votes
1
answer
25
views
How can I apply a formula to specify which cells I want to use?
I have a template document where I will want to grab cell values from different spacings depending on another value. Call it myValue which lives in myValueCell.
Say myValue is 1. Then I want to take ...
0
votes
1
answer
62
views
Replace with wildcards in Excel or batch edit some wrapping formula
I'm only trying to delete some CONCATENATE() function from some formulas in Excel.
What I have; here 'Something' could be a lot of things (digits, <, >, texts, etc…
CONCATENATE("Something = ...
0
votes
0
answers
31
views
Macro for compressing/expanding formulas in Excel [duplicate]
I have looked for a while but haven't come across something that does what I am looking for. I'm not that proficient in writing macros and if this isn't possible I will find a different solution to my ...
0
votes
0
answers
15
views
Microsoft Word IF formula
I am having trouble with my IF formula in Microsoft Word, I am trying to automatically output a phrase depending on if cell in my table (J2) is equal to, less than, or greater than 0.
=IF([J2]=0.0,&...
1
vote
2
answers
44
views
Libreoffice transforms formula from range reference to table reference
I have a weird problem
Given the following libreoffice calc sheet:
a defined range "myrange" with the contents:
One
Two
Three
1
2
3
And a formula field:
=myrange[Two]
The result is 2
Now:...
0
votes
1
answer
52
views
Is there a way to list the names of excel sheets that contain a value?
I have an excel workbook with several sheets. The first sheet is called Master sheet and there I have a table with several names on column B (with a title first "Name").
Some of these names ...
0
votes
1
answer
34
views
Excel formulas for autofil
I am trying to autofill a cell based on a date selected in a column. Ideally I would like the date selected in the start column to be presented as a filled cell within the correlating column.
-1
votes
1
answer
59
views
Returning value within two column when it match 3 criteria
I would like to return a value 4.42295, if i choose MYR. If I choose SGD will return 1.32963 as picture show below. I had tried some formula in the picture but seem like cannot solve what i need. ...
0
votes
0
answers
60
views
Highlighting duplicates in Excel with VBA, multiple sheets
I have a workbook set up with a sheet for every month and then a summary sheet, so 13 sheets total. Each sheet, other than my summary page, have two sets of tables set up. A table for each week in ...
-2
votes
1
answer
80
views
Unraveling a nested IF statement to make it more user friendly
I'm trying to streamline a nested IF statement that my lab uses on a spreadsheet for keeping track of what we make every day for testing - it's a ridiculously complex statement that reads the value of ...
0
votes
1
answer
37
views
Sum specific cells from a column based on criteria from another sheet
I have three sheets. ScoreInputs, RawScores and ScoreMatrix.
ScoreInputs is basically a single column of raw input scores. Score Inputs
In ScoreMatrix, the first row is a list of score categories, ...
0
votes
0
answers
49
views
Excel Vlookup Where Column to Search is identified in Preceding column
I am trying to figure out if there is a way to write a formula such that the Table_Array of a vLookup is based on a value from another column. Basically, I want the Vlookup to search on another sheet ...
0
votes
0
answers
39
views
Searching a large list of strings for a set of strings that may or may not be present
I've looked up similar questions but the answers there don't quite fit my situation, which I think is more simplified.
I have a custom application that can export an .xlsx file listing feature codes ...
1
vote
1
answer
65
views
Adding formula to all existing formulas in an Excel sheet
I'm setting up a monthly call tracker for sales and customer service. I have almost everything built out, but I'm looking to add the IFERROR function to all cells in the sheet that have a formula in ...
0
votes
1
answer
43
views
Ordering columns of string alphabetically
I have 3 columns of names that are several thousand rows long.
I need to arrange them in alphabetical order.
I attached a simplified image of the data (the sample table is on the left side of the ...
1
vote
1
answer
37
views
Show a list where min. number > 5 in worksheet forumula
I'm trying to return a list with a given criteria
My current data looks like
A B
1 Anna 4
2 Brad 6
3 Claire 2
4 Dan 5
4 Evie 2
I want to select ...
0
votes
1
answer
55
views
Getting IFERROR to move to next row if I paste a value in the previous row
Not sure if the title sums up my problem enough. Basically, I am attempting to streamline my data entry by using formulas. How my data is set up is I have three tabs (Sheet 1, Sheet 2, and Sheet 3). ...
0
votes
0
answers
26
views
Importing a range of formulas from csv or txt file starts with ' in excel and sheets [duplicate]
I am generating some image URLs with a python script and converting them to a formula. I have tried saving it to both a CSV and a TXT file as others suggested, but both lead to the same problem:
After ...
0
votes
2
answers
40
views
VLOOKUP displaying wrong information in Excel
new to excel, and trying to do a simple task, where depending on the months in a month range, some percentage will be displayed. However, it's always displaying 0.
Here's a picture to help you ...
0
votes
0
answers
19
views
INDEX MATCH repeats last valid result
I've searched for this issue and I've found some that were close, but not quite, so forgive me if it has been asked and answered.
When using INDEX/MATCH, all my results are correct, except that the ...
-1
votes
1
answer
20
views
how to use SUMIF to calculate a cell from the figures in 2 other cells based on the date
I have one cell that calculates a figure based on 2 cells, so for example target quantity is in C6 then the completed quantity is in D6 so cell E6 is "=D6-C6" but i only want it to ...
0
votes
2
answers
47
views
Why are mat1, mat2, mat3, etc. defined and equal to 0?
I'm writing a formula in Excel (Microsoft 365) using the LAMBDA function and happened to name a variable mat1. This resulted in an error and I noticed that the name mat1 was a different colour. I ...
0
votes
0
answers
36
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://...
0
votes
1
answer
39
views
Excel: Referencing a cell from another workbook using a dynamic filepath/filename, inside a formula
I have 50 students, each with a gradebook with many scores I am compiling in a master tracker workbook. The data points come from about 60 different worksheets within each student workbook. Rather ...
0
votes
2
answers
59
views
Use a Macro or Formula to replace a single letter with a string of text
This should be pretty simple. I want to use a formula or macro that will automatically replace a letter with a string of text within the same cell. Ex.: If I type the letter "r" in Cell A1 I ...