# Questions tagged [worksheet-function]

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

8,002
questions

0
votes

1
answer

46
views

### Using Filter Function and Dynamically Changing Column Based on Value in Another Field

I have an Excel table called "Participants". I want to filter out the delegation name (in this formula Participant[Delegation] and the name of an event. There a series of different events ...

0
votes

1
answer

33
views

### Filtered list of items in column A if item x is in any column from B to X

I’m working on an Inventory system for a restaurant chain with a large number of recipes and their corresponding ingredients. I have an excel sheet with all the recipe names in column A, and columns ...

0
votes

2
answers

39
views

### How to properly count multiple criteria from ranges in a separate sheet?

I have a master data table in a worksheet (worksheet name 'Data'; table range called 'MasterData'). This includes data about participants in a conference (name, nationality, etc.) and whether each ...

0
votes

0
answers

9
views

### How do i utilize IF functions (or others) in order to create a simple username/password in google sheets? [migrated]

On cell A3, there is a username input (i.e max or dave), on cell B3, there is a pin input (i.e 1234 if its max, or 8640 if its dave), how would you go about creating a formula in order to satisfy ...

1
vote

1
answer

41
views

### Convert column of data to comma separated cells of data based on record file presence

I have to migrate a database from one format to another.
The servers accept the data in different formats.
The current issue is image URL files.
DATA BASE 1) = WHERE IMAGES WERE DOWNLOADED FROM
DATA ...

0
votes

1
answer

91
views

### Average SumIf, differentiate between Zero and Blank

I need some help from the almighty community here.
I have a sample set of data here. I am trying to sumif some data and then average it. the goal being to get a running average of my budget but only ...

0
votes

1
answer

44
views

### Looking for suggestions for more efficient Excel FILTER solution

I'm creating a dashboard that will return data based on a user selection of two criteria, in this case the agencies that made a referral will be returned based on the quarter and program they referred ...

0
votes

0
answers

23
views

### Excel not propagating values changed while file closed, not updating calculations, formulae

An xlsx file is essentially a zip of XML files and such. I have an xlsx file that is modified by another, not-Excel program. It updates values on sheet A. Sheet B has formulae based on values on ...

0
votes

1
answer

28
views

### Windows 11, Excel

Is there perhaps a formaula that can combine the informations in multiple cells into one in another cell - I know I am not asking this correctly.
Please see below picture:
I need te information either ...

0
votes

2
answers

58
views

### how to count a variable amount of numbers within a set that sum to a given fraction of the total sum

I have a dataset in the form of 8 columns (separated based on additional variables that aren't directly relevant here) that vary in size each week and each data point is an effectively random positive ...

0
votes

1
answer

121
views

### Excel formula to calculate based on pieces required, pieces per box and shipments per day

I need a formula that will calclate the pieces required for each shipment as close to equally as possbile based on the amount of pieces in a box. I need it to calculate the shipments to match the ...

0
votes

2
answers

37
views

### Excel formula, count cells up to the last higher than zero

I´m trying to find out a formula in excel which would count cells up to the last cell which contains value higher than zero.
Example row: 0,1,7,0,0,1,3,4,0,0. Here the output should be 8, because 8th ...

1
vote

1
answer

68
views

### Given a value, I want to find this value in one range and then report out a value in the same relative position in a different range

I have two ranges on the same sheet, we'll call them Range A and Range B. These ranges are the same size and their location on a sheet is known.
I ultimately need to find a value in Range B. To start ...

0
votes

1
answer

36
views

### Excel - status (online/offline) corresponding to concrete number

I cannot deal with the below tasks, and need your help!
The thing is that I need to use a formula to solve this (not just copy-paste, assuming that the data will be much bigger). Basically, I need to ...

0
votes

2
answers

79
views

### Formula checks A1 and return value, if blank, return value in A2, if A2 is blank, return value in A3, if A3 is blank then display a message

I guess I just needed to have a closer look at each of my formulas' opening and closing statements and I figured it out.
Below is my original post for posterity:
Not sure if anyone can help me with ...

0
votes

1
answer

38
views

### Using the value of one cell to GOTO the cell of that value

I'm trying to use a cell that holds the address of another cell as text. I want the macro to use the text in the 1st cell (the value of the cell) to GOTO that cell named.
I thought I could trick the ...

1
vote

2
answers

34
views

### How to find second smallest from a set of formulas in Excel?

How to find second smallest of the following in Excel 2007 Sheet:
(M2+G2)/N2
(N2+H2)/O2
(O2+I2)/P2
(P2+J2)/Q2
(Q2+K2)/R2
I tried the following, but it does not work.
=SMALL((M2+G2)/N2,(N2+H2)/O2,(O2+...

1
vote

1
answer

40
views

### Find values not present in multiple spill lists

I have a list of usernames (which will contain duplicates) - beginning A2 in the image - and then three lists of usernames generated by spill functions of particular types of problems (again these ...

0
votes

1
answer

31
views

### Trying to sum cells depending on text at the top of a column and beginning of a row

I'm trying to create a budget template in Excel that can be easily expanded without needing to update formulas to include new additions. On the summary sheet of the workbook, I have two columns for ...

2
votes

1
answer

57
views

### Can I setup a 1:1 replacement for formula calculations in Excel?

Suppose I have one Excel sheet or region like this:
Good 5
OK 4
Acceptable 3
Poor 2
Bad 1
Nothing 0
And now I have another region like this:
Task 1 Good
Task ...

0
votes

2
answers

48
views

### Vlookup formula or something else?

I am trying to compare two separate columns from two different sheets. If the data on the Columns A match, I am trying to pull data from Column B within the first sheet and have it outputted on sheet ...

0
votes

4
answers

106
views

### Produce a complete list of ports from a switch's VLAN configuration output in Excel

I have a set of three formulae that convert a list of ports from a switch's VLAN assignment output into an array that spills into the cells below, and I'd like to combine them into a single formula (...

0
votes

0
answers

20
views

### Sum different rows

I used a pivot table on my data and I like to know if there is a formula or a way to see only one total for Aaron Donahue. Same thing with Abhi Gupta's line items.
enter image description here

0
votes

2
answers

57
views

### Subtracting Times Regardless of Date

I'm in charge of writing a custom report in Excel. I'm trying to formularize the calculated cells as most of the report is text. Consider the Following:
Date StartTime Length EndTime
1/1/24 ...

1
vote

1
answer

40
views

### In Excel, how to have hexadecimal values with spaces in it be converted to decimal?

I have the following in a cell:
00 0A
I'd like to convert it to the decimal value in excel without removing the spaces and formatting of that cell for readability purposes. How would I do that?
I ...

0
votes

1
answer

96
views

### SPILL! Error when pasting XLOOKUP formula to other cells

I'm new to using XLOOKUP, so this might be a dumb question, but I have created the following formula to pull data meeting 2 criteria from a separate Excel file. It works as expected where the formula ...

0
votes

0
answers

20
views

### Auto Numbering in a single cell

I want a Cell to Auto-populate a Tracking # in a form that several people have access to on a shared drive. Example:
I open the Exception Form for the first time and it should show SL(date)-#:
result ...

1
vote

1
answer

67
views

### How to apply the MATCH() function only to a filtered range? Or is there another way to do that?

I have a filtered list of values. Let's say I have the numbers from 0 to 5, and 2 is filtered out:
A (with auto-filter against "2")
0
1
3
4
5
Now I want to find out whether one of the ...

1
vote

3
answers

147
views

### Return the only value from a range that contains one populated cell

I'm new to Excel. I have a Laser measurer recording results in a spreadsheet:
Each row in D:G contains a single populated column and the remaining columns are empty.
The column that is populated ...

-1
votes

1
answer

57
views

### How can I use Excel to replace values in a comma separated list with values in another cell?

I have a lookup table that looks like this:
ID | SKU
1 | 5123
2 | 1238
3 | 5812
And another table that has a comma separated list of SKU:
SKULIST
5123, 1238
5812, 5123
How can I add a column that ...

0
votes

2
answers

62
views

### How to group a column based on another column. Then write it into another column

How to group the animal name column based on the category column, and put it on the corresponding column (yellow area). For example in the figure, find the animal name (column A) with herbivore (...

1
vote

1
answer

48
views

### Hyperlink does not change along with cell when filtered

I’m having a problem with hyperlink when using filters. So let’s say I have these table rows:
A
B
C
I have a hyperlink to cell C using the usual hyperlink option. But if I change the filter from “A to ...

0
votes

3
answers

118
views

### How to count for between two dates in 2 colums with different row in Excel

I have a spreadsheet with 2 columns with dates and one with a Room #:
Date In
Date Out
Room No
2/3/2023
2/5/2023
Room 1
2/5/2023
2/8/2023
Room 1
2/8/2023
2/10/2023
Room 1
3/23/2023
3/26/2023
Room ...

0
votes

1
answer

36
views

### Results offset by one row when using Index Match with Excel Table

There is data formatted as an Excel Table, Table 3, in Sheet1:
In Sheet2, there is a subset list of the first field, DuckID. It is required to pull values from the Table 3 by DuckID for a column name ...

0
votes

1
answer

109
views

### Why is Excel rejecting CHOOSECOLS(Array,Colnum) in a formula where it accepts INDEX(Array,0,ColNum)?

SUMMARY
Can anyone explain why Excel -- current versions on both Mac and Windows -- sometimes refuses to accept, in a cell's formula, this:
CHOOSECOLS(SomeArray,ColNum)
but it does accept this:
INDEX(...

0
votes

1
answer

91
views

### extract values from a variable table

i have a spreadsheet with many information, i want to extract automatically just a few information from some columns, in particular, I want to have a summary where I have 3 out of 10 or more columns (...

0
votes

1
answer

75
views

### Creating an array with a reduced set of elements matching existence of criterion in multiple columns

I want to do this on a much larger problem, but chose the simplest example for a proof of concept.
At the bottom is a CSV of my file (with formulas).
I have data for points (vertices of tetrahedron):
...

0
votes

3
answers

73
views

### What formula should I use to pull

What Excel formula can I write so it will look for the same project in Column B in Column I and place the YTD matching the project from Column J in Column C in the table below?
A B ...

0
votes

2
answers

138
views

### How to use the Offset() function when referencing the cell's tab/cell address

I am trying to use the Offset() function to return the value of a neighouring cell. Starting on the "Order" tab I would like to enter a cell reference ("source") to a "Ticker&...

0
votes

1
answer

41
views

### How to check and compare three column values in excel

I have three columns based on which I want to generate result. Lets say if any user has secured 70 and above in anyone the subject then that should be counted as passed.

0
votes

1
answer

18
views

### How to Get Date from Row Range to Populate into a Cell

I am looking for some help on a date function. I am trying to have a "dashboard" page if you will for employee training. I have created sign in sheets for each training topic with their name ...

0
votes

1
answer

62
views

### Non-volatile Excel formula to compute sum across the range at arbitrary offset from a given cell

It is well-known that Excel's OFFSET function is volatile and will cause recompute and therefore Excel will bother the user with asking to save any loaded file having such function even when the user ...

0
votes

1
answer

118
views

### Dynamic Array Subtotal that changes its column sizes without using VBA or volatile functions

I need to make a dynamic array subtotal that changes its column size by another dynamic array. The example sheet, challenge, conditions, and attempts I've made are below.
[Example Sheet]
Challenge:
...

0
votes

1
answer

19
views

### Using RAND Functions for individual cells

When using any RAND functions, it requires a trigger like F9 to recalculate the value of the cells.
For example I have both A1 and B1 with =RANDBETWEEN(1,6), and hitting F9 would trigger both cells to ...

0
votes

1
answer

28
views

### 2 Dates in 1 Cell with Formula in Date Format

I'm working between two tabs in Excel. One tab is for data entry and the other pulls the data using formulas. I'm trying to create a formula where it pulls 2 dates into 1 cell. The formula currently ...

0
votes

0
answers

85
views

### How to divide time by a constant in Excel?

I have an Excel sheet in which I have a column where each cell holds the amount of overtime accumulated since the first row. The format of this cell is [h]:mm.
In the column next to it, I want to ...

0
votes

1
answer

39
views

### MS Excel increments

I'm new to spreadsheets and really struggling to find a solution to what I'm sure should be a really simple problem.
I have a list of twenty teams. Each week they take part in a competition. Ten ...

0
votes

1
answer

50
views

### Excel finding a way to swap column data based on calculated total limit

I have an Excel sheet I've been working on and the last function I need I can't figure out. I have sizes in width x length which are typed into Columns B and D along with quantities in Col A.
The data ...

0
votes

1
answer

48
views

### Why am i having a #value in my index excel formula?

I am trying to create a formula With Index excel but there ia something wrong and i can't figure it out , below is my formula
=INDEX(TABLES!$D$98:$S$189,MATCH(1,($C$3=TABLES!$D$97:$S$97)*($C$4=TABLES!$...

0
votes

0
answers

20
views

### How to use a column in a queried table to update a column in the source table in Excel?

I have a source table that is being broken out into two different tables using Query. The tables are essentially just filtered versions of the source table. I would like for people to be able to use a ...