Questions tagged [worksheet-function]
Questions about using in-cell functions/formulas in spreadsheet software.
7,364
questions
0
votes
0
answers
5
views
Merge 2 columns in Excel and make data permanent
I know how to concatenate 2 columns in Excel, but I want the new column to become merged data NOT dependent on the formula. The reason is I can't copy the results into a new column.
I.e.
|col 1 | ...
- 11
0
votes
2
answers
12
views
IF function not populating
I am in need of help with the following if function. I moved the if(isblank) part of it up further in the condition.
Initially, the formula was this and it worked.
=IF(O9="TBD", "TBD&...
- 31
0
votes
2
answers
25
views
Excel "Average" based on variable term
I am trying to create a formula that averages values based on a variable term so its a variable average based on the term. Not sure what formula to use (or approach to take). Say I have 5 values
1,010,...
- 1
0
votes
1
answer
9
views
Stopping Excel Conditional Formatting rules changing between tabs
So I have set up a worksheet for my job and it is spread out between 3 different excel tabs. I created a number of different Conditional Formatting Rules to set up all the sheets in the same way to ...
- 1
0
votes
1
answer
24
views
Adjusting Excel Formula to reference itself within INDIRECT
I have an excel document with multiple sheets that I am trying to count occurrences and display on a summary sheet.
My current working formula looks like:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&...
- 103
0
votes
0
answers
28
views
How to apply a calculation on a table column with a range of value per cell
I have a large table where in the first column for some row rather than just a single value there is a range e.g.
I want to apply a calculation on all the data elements, but since in the first column ...
- 203
0
votes
1
answer
20
views
MS Excel range of values in cell between percentage
I am currently using this function:
=IF(G5=80%,A32,IF(G5=85%,B32,IF(G5=90%,C32,IF(G5=95%,D32,IF(G5=100%,E32,IF(G5=101%,F32,IF(G5=102%,G32,IF(G5=103%,H32,IF(G5=104%,A33,IF(G5=105%,B33,IF(G5=106%,C33,IF(...
- 1
0
votes
0
answers
36
views
Excel help formula
I need help on a formula.
I have set up a worksheet with 24 tabs. 12 review sheets, one for each month. 12 sheets for the P&L each month.
I want to copy and paste the p and l into the relevant ...
- 19
0
votes
0
answers
17
views
How to process data from leaderboards over time in excel
I have data in the form of a leaderboard over many days. The leaderboard for one day might look like:
Alice 1450
Bob 120
Caitlin 11
etc
With names and scores like that.
The leaderboard isn't the ...
0
votes
0
answers
13
views
Dynamic file path linking to another workbook
I have a series of workbooks that are stacked in sub folders (below a root folder) on a thumb drive which I want to link data to a master file in the root of the thumb drive.
I would like to have a ...
- 3
1
vote
1
answer
29
views
Easy way to fill progressive data column in excel
I am preparing monthly report of my institution. For example:
For April
Vaccine name
during the month utilization
progressive utilization
HSV
500
500
BQV
300
300
ASV
200
200
For May
Vaccine name
...
- 11
1
vote
1
answer
21
views
Calculate average score with missing values
I have an Excel-sheet with student grades, ranging from 0 to 5.
If a student didn't submit a solution for the test he/she gets a score of 'NA'.
My sheet looks like the following:
student
test 1
test ...
- 13
0
votes
0
answers
20
views
Formula to automatically distribute an amount across multiple cells where the max value of each cell and the order of distribution can be defined
EXCEL Question: I have 5 cells that I need to distribute the sum 1,000 into. I need to do this in a specific order (the 5 cells each have a numerical value that I can can be used to order the ...
- 1
2
votes
2
answers
38
views
Excel counting the number of entries filtered over categories, excluding duplicates
I'm counting the attendance to the series of events. Each event participants' data is stored in different sheet, with the identical form:
Name
Surname
Institution
Institution type - dropdown list
...
- 21
0
votes
1
answer
35
views
In Excel why use INT instead of MOD function to reduce value to a range?
In the Excel example on page 23 of Practical Astronomy with your Calculator or Spreadsheet (by Duffett-Smith and Zwart), cell C13 equals -465.9862462 hours. In order to reduce this value to the range ...
- 103
0
votes
0
answers
22
views
Possible to implement more code, so that highligted dates does'nt show until "in progress" is written
I am coordinating a large amount of production orders that are linked to thereown projects.
In the worksheet that I am working on I have created a calendar which
I have managed to highlight weekend ...
- 33
0
votes
1
answer
20
views
Excel filter function - pick data column based on cell value (header)
I'd like to output concatenated email addresses in C5 based on selected category from the pick list.
The questions is, can I filter Table 1 in cell A18 based on selected category in C2? "Table1[...
- 174
0
votes
1
answer
44
views
How to highlight specific dates in an excel calendar based on start and end date?
I am coordinating a large amount of production orders that are linked to thereown projects. In the worksheet that I am working on I have created a calendar which I have managed to highlight weekend ...
- 33
0
votes
1
answer
26
views
Use a variable in a link in Excel
I have a link in cell B2 to another sheet (e.g. ='[A new sheet]Sheet 1'!$A$1). This returns whatever is in cell A1 on that spreadsheet.
What I would like to do is put a variable in a cell and then use ...
- 1
0
votes
0
answers
31
views
Evaluate function not working
In the example, entering the combined values in K2 as a formula produces the result in J2. J2 is value copied and pasted in J4. To confirm that it is a formula that will not produce an error, the ...
- 13
0
votes
2
answers
40
views
Excel nested dynamic array formula, filtering distinct differences only
I'm working with a big dataset with many types of products to be uploaded to Shopify
In order for the correct dropdown(s) to be displayed, I have to let Shopify know what the "children" (...
- 11
0
votes
1
answer
27
views
Excel VBA How do I set active cell to be only a cell with X data?
Dim NA As String
NAFinder = 9
NA = ActiveSheet.Range("F9").Select
For i = 0 To NARows
NA = ActiveCell.Offset(i, 0).Value
If NA = &...
0
votes
0
answers
38
views
How to highlight cells in a row based on start date and end date in a calender in excel 2022
I am working on a worksheet that is used to overlook several projects and related orders. I need to be able to highlight each cell in each row that correspondes with that order and calendar row based ...
- 1
0
votes
3
answers
46
views
Don't show 1900 on blank date
I have a spreadsheet that converts the date in one cell to a year in another. I want to pull the formula down so that it automatically shows the date in columns A and B if there is a date in column C; ...
0
votes
5
answers
53
views
Find a substring after 2nd occurrence of delimiter
I am looking for a formula that help find a substring after second occurrence of a certain delimiter. For example, in below string, I want the substring that exists after the 2nd dash “-“ i.e. Ccc. ...
- 117
1
vote
1
answer
15
views
Vlookup search Bug
When I use VLookup to auto fill rows from a chart, it works for every search value except searches where the lookup value is CPT-X. In this scenario original formula:
If I change the lookup value to &...
- 11
0
votes
1
answer
28
views
Fast array formula for running total of a column for consecutive dentical values in another column
This is a follow-up question to Excel array formula for counting consecutive identical values. Here the problem is slightly more complicated.
I have a column (column E) with integer values, and ...
- 143
0
votes
0
answers
10
views
Formula works fine when entered normally, but gives #VALUE error when do evaluate(concatenate
The formula IF(AND(a2<>"",b2=$f$1),"food" works fine if I type it normally in cell C2. But if I evaluate and concatenate it in the same cell like this EVALUATE(CONCATENATE(&...
- 13
-1
votes
0
answers
32
views
String value of date not converting and returning error
The dates in column B are in string type, I have:
Used Delimeters.
Formatting Cells to date form
Tried using the =DATEVALUE() and =VALUE() to extract the number value yet its gives the #value! error.
...
0
votes
2
answers
39
views
#NAME? Excel error only occurring on one computer
We are using Excel 365. I was teaching my colleague XLOOKUP yesterday but she kept getting #NAME?.
What she entered was correct. She redid it in a new column in case any odd formatting was causing ...
- 23
3
votes
1
answer
43
views
Excel array formula for counting consecutive identical values
I have a sequence of integers and I want to compute, at each point of the sequence, how long is the current sequence of identical values. E.g., if the sequence is (0,1,1,1,0,0), I want as output (1,1,...
- 143
1
vote
1
answer
41
views
VLOOKUP logic when range_lookup is TRUE but table_array is not sorted
Excel's VLOOKUP hint tells us to use range_lookup = TRUE only when the table_array is sorted. However, does anyone know what is the logic that VLOOKUP executes when range_lookup = TRUE but table_array ...
- 11
0
votes
0
answers
29
views
Comparing a range to a value in excel is returning the wrong values
I am attempting to compare a Range of values in a column to a single value and return a True/False array.
The values are all dates and are formatted as dates.
example: =A1:A20 > 5/3/22
This is ...
- 1
0
votes
1
answer
39
views
Sort a sheet by a column while grouping rows with same value in another column
I have a sheet detailing a list of projects assigned to project workers and their progress status (1 - waiting approval, 2 started, 3 in progress, 4 completed). An individual can have multiple ...
0
votes
1
answer
27
views
How do I match columns in two worksheet and if they match check if the second col has values
I would appreciate any help with this. I have two worksheets with the following data
Worksheet 1
Worksheet 2
I am trying to match values in the name column in both worksheets and if the AGE column ...
0
votes
1
answer
11
views
How to calculate how many days have passed based on two cells values
I am trying to calculate how many days have passed upon receiving a PO after an NTP has been provided. The part I'm struggling with is that sometimes based on an entry there could be a blank in either ...
- 3
0
votes
2
answers
27
views
Excel - Sum row conditionally based on employee name
I need to create a sheet that will allow me to summarise the total cost per employee depending on their name. So, in the following sample, I would want to have a column which summarises the costs for &...
0
votes
1
answer
19
views
SUM function returning 0 value
DATA SET PREVIEW
I Used the sum function to calculate the time, as the trip duration is in seconds i used =SUM(cell/60) and then convert them to HH:MM:SS, but some of the cells are returning 00:00:00
...
0
votes
1
answer
37
views
Extensive nested IF, ISNUMBER, MATCH
=
IF(ISNUMBER(MATCH(A26;B$14#;0));A26;
IF(ISNUMBER(MATCH(A$36;B$14#;0));IF(ISNUMBER(MATCH(A$36;B$26:B26;0));
IF(ISNUMBER(MATCH(A$37;B$14#;0));IF(ISNUMBER(MATCH(A$37;B$26:B26;0));
IF(ISNUMBER(MATCH(A$...
0
votes
1
answer
24
views
Delete Specific Rows from Excel from a List of Row Numbers
I have a worksheet with 83,188 rows and 4 columns of data.
I have another worksheet that has a list of row numbers (eg 18751, 9127, 696, and etc) that signify the rows I would like to delete from the ...
0
votes
1
answer
19
views
Excel - automatically delete the first and last dates of a customer ID
Excel - I have a spreadsheet with the Customer ID, transaction amount and date as the headers.
I need to delete first and last date, including the transaction amount for every customer ID, how can I ...
- 1
0
votes
1
answer
41
views
Is there a formula in excel or VBA to count groups of values? For instance if I had a column with the an "X" in groups of 1, 2, 3 or 4
IF column B, 8 rows had xx xxx xx xxxx xx xxxx xx xxx. I need to find how many groups of two X, three x and four x. The result of the above would be xx = 4, xxx = 2 and 4 = 2. I've tried VBA but the ...
1
vote
2
answers
33
views
Interlace (merge) two spilled ranges
I am trying to merge two spilled ranges of equal sizes so that the first column of spill range A sits next to the first column of spill range B.
I'm close to an answer, but at the same time I've got ...
0
votes
0
answers
16
views
Service Desk sends 2 Updated workbooks daily- we need to copy to our internal Sheet without overriding our columns for Team Notes
We have our own internal Excel workbook, which has additional columns where the team makes notes. We don't want these to go away, that's why we have to manually copy the data from the 2 workbooks our ...
- 1
0
votes
1
answer
36
views
Excel formula to search column for specific text then list offset cells
I don’t have m365 so textjoin and filter won’t work for me
I want to look through a column to find all instances of todays date, then return a list of comma separated values in an offset column
I’d ...
0
votes
1
answer
22
views
Adding Multiple Criteria Using FILTER Function
I'm working on a sheet to where I update a tab and it automatically populates another tab sorting only the data I want to see. I used this article originally for help, and it seemed to work for 1 row ...
1
vote
1
answer
39
views
Excel Formula to display date when cells are filled in
I have a table and I need a excel formula that says the following:
If Cells C1, E1 and F1 are filled out, than today's date will appear. However if Cells C1, E1 and F1 are not filled out, than the ...
0
votes
3
answers
56
views
Calculate average in excel excluding cell contains date or zero or text
In B6 I want to calculate average for range c6:h6 skipping cells which has date, zero or non text values.
Result should be - 400
I tried using averageif with filter ex: =AVERAGE(FILTER(C6:H6,ISNUMBER(...
- 101
0
votes
3
answers
29
views
Excel RAND incrementing numbers
I have two numbers in excel a min and max and I want to fill in a random number between those two numbers but it must increase each time
thinking something like
INT(RAND()+C3)
and c3 has a value of ...
- 261
1
vote
1
answer
25
views
Is it possible to link an entry in a filtered sheet to the master sheet, matched on a key variable? (Excel)
I am trying to link a column in a filtered sheet to another column in the master sheet using a key category (i.e., id). Below is the illustrated dataset:
[]
As shown above, I have the master sheet, ...
