# 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 | ...

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&...

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,...

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 ...

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("'"&...

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 ...

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(...

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 ...

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 ...

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
...

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 ...

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 ...

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
...

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 ...

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 ...

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[...

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 ...

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 ...

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 ...

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" (...

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 ...

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. ...

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 &...

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 ...

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(&...

-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 ...

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,...

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 ...

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 ...

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 ...

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 ...

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 ...

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(...

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 ...

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, ...