# Questions tagged [worksheet-function]

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

7,534
questions

0
votes

1
answer

11
views

### Excel find non matches reverse XMATCH

I need a formula that finds non-matching entries in array1 with the criteria array2
To make this simple (and as the actual data is confidential)
array1 is {"a";"b";"c";&...

1
vote

1
answer

17
views

### Excel - hyperlink formula referencing cells on another worksheet increasing by 10 rows

In my workbook, I have two worksheets that I link information between - WS1 and WS2.
On WS2 in column A - I have hyperlinks referring to specific cells in column C of WS1.
How do I write a hyperlink ...

1
vote

1
answer

24
views

### An Excel sheet that will have 2-3k columns and 100 rows, is it better to use vlookup/hlookup or index/match from an efficiency/size perspective?

Thinking about sheet design and unfortunately, it looks like it's going to have a lot of rows and columns that will have to be looking up values. From a size/speed/efficiency perspective, is it better ...

0
votes

1
answer

26
views

### How to set the language for formulas to English instead of the default Excel language?

Excel 2007 is installed in French language. So all formulas are in French, which is not handy:
=RECHERCHEV(...)
instead of
=VLOOKUP(...)
Question: how to set the language to English for formulas, ...

0
votes

1
answer

28
views

### Copy Distinct values from One Excel Tab to Another Based on Two Conditions

Construction project system includes safety inspection tool. Each safety inspection is numbered sequentially. Running an export reveals that inspection numbers are not unique AND each inspection ...

0
votes

0
answers

23
views

### Automatically update data in a cell when a duplicate is entered from Microsoft forms

I'm using Microsoft forms to create a user friendly data entry for assets that need to be constantly updated. The reason for using forms is I like that your able to use it on the phone as well and ...

2
votes

1
answer

28
views

### how to get the pajama effect with New-Object -com Excel.Application

Does anyone know how to get this effect? Or is there a specific function for it?

-2
votes

1
answer

24
views

### How would I go about several contract values among various monthly start and end dates in Excel? Would like to use one dynamic formula if possible

How would I go about several contract values among various monthly start and end dates in Excel? Would like to use one dynamic formula if possible
I've tried playing around with it and have come up ...

0
votes

1
answer

33
views

### Excel - Auto-update a date every week

I have a small problem with Excel, I'm actually creating a planning for cleaning rooms.
I want the date to auto-update every week
Example of planning:
I want that the cell with 01/10/22 to be 07/10/...

0
votes

1
answer

14
views

### How to reference specific cell location for conditional formatting

I'm trying to make the conditional formatting to reference each cell location that is visible. (It has been filtered)
ActiveSheet.Range("A1:P24").AutoFilter Field:=3, Critical:="Tuesday&...

0
votes

0
answers

21
views

### Is there a way in excel to dynamically change the range of column filters?

I have an excel spreadsheet which is contributed to by multiple users. The columns are currently all using the simple filter. A couple of the columns contain formulas. The columns with the formula are ...

0
votes

2
answers

44
views

### Moving average across two columns

Let's say I want to do a 10 line moving average over multiple columns. To simplify, I made this visual example:
I want to enter the algo into cell C12 and fill down. It should take a 10 line average, ...

0
votes

0
answers

22
views

### How to automatically user input to another collum excel [closed]

I want to make something like a to do list where the user can a piece of text, after the user hits enter, the piece of text needs to be applied to a 'list' in the worksheet.
For example
user input
Has ...

1
vote

0
answers

42
views

### group by in powershell

i have de following code:
cls
$x = New-Object -com Excel.Application
$x.visible = $true
$x.displayAlerts = $false
$wb = $x.Workbooks.add()
$enrolled = @('PONTEVEDRA:True','PONTEVEDRA:False','...

0
votes

1
answer

43
views

### Excel function expand counts into rows

I am adding a lookup sheet onto a large dataset of inventory for work, there are multiple jobs mixed on the list and potentially multiple lines of a given item if it has revisions. It is possible for ...

0
votes

1
answer

29
views

### Excel/Google Sheets: What is the best way to continue this formula so it will include cells yet to be filled in?

I'm manually calculating the variance and standard deviation of a set of potential investment returns, each with their own probability of occurrence.
Assuming I'd like to expand this list and not have ...

0
votes

1
answer

33
views

### How to sort 3 unusual type of dates in Excel?

I have 3 types of dates in my excel files. These dates include the above : Q (quarter), Apr (Abbreviation or abbreviated name of the month), April (Full name of the month). These are combined with the ...

0
votes

0
answers

31
views

### How do I use a series of quantities in a column of cells to populate set information for that many cells into another column?

I'm working with sensitive information, so I can't show too much, but I think my request will be clear.
I'm trying to use the below quantities to spit out values in a different sheet that number of ...

0
votes

2
answers

24
views

### How to make cell in Excel unclickable and unactive even when click?

First and foremost, I do know on how to protect the Excel files and I also know on how to Locked Or Hidden cell. The unlock cells let user to edit the cell while the lock cells will not let the user ...

0
votes

0
answers

36
views

### How to sort unusual dates in Excel? [closed]

I have to edit a lot of Excel files in the coming days. One of the things I will need to do is sort out unusual dates.
For example:
I put this example here:
1991 Q1
2001_December
May 2000
2005-Q2
...

1
vote

1
answer

26
views

### How can I best reorder data in mixed row/column format into a simple table?

I have (from a CVS file) data in the following format:
The data is a continued list (row by row of comma separated values) where in arbitrary locations columns are inserted or removed. At these ...

0
votes

1
answer

20
views

### Libre Office Calc List every wednesday

I'm trying to list the date every Wednesday in a column for a budget sheet. I think I need the DATE function, but I don't know how to find the "day" value of Wednesdays in an arbitrary month....

0
votes

0
answers

22
views

### How to add rows in excel depending on the value of cell?

I want the user to write the required number of rows in a cell, and based on this cell value that many numbers of blank rows should be generated somewhere else in the excel sheet.
Basically, I want to ...

0
votes

1
answer

18
views

### How to setting and sort row and column simutaneously

From images below, the left is the base table and the right is the table that want to show. Here, what to I want is filter for both column and row. We do choose specific date (I did not put DATE ...

0
votes

1
answer

20
views

### How to sort in column and row in order to get specific data in table Excel?

Given that image below is a simple table where the variable is DATE and TYPE. I do know on how to to filter of things in terms of only using one variable.
For instance, if I create those table, I can ...

0
votes

0
answers

32
views

### Why does excel change rows in formulas in this case

I have the following workbook:
Sheet 1 contains a series of informations row by row with several cells per row.
Sheet Formulas 1 contains a series of formulas formatting the data in Sheet 1 respecting ...

0
votes

2
answers

36
views

### Why does this LOOKUP function work like this?

Can you please help me understand why this LOOKUP function works as it does? The formula in yellow-highlighted cell A6 seems to return the label (from row 2) above the rightmost non-zero data value (...

0
votes

1
answer

41
views

### Need Formula to calculate number of months into 2023 given duration and start date

In an excel file I have been given the start date of a contract and it's duration. I need to get the number of months this contract will be active in 2023.
So for example if the start data is 10/1/...

0
votes

2
answers

58
views

### Adjust formula automatically when a row reference changes

I have two workbooks. Student Attendance The first contains a column of names of school students. The rest of the columns include attendance - "time in", "time out" and "...

0
votes

1
answer

35
views

### How to change order of values in Excel?

I want to edit arrangement of values entered in a column and separated by a semicolon.
For example this is a very small sample of my files:
And now i want to convert to this:
Note:
Always the first ...

0
votes

1
answer

37
views

### How to insert filename from cell into excel formula?

I have a bunch of .csv files that have IDs that I am trying to insert into a formula. The IDs are all listed in column F. Here is F in my sheet:
F
701
684
128
701
485
701
...
Each CSV file is in a ...

0
votes

1
answer

39
views

### EXCEL formula column how to return cell reference

The values in columns E:G are calculated from data in columns A:D. The respective formulas are as follows:
E=A+B
F=B*C*D
G=D-C-B
The excel sheet looks as follows:
Assume I want to find which formula ...

0
votes

0
answers

33
views

### Excel: Is it possible to reference the currently-selected cell?

Is it possible to select a cell and then have a formula reference that specific cell?
For instance, if I clicked on C3, my calculated cell would then show whatever value is in C3, and it would update ...

0
votes

2
answers

26
views

### Formula with changing multipliers within range of numbers

I am trying to create a formula where different values within a range of numbers are multiplied by different multipliers. For example, the number 50:
Values 1-10 are multiplied by 100, values 11-20 ...

0
votes

0
answers

18
views

### Gathering data from raw data table with multiple criteria

I am coming up empty handed trying to formulate a string to pull specific training data from a huge list.
Raw Data has over 4800 rows
Column A has over 70 Unique Names
Column B has over 74 Unique ...

0
votes

1
answer

21
views

### Excel: AverageIf Multiple Criteria, sometimes one criteria will be empty

I need a formula that finds the average of multiple criteria even if one of the criteria happens to be blank. For instance, I want to find the average of how many apples and oranges, even if oranges ...

0
votes

1
answer

29
views

### Excel formula needed to change interlinked values

I'm having 4zones with 100% business mix.
Let's suppose
Zone1 = 40%
Zone2= 15%
Zone3= 25%
Zone4= 20%
I want to know formula for below:
interchange% manually for 1 or 2 zones but rest zone have to ...

0
votes

1
answer

26
views

### Get sum() of all rows with a given month

I have this table here:
Purchase date
Price (USD)
29.12.2021
2000
04.01.2022
2300
05.01.2022
1500
07.05.2022
3800
15.09.2022
1500
I need a formula that summarizes all prices in a given month. ...

0
votes

1
answer

12
views

### How to formulate the column S so that I get the desired output as in the scenarios? I need column S to be the higher date among col Q & R

I need column S to be the higher date among col Q & R and if there's no value(-) in one of them, I want the other col value to show in col S, if both have no value(-) , I want "-" to ...

0
votes

0
answers

48
views

### IF test combining 2 cell

I am a casual user of Excel supporting my daughters ETSY business . Periodically I have to modify formulas and after trying several options require assistance.
I am stuck with what I think should be ...

0
votes

0
answers

25
views

### Excel Formula to Gather Data

i have this sheet in excel:
enter image description here
all i want to collect is like this:
enter image description here
i have tried index match but i dont get it done very well. i thought it needed ...

0
votes

1
answer

47
views

### Excel: How do I list all the values in that column based on dynamic column header name?

This is my scenario, The "Creative Direction" Sheet is a template that I give to my clients. When it is returned to me, they've added extra columns in between of my columns. It takes a long ...

0
votes

0
answers

34
views

### In excel, how do I return values beside a searched string

I am currently trying to lookup a string value in a range and have it return the value from the cell above or below it. I have tried many formulas to no avail.=INDEX(B2:J49,MATCH($V$1,B2:J49,0)-1,1) ...

0
votes

0
answers

24
views

### Excel Data Validation: If any cell in this row isn't blank there must be a value in this cell

I'm trying to set up data validation in a cell so that if any cell in a range in that row has a non-blank cell, the cell with the validation must have a value from its drop-down list. As data is ...

0
votes

1
answer

32
views

### Count number of occurences of [x] amount of zeros only after the occurence of first 1 in a row

I have a table which has only 0s and 1s in ~50 columns and ~13k rows.
What I want to do is to calculate how many times did a series of specific amount of zeroes occured in each row, after the first 1 ...

2
votes

1
answer

45
views

### How to create Excel bar graph with ranges on x-axis while?

I have a dataset with a range of percentages. See image for a of subset of the data here.
I would like to create a bar graph which includes consistent ranges of percentages on the x-axis. Like this.
...

0
votes

2
answers

31
views

### How to use vlookup or lookup or any variants of lookup to deposit an array of strings to another table?

Hello. How to use vlookup/lookup/any other types of lookup to find students whose score matches the criteria of the column in the second table (ie. score=1,score=2 etc) and then write the names of the ...

0
votes

0
answers

29
views

### Comparing two boolean cells with nested IF functions with AND functions

I have two cells, for this example A1 and B1 that take the values TRUE and FALSE. They are the boolean values not just text values. I want to create an IF function to capture the four variations of ...

0
votes

1
answer

20
views

### excel - how to create a "copy" of a worksheet in the same file, that still references to the original worksheet

excel - how to create a "copy" of a worksheet in the same file, that still references to the original worksheet
So I want it to behave like this
worksheet 1
worksheet 2 = copy of worksheet 1
...

0
votes

2
answers

26
views

### How do I create an array in excel using the "coordinate" values of each cell?

Sorry if this is trivial, but I can't seem to get this to work. I have a column of values, say from A2 to A10, and a row of values, say from B1 to P1. I want to fill out the values of the array from ...