Questions tagged [worksheet-function]

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

Filter by
Sorted by
Tagged with
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 ...
matt's user avatar
  • 3
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 ...
Carlos's user avatar
  • 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 ...
matt's user avatar
  • 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 ...
ZenLife18's user avatar
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 ...
DaveG's user avatar
  • 11
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 ...
Michael Lakner's user avatar
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 ...
Steve's user avatar
  • 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 ...
scorpdaddy's user avatar
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 ...
Barbara-Ann Coetzee's user avatar
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 ...
Alex Robinson's user avatar
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 ...
Kevin's user avatar
  • 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 ...
niklas's user avatar
  • 3
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 ...
ThirstyVoltage's user avatar
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 ...
Andon Key's user avatar
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 ...
PetRockBrain's user avatar
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 ...
WayneInIndy's user avatar
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+...
arun's user avatar
  • 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 ...
MrB's user avatar
  • 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 ...
Mark Johnns's user avatar
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 ...
fdmillion's user avatar
  • 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 ...
Classical Wood's user avatar
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 (...
Rockfish7495's user avatar
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
user1859445's user avatar
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 ...
eyoung100's user avatar
  • 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 ...
plu's user avatar
  • 453
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 ...
janalytics's user avatar
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 ...
Tina Hambelton's user avatar
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 ...
questionto42's user avatar
  • 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 ...
Billibong13's user avatar
-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 ...
Timothy Fisher's user avatar
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 (...
firmln's user avatar
  • 1
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 ...
Hải Nguyễn's user avatar
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 ...
Chris's user avatar
  • 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 ...
acolls_badger's user avatar
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(...
tkp's user avatar
  • 432
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 (...
Sebastiano's user avatar
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): ...
Eric Marceau's user avatar
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 ...
Lulu's user avatar
  • 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&...
robbie70's user avatar
  • 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.
 Ali's user avatar
  • 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 ...
Spencer's user avatar
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 ...
Regus Pregus's user avatar
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: ...
DYP's user avatar
  • 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 ...
PAO3092's user avatar
  • 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 ...
Ni.Sen's user avatar
  • 3
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 ...
Joe's user avatar
  • 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 ...
rocksoft's user avatar
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 ...
Bloodwolfb's user avatar
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!$...
Vera Aramouni's user avatar
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 ...
Cameron McMains's user avatar

1
2 3 4 5
161