Questions tagged [worksheet-function]

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

Filter by
Sorted by
Tagged with
1 vote
2 answers
14 views

How do I convert an Excel calculation into a spilled dynamic array

I'm not even sure how to ask what I'm looking for, without describing the full question. if I have this table (CSV form at the bottom if anyone wants it): A B C ...
user avatar
3 votes
2 answers
19 views

Excel - Need formula help for project at work

I am working on a project at work where I am pulling a report each week from one of our systems and pasting it into a "DATA" tab in a workbook. There are then 6 other tabs that are specified ...
user avatar
1 vote
1 answer
15 views

Excel/GoogleSheets Forumla to List All Rows Without A Corresponding Value in Another Column

I'm hoping there is a simple way to perform the following. I have 2 columns (A,B), and I would like a third column (C) which consists of all values from column A that have an empty cell in column B. I ...
user avatar
0 votes
2 answers
31 views

How to get a running sum - floating above the cell

I have a column of natural numbers (1, 2, 3...) and I want to know the sum up until a certain point without opening a new column that would calculate the sum. Basically, I want to have a floating ...
user avatar
0 votes
2 answers
24 views

array difference and/or intersections in Excel

How to get the difference and intersection between arrays? (Possibly w/ dynamic arrays functionality) I want {"a","b","c","d","e"}-{"a",&...
user avatar
  • 11
0 votes
1 answer
18 views

Split cell then Vloop then sum

I have a google form linked to a google sheet. There is one multiple choice checkbox question in the form that asks the user to check A, B, C. In the google sheet, the answer are concatenated with a ...
user avatar
  • 183
0 votes
1 answer
31 views

My workbook is 2 sheets. the 2nd looks in the 1st for a match on a name and returns the value in a cell from the row where the matching name is found

I asked this question before and got an answer that worked well, however, it no longer does. I've spent the last half hour trying to find it on site but can't. So anyway, I'm like to attach a sample ...
user avatar
0 votes
2 answers
26 views

Create a formula including nth values in excel

Say i want to use this formula "(x1*m1+x2*m2...+xi*mi)/m_tot" in excel. Where "xi" is found in every 6th row and "mi" is found in every 6th row in another comlumn. "...
user avatar
-1 votes
2 answers
33 views

Remove Excel Duplicate Columns 123 , 123-1, 123-3

I am facing this problem where I want to remove duplicates in columns in Excel. For example in 1 column I have these types of values: |Values| |123| |123-1| |123-2| |123-3| I want to have only the 123-...
user avatar
  • 1
0 votes
2 answers
27 views

Dynamic Table Name in Excel Lambda Function with Structured References without INDIRECT

Is it possible to define an excel lambda function that takes a parameter that acts as a "dynamic" name for a table, that is then used in a structured reference, without using the INDIRECT ...
user avatar
0 votes
0 answers
4 views

Google Sheets - efficiently aggregating one column for grouping, based on minimum value in another column? [migrated]

I have a sheet with upward of 1000 rows with the following schema: GroupCol | NameCol | NumberCol In the dataset, any given value in GroupCol is repeated 1-10 times. I need another sheet that has one ...
user avatar
0 votes
2 answers
55 views

Excel Sort Command not working AT ALL as expected

I have a table in excel (formatted as a Table), that does allow me to sort, but its results are incorrect in at least two ways: the numeric value column I am sorting on A-Z or Highest-to-lowest comes ...
user avatar
  • 3
0 votes
2 answers
29 views

Sorting Function in sheets or excel

How do I do a repeated function in excel? I want the pattern to go: (Vertical) =a1 =b1 =c1 =a2 =b2 =c2 =a3 =b3 =c3 and so on. this will go on for until: =a400 =b400 =c400 Example in sheets
user avatar
  • 1
0 votes
4 answers
40 views

How to calculate an average of numbers when some cells in the range are not a number?

I'm trying to calculate an average number of days to resolve from some data I have. However, in some cases, because the problem hasn't been resolved yet, Excel thinks the number of days is #NUM! I'm ...
user avatar
0 votes
2 answers
21 views

Searching for a unique value associated with a category that is duplicated

This is my first time diving into excel and I'm currently trying to create a formula that finds the highest score in a certain category. I've left out some data for privacy reasons, and because it's ...
user avatar
  • 1
0 votes
2 answers
32 views

How to check two rows for two separate values and then populate each applicable value?

I am building a simple workflow. The functionality I am attempting to build is a column that will be used to show if something needs to be completed. Identification: Column B is the column that needs ...
user avatar
0 votes
1 answer
23 views

Excel function goal seek

I have 2 questions about the Goal Seek function: Can I set the "To value:" to equal the value of a specific cell such as U3? How can I do this function for 5 cells at the same time?
user avatar
0 votes
2 answers
37 views

Return list of all unique values based on criteria

I am looking to return a list of all outstanding packages. For each unique Package Name, I want to return the package name and code that is next to be completed (Either In Progress or Not Started) For ...
user avatar
  • 1
0 votes
0 answers
20 views

How to join/match two separate pieces of data so that it always shows on the same row?

I have a sheet that I intend to use as a workflow. You will see in the illustration below two outlined boxes. Green box - this is manually entered information. Red box - is data that is being pulled ...
user avatar
0 votes
1 answer
39 views

How to affiliate two separate pieces of data so that it always shows on the same row?

I have a sheet that I intend to use as a workflow. You will see in the illustration below two outlined boxes. Red box - is data that is being pulled from another sheet Green box - this is manually ...
user avatar
0 votes
0 answers
26 views

Excel for Web Data Validation List Not Working with FILTER() function

I am using a combination of a data validation list - which contains zip codes - and the =FILTER() function to try and create a drop down list of zip codes for the user to search from. When I remove ...
user avatar
1 vote
1 answer
19 views

Excel Special Characters Legend

Would anyone happen to have a list of all formula characters that can be used in a formula? E.g. "" = Blank/null; * = multiplication; etc.
user avatar
0 votes
1 answer
21 views

Need to Sum if both cells on the same row are greater than Zero

I am attempting to create a report that will count the number of total sum of two columns (B4 and C4) only when the both cells on that row are greater than zero. I would also like to add text "...
user avatar
0 votes
1 answer
41 views

Return rows ONLY if another column is equal to "Yes" Using dynamic table

So I m trying to return rows of data in a dynamic table Here's a picture below of the data, table name is "Assignments" and I've tried this formula if(Assignments[Shipping]="Yes", ...
user avatar
0 votes
1 answer
15 views

count unique with multiple criteria, what should i do?

Problem I want to count uniquely in multiple criteria but it seems the formula isn't working The count should be 6 instead of 3.5 what should I do?
user avatar
0 votes
1 answer
29 views

How to combine different IF and OR in the same excel cell?

I'm a GP trying to build an excel sheet to adjust the insulin dose according to the glycemia levels. For that I need to make a formula like this IF(F8<=B15;D9+C15),OR,IF(F8<=B16;D9+C16),OR,IF(F8&...
user avatar
0 votes
1 answer
17 views

Sum a range of cells in Excel according to a range (criteria)

cordial greetings I am trying to obtain the sum of the number of males taking into account an age range that I set previously. For example, I want to know how many males, between the ages of 3 and 12,...
user avatar
0 votes
1 answer
35 views

How to round in Excel based on value in 3rd decimal place?

I'm following some instructions that say "Round the result to two decimal places based on value in 3rd decimal place." I've used the Round() function before, but how do I control rounding ...
user avatar
0 votes
1 answer
21 views

Conditional formatting based on selection from a dropdown list

I have a spreadsheet that is used as a sort of a schedule. Each person has their own ID number and the ID number is shown in a distinct cell. So if my ID is 41, I might see myself scheduled 9-12 on ...
user avatar
  • 3
0 votes
2 answers
44 views

AVERAGEIF function pulling in non-parameter based data

I am using an AVERAGEIF function to average results based on the selected "Topic Name" defined on the sheet below. Formula: =IF($B5="Primary",AVERAGEIF(Primary!$L$4:$T$107,Testing!...
user avatar
0 votes
2 answers
41 views

Find unique IDs which entries do not have a certain value in Excel

I have the following dataset (the actual database has thousands of rows) I want to find which unique studies do not have any "accept" value, count them, and exclude them. For the sample ...
user avatar
0 votes
1 answer
19 views

Countif with literal array

Why does this formula not work: COUNTIF({1,2,3},2)? I can use both COUNT() and COUNTIF() with a range, but I can replace the range with a literal array only on COUNT, not on COUNTIF. If I use LET to ...
user avatar
  • 113
0 votes
0 answers
26 views

Finding average according to column

Screenshot of example table I have a table with the first column of names and the first row of events and scores of each event filled in. There are ~50 columns and rows, but I simplified the picture ...
user avatar
  • 1
0 votes
1 answer
11 views

Excel Reference another sheet like a dictionary

On a sheet in my excel file, I have rows as shown in the following : In a second sheet in the same excel file, I have a reference sort of like a dictionary that links each organization to an ...
user avatar
0 votes
1 answer
35 views

Microsoft Excel - How can I convert the following text to 'date' only format?

I have text in a cell with the following syntax: Jan 6, 2022 10:18 AM The standard format adjustment from 'general' to 'date' does not work. I need to convert the text into a date-only format (ideally ...
user avatar
  • 1
0 votes
0 answers
16 views

Excel Grid plot

Hey I have two results from 0 to 9 that I wanted to plot onto a specific cell reference to create a representation. So for example one axis would show in row (1 to 9), e.g. this score was 8. And the ...
user avatar
1 vote
3 answers
251 views

In Excel, how do I access cells using fully-qualified numeric coordinates (worksheet, column, row)?

I have the indices of a cell in an Excel workbook: Worksheet, column, row (and yes, I know that Excel likes 1-based indices.) Given those, what formua should I write to obtain the value of that cell?
user avatar
  • 7,240
1 vote
1 answer
91 views

Excel logic for complex draw from different buckets without repeating bucket

I am trying to create an Excel file which can help me distribute 1944 coins into 648 bundles of 3 coins each. The coins are coming from 10 buckets. Bucket 1 contains Gold coins. Buckets 2, 3 and 4 ...
user avatar
  • 113
0 votes
2 answers
34 views

In Microsoft Excel, how to copy an entire entry to another sheet automatically?

I am currently extracting data for a research, and I want to automatically copy the data to multiple sheets when a condition is fulfilled, as the data keeps being updated over time. I am aware that ...
user avatar
0 votes
1 answer
18 views

Create latest update date excel

I want to insert a last updated on () if any value in that row was changed. I have a column for this in my spreadsheet. Applies only with actual values changed
user avatar
0 votes
2 answers
16 views

How to add Epi Week aka MMWR Week column in Power BI

My dataset has a date column. I need to assign the correct Epi Week/MMWR Week to each row. I can't do it by hand because the dataset spans five years and has over 18,000 rows. The Power Query function ...
user avatar
0 votes
2 answers
24 views

I want Excel to return a value based on a table of information

Trying to automate a set of scoring for an older golf buddy. There are strokes added based on where their score lands in a chart. I'm trying to write a formula that returns one of 5 values after ...
user avatar
0 votes
1 answer
19 views

Excel convert table from horizontal to vertical

Need to have regular horizontal table to rearrange into vertical as per example. Used formula inside E1 =A1 and inside E3 =A2 however when i auto fill down E5 gets populated with formula =A5 when ...
user avatar
0 votes
0 answers
29 views

Excel formula with dynamic variable based on input from whatever is in column of specific row

So, I have no idea if this is possible and my head is breaking. Here is what I am trying to do. I am trying to track my plants. Let's say I have broccoli, peppers, plant x, plant y, and plant z. They ...
user avatar
0 votes
0 answers
16 views

What's (if any) the formula to get multiple matching values in LibreOffice Calc? [duplicate]

Suppose I have the following data: A B 1 idx1 VAL1 2 idx2 VAL2 3 idx3 VAL3 4 idx4 VAL1 5 idx5 VAL4 6 idx6 VAL1 7 idx7 VAL1 8 idx8 VAL2 9 idx9 VAL5 10 idx10 VAL3 Is there a LibreOffice Calc ...
user avatar
  • 167
0 votes
1 answer
37 views

Find Closest Match (Conditional Lookup)

I am trying to do conditional lookup in Excel (MacOS). Let's say I have the table shown below, and I want to search for (type=alpha, length=165). In the table, there isn't an exact match so I'm trying ...
user avatar
  • 103
2 votes
1 answer
23 views

How to return in a cell all the matching values present in a column?

Suppose I have the following data: A B 1 idx1 VAL1 2 idx2 VAL2 3 idx3 VAL3 4 idx4 VAL1 5 idx5 VAL4 6 idx6 VAL1 7 idx7 VAL1 8 idx8 VAL2 9 idx9 VAL5 10 idx10 VAL3 For each "VAL[n]" ...
user avatar
  • 167
0 votes
1 answer
26 views

Removing date from datetime (VBA Excel)

I need to delete the date from mulitple rows with a datetime value. Or extract the time to another cell. For example: A1 = 3/11/2022 10:20:00 A1 after macro = 10:20:00 or A1 = 3/11/2022 10:20:00 A2 = ...
user avatar
  • 1
0 votes
2 answers
39 views

Microsoft Excel - Moving data within multiple staggered rows, into single row

I have a spreadsheet with massive amounts of data (circa 8000 lines). the issue is that of those 8000, there is a group of 9 lines that need the data from various columns across those rows merged ...
user avatar
  • 1
0 votes
1 answer
36 views

custom per-category formulae over summarised rows

I'm trying to figure out some pivot table magic here, where I can have the top-level row-field be a custom formula for each category. So, for example, with data like this: Edit: Providing in CSV ...
user avatar

1
2 3 4 5
147