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

**0**

votes

**0**answers

3 views

### Calculating frequency of only visible cells

I'm having trouble calculating the frequency (in order to plot a histogram) from filtered data.
As for now, on Sheet1 I have the data of ten temperatures with a filter, I assigned a name for each ...

**0**

votes

**2**answers

24 views

### Create New Spreadsheet of data from many Excel workbooks

In retrospect, this is an issue of data-collection, but I want to see if there is an easy way to do it without doing each file.
I have about 350 separate Excel workbooks. Each workbook has a bit of ...

**0**

votes

**1**answer

24 views

### Excel Formula Assist - Find missing numbers

I have a spreadsheet exported from my database that contains Roles and Account IDs. There are 4-5 role names per account ID. I need a formula to display any accounts that do not have a role of RBD. ...

**0**

votes

**3**answers

1k views

### Excel: How to sum values in a column based on multiple criteria

I have a list of criteria in row 20, like this:
A20: "My Company, Inc."
B20: "Client Expenses"
C20: "Travel"
The data is below, like this:
B40: "Client Expenses"
C40: "My Company, Inc."
D40: ...

**0**

votes

**0**answers

20 views

### Matching a Date Conditional on its Distance to Another Date

I have a set of names and datetimes on two sheets. One sheet we'll call LargerSheet and the other we'll call Sheet1. On LargerSheet each name has multiple datetimes associated with it. On Sheet1 ...

**0**

votes

**2**answers

40 views

### Indexing a Date Range if Date is Greater Than and Within n Days of Another Date

In a previous post I asked a question about how to find the most recent date associated with a particular entity and have that value returned in Sheet2 so long as it was greater than the associated ...

**0**

votes

**1**answer

17 views

### How can I use a formula or conditional formatting to format mutilple criteria?

I am trying to do an excel function, either an IF formula or use conditional formatting to do an AND formula.
I need it to produce all three results in one formula, it is:
if the date is before ...

**1**

vote

**1**answer

12 views

### Sumproduct, Sumif, Indirect and named ranges issue

I have this formula that works:
=SUMPRODUCT(SUMIF(INDIRECT(Financials&"!$B3:$B10000"),'Information PG1'!$K$2,INDIRECT(Financials&"!d3:d10000")))
where Financials is a named range. If I ...

**-2**

votes

**0**answers

13 views

### If inventory formula needed [on hold]

Good morning, I am trying to create a spreadsheet to keep count of inventory. I need a formula that subtracts by size from the original inventory count. For example, If a cell = youth small, i need ...

**-1**

votes

**3**answers

38 views

### Excel Formula to match dates

This may sound really simple but I need a formula that checks if one column's date is the same as another, and if so put 'true' or '1' or something. I tried =IF(H2=G2, "True", "False") but it does not ...

**3**

votes

**4**answers

183 views

### Showing whether a list in Excel has a 1 or a 0 in it

So I have a column in excel that I want to roll up into one row. Essentially the column has a 0 if a feature isn't present, and a 1 if it is.
What I want to do is get excel to check a column, lets ...

**0**

votes

**1**answer

25 views

### Search from bottom with multiple criteria

I am trying to search a data table in excel and find the bottom most row that meets a specific criteria. I thought I could use this LOOKUP trick but it only returns a #DIV/0 error. This is what I ...

**0**

votes

**2**answers

24 views

### Getting current value and passing it to another function

A B
1 Date Data
2 1/1/2014 1
3 1/2/2014 2
4 3/5/2014 3
I wanted to sum up rows for certain months. With month I can get the month and sumif ...

**1**

vote

**3**answers

24 views

### Using OR condition in SUMPRODUCT

I have three columns of data - a sum column (A) and then the two columns that form the sum (B and C). Sometimes, just one of the B and C columns is filled in; occasionally the B and C columns are both ...

**0**

votes

**2**answers

46 views

### Searching and Inserting Data Based on Date Range

In another post, a formula was given that was meant to help search and insert an entry if the date associated with the entry was less than the date on another sheet. The formula was as follows:
=IF(
...

**0**

votes

**0**answers

19 views

### Excel Table Copy Wrong Formula with Newly Inserted Rows?

I am trying to create a simple spreadsheet to track costs. I have a total of five columns with different categories, two of which have formulas. I wanted to be able to insert a new row into the ...

**2**

votes

**1**answer

36 views

### Excel Help: How to scan a row and return the value of the first cell in that column

I have a matrix of numbers and the matrix has column header -- say like this:
a b c d e f
0 0 0 1 0 0
0 0 1 0 0 0
What I want to do is to search for each row for the element 1 and return its ...

**0**

votes

**1**answer

2k views

### Excel - Counta from a certain cell until a blank cell

I'm trying to create a dynamic formula which counts all the rows from cell D3 downwards until a blank cell is reached. I want to be able to add new data everyday and have the range automatically ...

**1**

vote

**1**answer

55 views

### Avoiding Long Nested IFs in Excel Function

I have a formula that relies on two different inputs: the first is an integer value between 0 and 5, and the second is the rounded sum of values in an array. I would like to have users input values ...

**0**

votes

**2**answers

29 views

### Excel - Is there a function to return a number based on which string is in a cell, like CHOOSE but not?

I need a function =Func(A1, "Alex, "Betty", "Charlie") that returns the index of the matched entry. If this function is put into B1:B13, it would show
"Charlie" 3
"Alex" 1
"Alex" 1
"Alex" ...

**0**

votes

**2**answers

2k views

### Comparing two columns in different Excel spreadsheets

I have two Excel spreadsheets. In the first spreadsheet I have two columns. Column A has 240 Accounts entries and Column B has Values for each account entry. Once again Spreadsheet 2 has its data in ...

**0**

votes

**1**answer

17 views

### Excel help to lock formulas from adjust on cell insert

I have a excel workbook where there is a hidden sheet that basically pulls data from other sheets in the same workbook just using the = formula.
The problem I am having is if I add a column or cells ...

**0**

votes

**2**answers

33 views

### Using IF to Place A Value in A Different Cell

Is it possible to do something like the following?
=IF(A1=0, Place "Yes" in B2, Do nothing)
I was considering using =OFFSET somehow, but I'm really not sure how I would implement it here.

**3**

votes

**2**answers

99 views

### Flatten Excel Formula (2007)

Say cell A1 has the formula A2 + A3
and B1 has the formula B2 + B2
and C1 has the formula A1 + B1
I need an easy and quick way to create the formula for C1 in the flattened state..
i.e.
(A2 + A3) ...

**1**

vote

**1**answer

24 views

### Excel Conditional Formatting Colour Scales highlight group of cells when another cell is a certain value

So I have a document that has 2 drop downs with the first acting as a currency selection and the other acting as a discount selection (0% - 50%). Basically whenever I select from the dropdowns the ...

**1**

vote

**1**answer

15 views

### Trying to sum a column of values between settable dates

So I have a list of costs over a period of time. Each row is a date, descriptor, quantity and value. So looking at the image, all the values in B, C and D are entered manually or through some other ...

**0**

votes

**1**answer

19 views

### What's wrong with this COUNTIFS() formula? [on hold]

Will someone please help me with the following formula
=COUNTIFS('CURRENT LEAD LISTING'!$C$2:$C$378,"ACT",'CURRENT LEAD LISTING'!$E$2:$E$378,"X",'CURRENT LEAD ...

**0**

votes

**2**answers

71 views

### Calculating time difference between two date/times from two columns in Excel

I have 5 columns: Start Date, Start Time, End Date, End Time and Duration. I need to calculate the duration between the Start Date/Start Time and the End Date/End Time. I tried just subtracting the ...

**0**

votes

**1**answer

358 views

### In Excel input filter values in cell

Using Excel I am searching for records using Filters>Down Arrow>Text Filters>Contains
(refer screenshot), this is very tedious as the seach window disappears after taking the imput and I have to ...

**2**

votes

**1**answer

13 views

### Use IRR function with discontinuous cells in EXCEL

Is there a way to use a range of cells that are not next to each other with the IRR function?
I'd like to use IRR this way:
=IRR(A1:A3 AND B2) meaning read A1, A2, A3, B2 the same way you would ...

**-1**

votes

**0**answers

23 views

### 2 Excel Tabs: Find matching values in column As and match the B columns based on A. (Only one tab has B filled)

I have two tabs in excel; one working with technologies and one with patents on those technologies. The technology tab has each tech ID in column A, and the patent tab has the same tech ID numbers but ...

**1**

vote

**2**answers

31 views

### add same value to range of cells in microsoft excel

I want to add the same word from cell B1 to B40000
I do not want to use the click and drag box method to apply it across multiple cells/rows.
I know I have to type in the formula bar B1:B40000 but I ...

**0**

votes

**1**answer

14 views

### Excel - sumifs and indirect

I am trying to obtain data from multiple worksheets:
Where:
sheet 1 - has an F2 cell that enables you to choose from a drop down list of names and each of these names equals a different worksheet in ...

**1**

vote

**1**answer

33k views

### Can Excel use formula-input data for cell references?

I have a cell in Excel, to which I want to pull data from another cell that is defined as a value in a third cell.
For example:
A1=5
A2=10
A3=insert syntax here, using A4 as source for cell ...

**0**

votes

**1**answer

27 views

### Automatically Copy the only Credit rows into a Sheet

I am trying to use a formula by the below, With out use Developer Options.
I have a excel workbook and there is a two sheets
One is -
One sheet is (sheet1)-
TXN. ID Transection Type After ...

**-1**

votes

**0**answers

15 views

### Excel - Timesheet query - find first booking in and last booking out in a day

I have a spreadsheet of about 5000 rows as follow
COLUMN A: EMPLOYEE NAME - Jo Bloggs, Sheryl Smith, etc
COLUMN B: DATE/TIME
COLUMN C: EVENT (IE "BOOK IN" OR "BOOK OUT")
I am trying to find the ...

**1**

vote

**2**answers

33 views

### Filter OpenOffice Calc rows by matching if a cell in one column exists in another column with a certain value

Have a question I cant seem to figure out using the filter.
Column A has 1400 rows with 800 additional rows that are duplicates (so 2200 total rows).
Column B has inventory amount for only the last ...

**0**

votes

**0**answers

33 views

### Microsoft Excel 2007 - Auto Update in 1st Sheet based on What updated in 2nd sheet (2nd sheet Update will be in Fixed Column but Random Row)

Actually I'm facing a problem now.
I am using Excel 2007.
And, I have two different sheet of excel file with me.
My 1st sheet is my report.
My 2nd sheet is my customer name.
I hope that when ever ...

**0**

votes

**2**answers

39 views

### How do I find the next smallest/largest value without sorting?

I have a table from which I need to pull the next largest and/or smallest value, relative to a value in a separate table. However, I also need to allow the first table to be sortable by any criteria, ...

**0**

votes

**3**answers

41 views

### Excel filter duplicate values in column A and filter out rows which have column C blank

Total n00b here.
How to filter duplicate values in column A and filter out rows which have column C blank.
I have 2200 rows in Col A (the first 1400 rows also have data in Col B & C, the next ...

**2**

votes

**4**answers

63 views

### In Excel, how to count enormous number of timestamps, and arrange counts in 5 minute periods sorted by day of week?

I've got just over 300,000 rows in a spreadsheet, and each of these rows contain a timestamp (HH:MM:SS AM) and a date (DD/MM/YYYY).
I'd like to get counts for these timestamps and group them in 5 ...

**1**

vote

**1**answer

33 views

### A formula to check if the date is older or equal to 60 days and another columns cell is equal to a value

I have tried using conditional formatting and managed to get the first part of the formula to work.
=D2:D999<=TODAY()-60
I want to then also check if the value of what is in column G is equal to ...

**1**

vote

**1**answer

22 views

### VLOOKUP not interpolating all cells?

My VLOOKUP formula is not interpolating all cells
AD AE AF
11-6-2014 15:20 90,4 #N/A
11-6-2014 15:21 89,1 #N/A
11-6-2014 15:22 90,4 Good
11-6-2014 15:23 89,1 Good
AF column is my ...

**0**

votes

**1**answer

3k views

### Copy/move values matching a pattern to another column

I have a worksheet where we need to move all the values matching a pattern to different columns.
For example, we have a B column with values that are one or more letters followed by one or more ...

**1**

vote

**1**answer

15 views

### How to remove one column of a calculated item in Pivot?

I have two columns. One with data from this year and the other with data from previous year. I now have added a calculated item in Pivot to display the % delta of these two years. Now when doing this ...

**0**

votes

**2**answers

2k views

### Comparing columns in two different excel sheets and if they match copying third column

I have limited working knowledge of Excel and I hope someone will be able to help me with my problem.
I have two different work sheets (say F1 and F2) with last name in Column A, first name in Column ...

**0**

votes

**1**answer

89 views

### Excel - find and remove text, surrounded by varying characters

I've got an Excel file with ~5,500 rows of inventory items. One cell in each row is a description field and contains HTML.
Some (but not all) of these cells contain a link formatted something like ...

**0**

votes

**1**answer

23 views

### In Excel I need to find data on sheet3 from data on sheet1

I have an Excel workbook with several sheets. The column 'SheetA'!W:W lists text that can be found within a column header on row 'Sheet3'!1:1. For example, SheetA'!W42 contains "B16". Cell ...

**0**

votes

**1**answer

16 views

### Last occurence in a “right vlookup”

I don't know if the question must be put here or on SO. Sorry if i make a mistake.
I have to search the first and last occurrence of a certain value within a column(array).
I have managed to make use ...

**4**

votes

**3**answers

221 views

### Suggestions for building and managing complex functions in Excel

Far too often, I've got a formula that wraps functions inside of functions inside of functions, and when some new condition requires that I wrap another function around it, I often find myself ...