Questions tagged [worksheet-function]
Questions about using in-cell functions/formulas in spreadsheet software.
8,137
questions
0
votes
0
answers
33
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
-1
votes
1
answer
39
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 ...
0
votes
1
answer
46
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
78
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
60
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
84
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
18
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 ...
- 103
0
votes
1
answer
71
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
56
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 ...
- 7
0
votes
1
answer
34
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
1
vote
1
answer
164
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 ...
- 13
1
vote
2
answers
192
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
41
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 ...
- 1
0
votes
1
answer
26
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 ...
- 1
0
votes
1
answer
65
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 = ...
- 101
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
20
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
52
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:...
- 232
0
votes
1
answer
60
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 ...
- 856
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
-1
votes
1
answer
60
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
82
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 ...
- 13
-2
votes
1
answer
83
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
40
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
50
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 ...
- 1
0
votes
0
answers
41
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
115
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 ...
- 13
0
votes
1
answer
44
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 ...
- 1,933
0
votes
1
answer
66
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). ...
- 1
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
42
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 ...
- 1
0
votes
2
answers
48
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 ...
- 103
0
votes
0
answers
49
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
43
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
83
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 ...
0
votes
1
answer
42
views
How to use conditional formatting on a specific formula added?
I want to add a conditional formatting to a cell. In that cell I have a formula that provides 2 dates. The dates in the cell look like this 01/12/2024 and 01/24/2024.
Originally when I had 1 date in ...
0
votes
0
answers
26
views
I need to compare two excel sheets and then merge the demographics into one of the sheets
I have two sheets:
Sheet AA has 250 Names in column A and a dollar Amount in column C.
Sheet BB has 2000 names in column A and sheet BB has most of the addresses in columns h,I,j,k. Some are missing.
...
- 1
0
votes
1
answer
33
views
If formula not working for hospitality catering manager
I am creating a spreadsheet for my Catering Manager. For example, she can select one of three options for breakfast. All she does is enter the quantity beside the option that the customer has ...
1
vote
1
answer
19
views
Libreoffice calc: countifs over two rows
I have a table with dates in column A and strings in column B. I'd like the countifs function to count the cells where the weekday in column A is thursday and the corresponding cell in column B is ...
- 13
2
votes
2
answers
301
views
How to nest 2 different formulas in one cell
I'm trying to combine these 2 formulas in one cell. I'm trying to get 2 different dates in one cell. To show in the end (e.g. 2/15/2024 / 2/24/2024.
My 2 formulas
=($C$1-DAY($C$1)+1)+14
=($C$1-DAY($C$...
0
votes
1
answer
87
views
How could we check if a column of a table is filtered or not, by formulas
I need to check whether a column of a table is filtered or not. The data type of the column is Text. I want to use worksheet Formulas, not VBA macros.
Something like this:
=If(IsFiltered(Table1[Field1]...
1
vote
2
answers
59
views
SUM select ranges of varying lengths in a single column using null/non-null values in second column to decide SUM indexes in an Excel formula
Example of format for the issue, I need to generate the third row that is a sum of each color block:
I have an odd set of columns in a very large dataset, and I am not great at Excel. I could be way ...
- 13
1
vote
1
answer
36
views
Formulas corresponding to the correct table in my sheet, vba. Multiple tables across multiple sheets
I am setting up a cycle count sheet using VBA. I am having trouble with the formulas in my tables only referring to the first table in the sheet for the input values.
For example, I have 4 tables per ...
- 13
1
vote
3
answers
131
views
Convert an Excel table from one structure to the other
Original table
I have this table in Excel or LibreOffice.
Unit number
Type
Name
1
Object
Top
1
Object
Bottom
1
Object
Left
1
Object
Right
1
Object
Back
1
Object
Front
1
Property
Right-Fixed
1
...
- 271
0
votes
1
answer
83
views
Excel: Using COUNTIF + UNIQUE gives generic error "There's a prblem with this formula"
I'm trying to create an excel function that counts the number of unique entries in a range of (potentially merged) cells. The UNIQUE function returns two values for merged cell, one of which is zero. ...
- 1
0
votes
1
answer
29
views
Looking up a value and putting a different value in a cell in the same row
All the cells I reference can be found in the image below. I'm trying to figure out how to search for a name that I enter in F3 and then put the date I have in E3 into the corresponding cell in column ...
- 1
0
votes
0
answers
42
views
How could I add a value from text that could be across multiple sheets?
I have a calendar that I put people down for time off. This calendar has a sheet for each month where I put something like John-V. What I want to do is get a count for each John-V across every sheet ...
- 1