Questions tagged [worksheet-function]
Questions about using in-cell functions/formulas in spreadsheet software.
8,002
questions
0
votes
1
answer
45
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 ...
- 3
0
votes
1
answer
31
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 ...
- 1
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 ...
- 3
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
40
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 ...
- 11
0
votes
1
answer
89
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
43
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 ...
- 1
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 ...
- 101
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 ...
- 117
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 ...
- 1
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 ...
- 3
1
vote
1
answer
67
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
78
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 ...
- 11
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+...
- 31
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 ...
- 113
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 ...
- 1,371
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
104
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 ...
- 441
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 ...
- 453
0
votes
1
answer
95
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
65
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,967
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 ...
- 11
-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 ...
- 101
0
votes
2
answers
61
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
1
vote
1
answer
45
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 ...
- 11
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 ...
- 1
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 ...
- 229
0
votes
1
answer
107
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(...
- 422
0
votes
1
answer
88
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):
...
- 254
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 ...
- 1
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&...
- 115
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.
- 1
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 ...
- 1
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 ...
- 132
0
votes
1
answer
116
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:
...
- 3
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 ...
- 11
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 ...
- 3
0
votes
0
answers
84
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 ...
- 173
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 ...
- 1
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 ...
