Questions tagged [worksheet-function]

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

Filter by
Sorted by
Tagged with
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 ...
  • 11
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 ...
  • 121
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, ...
  • 1,424
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 ...
  • 1
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?
  • 233
-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/...
  • 3
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 ...
  • 1
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','...
  • 233
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 ...
  • 101
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 ...
  • 386
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 ...
  • 1
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 ...
  • 101
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 ...
  • 101
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 ...
  • 1
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/...
  • 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 "...
  • 3
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 ...
  • 1
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. ...
  • 3
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 ...
  • 1
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 ...
  • 1
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 ...
  • 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 ...
  • 13
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 ...
  • 103

1
2 3 4 5
151