2022 Developer Survey is open! Take survey.

# Questions tagged [worksheet-function]

7,361 questions
Filter by
Sorted by
Tagged with
19 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("'"&...
• 101
27 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
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
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
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 ...
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 ...
1 vote
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 ...
1 vote
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
19 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
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
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
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
19 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
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
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 ...
29 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 ...
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
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 = &...
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
44 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; ...
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. ...
1 vote
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
27 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
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(&...
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. ...
36 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
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
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
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
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 ...
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 ...
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
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 &...
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 ...
36 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\$...
23 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 ...
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
39 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
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 ...
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 ...
44 views

### Finding the average of percentage differences

I have built a program that compares two path finding algorithms on how well they find the optimal path, by counting the number of nodes, position (x,y), on a grid map they have to search through. ...
35 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 ...
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
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 ...
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
28 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
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, ...
52 views

### How to concatenate multiple if statement results

I am working on a simple system that will produce the name of a task that is coming up within five days of TODAY()'s date. I was successful in getting one single case to populate. However, let's say ...