Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

Questions tagged [worksheet-function]

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

0
votes
0answers
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
2answers
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
0answers
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
0answers
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
0answers
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
1answer
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
0answers
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
0answers
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
1answer
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
1answer
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
3answers
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
0answers
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
3answers
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
1answer
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
1answer
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
2answers
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
1answer
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
2answers
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
2answers
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
0answers
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
1answer
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
0answers
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
0answers
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
0answers
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
2answers
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
3answers
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
1answer
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
1answer
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
0answers
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
0answers
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
1answer
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
1answer
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
1answer
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
2answers
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
0answers
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
3answers
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
2answers
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
0answers
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 ...