Questions tagged [worksheet-function]

Questions about using in-cell functions/formulas in spreadsheet software.

Filter by
Sorted by
Tagged with
0 votes
2 answers
22 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 ...
bobobobby's user avatar
0 votes
0 answers
30 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 &...
Toby Hogg's user avatar
0 votes
1 answer
59 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 ...
chunky-coal's user avatar
0 votes
1 answer
15 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 ...
Ross R's user avatar
  • 101
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 ...
Ramya's user avatar
  • 35
0 votes
1 answer
65 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 ...
General_Adnan's user avatar
0 votes
1 answer
53 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 ...
newt335's user avatar
0 votes
1 answer
17 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 ...
Michelle's user avatar
1 vote
1 answer
85 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 ...
Tim Valli's user avatar
1 vote
2 answers
102 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 ...
Beer_en_thu_si_asT's user avatar
0 votes
0 answers
34 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 ...
MarcG's user avatar
  • 1
0 votes
1 answer
23 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 ...
BMac3's user avatar
  • 1
0 votes
1 answer
58 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 = ...
Seba99's user avatar
  • 101
-2 votes
0 answers
33 views

Can we override F1 key or disable/enable in web excel of microsoft 365 in all browsers, if yes then how? [closed]

I am using web excel where I want to disable and enable F1 function key or override the function of F1 key. And I have been trying to find the solution to the following with no success.
Ujjwal Kant's user avatar
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 ...
Skrobbit Johnson's user avatar
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,&...
Miller3223's user avatar
-1 votes
0 answers
17 views

Is there a way to create multiple rows in a data table based on a single cell entry?

I'm creating a tracking spreadsheet for test samples. Is there a way for a user to enter a single product code that will generate 5-6 rows of for data for the tests required for that product code? ...
Wendy's user avatar
  • 1
0 votes
2 answers
36 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:...
Daniel Alder's user avatar
0 votes
1 answer
47 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 ...
KansaiRobot's user avatar
0 votes
1 answer
33 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.
Clairem's user avatar
0 votes
1 answer
54 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. ...
Anakin Teow's user avatar
0 votes
0 answers
53 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 ...
Aisha Stayer's user avatar
-2 votes
1 answer
78 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 ...
Martin Harding's user avatar
0 votes
1 answer
36 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, ...
OverlordQ's user avatar
0 votes
0 answers
46 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 ...
bpep's user avatar
  • 1
0 votes
0 answers
38 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 ...
Mark Barnes's user avatar
1 vote
1 answer
53 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 ...
jpheimer's user avatar
0 votes
1 answer
42 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 ...
Peter Thome's user avatar
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 ...
MyDaftQuestions's user avatar
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). ...
Jasmine's user avatar
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 ...
dennisklad's user avatar
0 votes
2 answers
39 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 ...
Juan Felipe Rubiano's user avatar
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 ...
Hiker Bro's user avatar
0 votes
1 answer
19 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 ...
Tracy's user avatar
  • 3
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 ...
David's user avatar
  • 103
0 votes
0 answers
30 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://...
exar10's user avatar
  • 1
0 votes
1 answer
36 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 ...
Tuffsheet's user avatar
0 votes
0 answers
9 views

Error in Calculated field of google sheets [migrated]

I have my data in the following format: I added 2 new columns on the right: (1) Column name: YesInCSAT, formula used: iferror(CSAT% * Total Surveys Given,"") (2) Column name: AutomatedCount,...
Mohit Singh's user avatar
0 votes
2 answers
52 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 ...
Christopher Stanesa's user avatar
0 votes
1 answer
41 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 ...
Danny Zambrano's user avatar
0 votes
0 answers
23 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. ...
WCoops's user avatar
  • 1
0 votes
1 answer
31 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 ...
Kerry Gnik's user avatar
0 votes
1 answer
13 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 ...
Georgios's user avatar
2 votes
2 answers
266 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$...
Danny Zambrano's user avatar
0 votes
1 answer
53 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]...
Alireza M.Majdabadi's user avatar
1 vote
2 answers
55 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 ...
Drail's user avatar
  • 13
1 vote
1 answer
34 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 ...
Aisha Stayer's user avatar
1 vote
3 answers
126 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 ...
Megidd's user avatar
  • 271
0 votes
1 answer
56 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. ...
pkujawa's user avatar
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 ...
Mesah's user avatar
  • 1

1
2 3 4 5
162