2022 Developer Survey is open! Take survey.

Questions tagged [worksheet-function]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
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("'"&...
user avatar
  • 101
0 votes
0 answers
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 ...
user avatar
  • 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(...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 13
0 votes
0 answers
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 33
0 votes
1 answer
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[...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 1
0 votes
0 answers
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 ...
user avatar
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" (...
user avatar
  • 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 = &...
user avatar
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 ...
user avatar
  • 1
0 votes
3 answers
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; ...
user avatar
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. ...
user avatar
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 &...
user avatar
  • 11
0 votes
1 answer
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 ...
user avatar
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(&...
user avatar
-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. ...
user avatar
0 votes
2 answers
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 ...
user avatar
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,...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 &...
user avatar
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 ...
user avatar
0 votes
1 answer
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$...
user avatar
0 votes
1 answer
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 ...
user avatar
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 ...
user avatar
  • 1
0 votes
1 answer
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
-1 votes
0 answers
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. ...
user avatar
  • 1
0 votes
1 answer
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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(...
user avatar
  • 101
0 votes
3 answers
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 ...
user avatar
  • 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, ...
user avatar
0 votes
2 answers
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 ...
user avatar
0 votes
4 answers
52 views

How do I get VLOOKUP to return all results not just one (using formula not auto-filter)

I have a table full of several orders and the dates. On another page i want to be able to type in a date and for all orders to show from that date, like a VLOOKUP. But, a VLOOKUP would only display ...
user avatar
  • 1
1 vote
1 answer
35 views

How can I multiply or divide every value in an column by 2 while ignoring blank cells using Microsoft Excel?

I am modifying .csv files using Microsoft Excel and would like to be able to quickly multiply or divide all the values within columns by 2 in order to save myself time. At the same time I also wish to ...
user avatar

1
2 3 4 5
148