Questions tagged [worksheet-function]

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

Filter by
Sorted by
Tagged with
0 votes
2 answers
42 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 ...
YGA's user avatar
  • 1,903
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 ...
YGA's user avatar
  • 1,903
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.
Tom Cone's user avatar
-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). ...
Cathryn's user avatar
  • 11
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:...
johnteach's user avatar
2 votes
2 answers
45 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 ...
Jules's user avatar
  • 21
-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 ...
JWS's user avatar
  • 1
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 ...
cxrbon's user avatar
  • 3
-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 ...
Jeffery Kaious's user avatar
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 ...
citrus's user avatar
  • 1
-1 votes
1 answer
30 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 ...
Uncle Bajubjubs's user avatar
-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 ...
Anakin Teow's user avatar
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/...
Samantha Lima's user avatar
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 ...
bobobobby's user avatar
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 &...
Toby Hogg's user avatar
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 ...
chunky-coal's user avatar
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 ...
Ross R's user avatar
  • 103
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
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 ...
General_Adnan's user avatar
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 ...
newt335's user avatar
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 ...
Michelle's user avatar
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 ...
Tim Valli's user avatar
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 ...
Beer_en_thu_si_asT's user avatar
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 ...
MarcG's user avatar
  • 1
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 ...
BMac3's user avatar
  • 1
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 = ...
Seba99's user avatar
  • 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 ...
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 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:...
Daniel Alder's user avatar
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 ...
KansaiRobot's user avatar
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.
Clairem's user avatar
-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. ...
Anakin Teow's user avatar
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 ...
Aisha Stayer's user avatar
-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 ...
Martin Harding's user avatar
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, ...
OverlordQ's user avatar
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 ...
bpep's user avatar
  • 1
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 ...
Mark Barnes's user avatar
1 vote
1 answer
64 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
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 ...
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
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 ...
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
-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 ...
Tracy's user avatar
  • 1
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
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://...
exar10's user avatar
  • 1
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 ...
Tuffsheet's user avatar
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 ...
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

1
2 3 4 5
163