# Questions tagged [worksheet-function]

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

**0**

votes

**0**answers

8 views

### How to prevent #REF errors appearing in formulas when referenced workbook is opened

In my workbook A, I have formulas that reference my workbook B. Once I open workbook B and enable editing, all of the formulas in workbook A show workbook B's worksheet name as #REF (but the filepath ...

**2**

votes

**2**answers

18 views

### Excel - How to return different match values with other formulas?

I have the following table and resulting formulas:
First I have the sales person and the number of sales.
Then in columns D and E, I want to know who are the best salesepeople.
The formula in E2 is ...

**0**

votes

**0**answers

18 views

### How to calculate Q1, Q2… but for a given date and not 01 Jan

I have been trying to calculate the Quarter period but not for a year that starts on 01 Jan but for a date that denotes the end of year which could be 03-22 for example.
There are a lot of examples ...

**0**

votes

**0**answers

41 views

### Excel vba, Changing cell references in formula to point to different cell location

I am trying to move (offset) only parts of formula column location to a different column location by a specified amount of columns. The formula cells change from sheet to sheet. I have thousands of ...

**-1**

votes

**0**answers

28 views

### Office 365 Excel

Q1: I am generating a percentage of completion with the formula
=COUNTA(E4:N4)/COUNTA($E$1:$CU$1)
However, I would like it to ignore any "no" values in a cell. How do I write the formula to ...

**1**

vote

**1**answer

30 views

### Assign Nested Sequential unique numbers for items in Excel Table

I'm trying to generate a unique ID number for a items in a table in excel.
For example: 1.2.4
For each column the number is uniquely numbered based on the text.
For example in the table below would ...

**-1**

votes

**0**answers

22 views

### Excel VBA programming [on hold]

One column has a 16 digit ID number. I need to be able to copy and paste that number into a cell and it will be able to tell me if it is duplicated. If it is duplicated the number of times it has been ...

**0**

votes

**0**answers

20 views

### Find the first occurrence of a value in a filtered data set, and return a corresponding value from another column

I have a spreadsheet of donor data, and I want to find out where our new donors come from each month. A data sample is below, but the full data set is approx 80,000 rows, and is formatted as a table. ...

**0**

votes

**1**answer

43 views

### Return All Column Location of String Value (Can be subbed for T/F) in a Row

I have a quandary - I am attempting to automate a process where I am manually searching across 59 cells per row, looking to provide all cell locations that house the string value of "Unmet" I have a ...

**1**

vote

**1**answer

38 views

### How to implement this formula with braces and brackets into Excel

I'd like to use Microsoft Excel to compute this formula to find y :
But I absolutely don't know how to do... It's the first time I see such one.
Could you help me ?
Note : Td2 is a known variable ...

**-1**

votes

**3**answers

77 views

### Excel Formula IF/AND with MIN Calculating Due Dates

I'm creating a spreadsheet for payment plans. I am trying to create a formula to calculate the due date based on the $ amount and enrollment date. I want the formula to calculate for different tiers ...

**1**

vote

**0**answers

11 views

### Spreadsheets: Sum points per row based on if expected value is unique within row [migrated]

Given a Google spreadsheet of quiz answers (with rows for questions, and columns for people's answers), I want to work out a person's total score. The scoring system is that if only one person gives ...

**1**

vote

**3**answers

90 views

### How to automatically fill data in Excel in a blank cell, using a logical copy of data

I have data in cells A1-A50 and also data in C1-C50.
Columns A and C are synchronized;
i.e., the value in Cn corresponds
to the value in An.
When I enter information in column A (new row, A51+) I ...

**2**

votes

**1**answer

37 views

### Excel: Sum up a column based on conditions

I have an Excel sheet with such columns:
A B C D
1 1 X INT
2 1 X INT
1 2 Y INT
2 2 Y INT
3 2 Y INT
1 3 Z INT
2 3 Z INT
3 3 ...

**0**

votes

**1**answer

31 views

### Count if subtotal

I am trying to get a subtotal of numbers smaller than 3 in another sheet (named "Detail") were the formula is. I have other subtotal formulas in the sheet that work fine, but I am unable to figure ...

**2**

votes

**2**answers

33 views

### Using GCD Function With Arrays in Excel

I'm having trouble determining GCD from a dataset. I'm trying to determine the quantity of items in a box of any given item in a warehouse without physically looking at each item on the shelves.
...

**-2**

votes

**1**answer

38 views

### Moving data from one sheet to another via a hyperlink

I've searched the existing questions and found similar questions and answers but none that meet my requirements.
I have a spreadsheet where I wish to be able to click on links in one spreadsheet to ...

**0**

votes

**2**answers

25 views

### Excel offset from two different workbook, create a copy of one without affecting the other

I am using two different workbooks the first one [Excel Data only] created as a databse and the second one [Invoice Template] created to create an invoice.
When creating an invoice i am using offset ...

**0**

votes

**2**answers

47 views

### Formula to take the closest possible result from the lookuplist [on hold]

Need your help for the formula that can get us the closest match possible from the lookup list
I need to use a formula that will get us the closest possible result from the lookup list, in this case, ...

**0**

votes

**1**answer

53 views

### Excell SUM minus numbers

I am trying to add up specific groups of numbers (some negative) in a column, then get an average.
I've tried =SUM($O8,$O9,$O10)/3 where O8, O9, and O10 are negative numbers.
Other SUMs in the ...

**1**

vote

**1**answer

22 views

### Two If statements using index match

=IFERROR(IF(I33<120,INDEX('Cost Lookup'!$E$3:$E$12*I33,MATCH(Data!J33,'Cost Lookup'!$C$3:$C$12,0))),IF(I33>120,INDEX('Cost Lookup'!$F$3:$F$12*I33,MATCH(Data!J33,'Cost Lookup'!$C$3:$C$12,0)),""))
...

**0**

votes

**2**answers

57 views

### change 2017/2/3 to 2017/02/03

I have a column in Excel which shows date but not in date format, it is in text format and the values for those dates that have one character for the month or day don't contain a zero before them.
E....

**0**

votes

**1**answer

31 views

### spreadsheet formula for returning parts of filtered cells in column

I use Libreoffice. (If Excel or GoogleSpreadsheets would be the only way to get a solution, I would be willing to switch. But the preferred platform is LibreOffice, I'm looking for a solution on this ...

**0**

votes

**2**answers

30 views

### Match a column header (of nonspecific location) against column contents, and get all row headers

I am using Excel 2016 on Windows 8.1.
I want to be able to match two criteria, where one criterion is a column header and the other is cell contents that can occur any number of times within that ...

**-1**

votes

**0**answers

33 views

### calculating average time per person [closed]

I want to try calculating average time per person for a whole Excel sheet. I need help in the formula on what to use without me filtering each person. I will be dealing with at least 300 of this every ...

**2**

votes

**1**answer

37 views

### Excel 2010 - Auto populating a sheet based on data from another sheet in the workbook

Thanks for taking the time to view my question!
I'm pretty new to Excel, trying to teach myself the basics to get stuff done at work. I've been asked to create a tracking spreadsheet for the ...

**0**

votes

**0**answers

20 views

### Conditional excel number and formula formatting based on the previous two cells

I have a spreadsheet that looks something like this:
What I want to do here is to do some kind of conditional number formatting, like if I put an item that is counted by the hour like the 'something ...

**0**

votes

**0**answers

24 views

### How to maintain column or row in drag cell? [closed]

I try to copy data from another sheet,
I use "=sheet1!D3" for the formula. But i need key in lots of data, so I need much faster way.
For example, in sheet1 I have lots of data from A1 til A90 and A1 ...

**1**

vote

**1**answer

31 views

### Getting an array result back from index-match

So a bit different from most index-match array questions
I have a table of movements from A to B. I can have movement Type 1, Type 2, Type 3, or Type 4.
Each of these movements corresponds to a ...

**0**

votes

**1**answer

27 views

### Microsoft Excel Stored Values Formula

I have a spreadsheet that I received that is using a formula with a "Stored Values" in the formula. I have never run across this use before and I'm not sure where the values are stored and what they ...

**-2**

votes

**1**answer

24 views

### Excel 2013 Duplicate Value filter formula by date [closed]

So I am trying to count how many stores were audited in a quality tool. Around 8000 records. But there are duplicates and I only wish to count a store once. On top of that I need it for a date ...

**-1**

votes

**0**answers

22 views

### Pulling column data from one worksheet to another by matching employee ID

I would like to pull employee data (the employee name) from one column in a worksheet, into a column in another worksheet using the Employee ID number to matching the entries.

**0**

votes

**1**answer

28 views

### EXCEL Cell content based on content in other cell (same row)

I've got a sheet of about 500 entries. This sheet contains 4-letter words in one column, a corresponding number to this row, and a corresponding gender. I need to make a function that finds all rows ...

**1**

vote

**1**answer

80 views

### How do you round a time and date back to a previous day with a formula in excel?

I am trying to sum up hours for an employee worked in one shift. The employees I'm having a problem with are on night shift. The employees entries in excel are shown as individual tickets for work ...

**0**

votes

**1**answer

12 views

### Drag Down Formula whilst retaining some values but change another

I'm looking for assistance with a problem i'm having when using Excel. The best way to explain the problem i'm encountering is that i'm using the auto-fill drag down to copy and paste essentially a ...

**0**

votes

**0**answers

18 views

### Update Values to an Existing Sheet from every new Sheet created by Macro

As the question on the title, I want to update the values of a sheet from every new sheet that gets created by a macro I made. I made a Macro run when inserted Client Name on the inputbox that runs ...

**1**

vote

**2**answers

27 views

### Excel Data Validation between Sheets

I have a list of ID's and Descriptions in Sheet 1. This data is then used in a second sheet (Sheet 2) which the user can interact with.
The list in Sheet 1, has an ID which repeats, but has a ...

**1**

vote

**3**answers

38 views

### Find date of third previous event

Basically, the formula needs to find the 3 most recent completions of a task, then output the date of the oldest completion of those 3. That is, the date of the third-most-recent completion of each ...

**-2**

votes

**1**answer

55 views

### Excel formula is parsed but not evaluated

As can be seen from the screenshot I have entered what seems to be a valid formula (also see this: ) . However excel just prints the formula and not the result in that cell.
=SUM(+C50:C51)
What ...

**0**

votes

**1**answer

99 views

### VBA CleanFileName and CleanUsedRange

I am having some issue with the following code. Whenever I run the vba code, the CleanFileName and CleanUsedRange code delete my Vlook formulas.
Is there any way to use the CleanFileName and ...

**-1**

votes

**0**answers

28 views

### VBA to copy and paste a formula on a set number of rows

I'm trying to write a VBA code to copy and paste a formula on a set number of rows, but to move columns every time I run the macro.
Here is what I've done so far, I basically need this to be ...

**0**

votes

**0**answers

35 views

### How to rank two values based on the following criteria

I have the following two columns, Benefit and Cost scoring results, each of which are out of a hundred.
I want to be able to order each row based on its ranking compared to each of the other row ...

**-1**

votes

**1**answer

71 views

### Calculating a percentage from the res value after deducting itself

I have a main variable number. There are static values deducted from it and a 5% value of the result of itself.
Arbitrary values:
> 40,000 (start)
- 2,000 (static)
- 300 (static)
- 200 (static)
- ...

**1**

vote

**1**answer

57 views

### Working out a team's rolling form for its previous six games

I have a spreadsheet with lots of football scores. I have attached it so you know what I'm talking about.
Here is one of the entries:
+-----+--------+-----------+-----------+--------+--------+-------...

**0**

votes

**1**answer

28 views

### How do you insert weekends into data only containing weekday dates?

I have 2 year daily closing prices for Bitcoin, from 1st August 2016 to 31st July 2018. Bitcoin can be traded 7 days a week so this data includes the dates of weekends:
I want to add more data sets, ...

**1**

vote

**2**answers

41 views

### Excel 2013: VBA to copy formula from one cell to the next

I need to copy a formula from Cell A3 to B3 in Excel, using a button to run below macro:
Sub Copy()
Range("A3").Select
ActiveCell.Offset(0, 1).Formula = ActiveCell.Formula
End Sub
Then with ...

**-1**

votes

**0**answers

19 views

### Excel Array Pulling Duplicates

I am trying to pull rows from another sheet into a different sheet. The criteria that I am looking for is "Open" and "(employee's name)". Both these criterion are going to be constant for all twenty ...

**2**

votes

**3**answers

343 views

### Copy every other value from a column

I have the below sample data set with values in Column B .
In Column D I want every other value, starting at Row 1.
0 A B C D F G H I J
1 20 =B1
2 10 =B3
3 ...

**0**

votes

**2**answers

32 views

### Make a second column's values the same, for multiple rows matched by a first column

A subset of my Excel data sheet contains names in different rows (the same name can appear multiple times) and a corresponding number (1 or 0, but if someone has a 1 after their name, it only appears ...

**-1**

votes

**0**answers

27 views

### Microsoft Excel transpose and consolidate table (see image)

http://imgur.com/LGLqsTr
Basically I was wondering what formulas etc I would need to perform the transposition and consolidation that is shown in the image above.
I want the new table to be in a ...