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

**0**

votes

**0**answers

43 views

### Reference last cell not only in a row or column, but in a range

all users, especially Jerry Beaucaire.
I was reading this link when I stumbled upon his answer
Here is the link
How to refer to the last cell in a row in Excel?
And Jerry Beaucaire answered it like ...

**2**

votes

**1**answer

68 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

**2**answers

31 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

40 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.

**1**

vote

**1**answer

81 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 ...

**1**

vote

**1**answer

55 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

29 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

27 views

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

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

**1**answer

49 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 ...

**2**

votes

**1**answer

33 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**

vote

**3**answers

45 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 ...

**1**

vote

**2**answers

43 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

38 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 ...

**0**

votes

**2**answers

571 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

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 ...

**0**

votes

**0**answers

58 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

**3**answers

102 views

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

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 800 rows are ...

**1**

vote

**2**answers

158 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 ...

**2**

votes

**4**answers

148 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

32 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 ...

**1**

vote

**1**answer

39 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

**1**answer

37 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

**2**answers

73 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, ...

**1**

vote

**1**answer

85 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 ...

**0**

votes

**1**answer

21 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 ...

**1**

vote

**2**answers

24 views

### Rolling up the status of an excel column to a single cell

I am an intermediate excel user at best. I've looked everywhere back can't seem to be able to find this specific formula how-to.
What I need to do is in a status worksheet I have a series of tasks ...

**0**

votes

**1**answer

37 views

### excel spreadsheet average with prioritization

I need help how to write excel formula (or any excel solution)
to sum with priorities.
I have transaction data like this:
Date Unit Price Buy/Sell
7/11 3 10 Buy
7/12 2 9 ...

**0**

votes

**2**answers

54 views

### Using Index Match Conditionally to Indicate Whether

I feel pretty comfortable with INDEX MATCH, but I came across a problem I'd like to use it on where I'm not quite sure it works. I have two sheets: the first smaller sheet has a small subset of ...

**1**

vote

**2**answers

25 views

### Single formula to search column and return corresponding fields

Am having issues creating a single formula to search the billionaires list by name and autofill the rank, age, net worth, and citizenship field in the search spreadsheet.
This is the formula I have ...

**1**

vote

**0**answers

20 views

### Looking up the same value in a row and returning multipe corresponding values

I am trying to lookup value 1 in the example below and return corresponding column name. When I use index match, it only looks up the first column which corresponds to 1 in the below example, it looks ...

**0**

votes

**2**answers

116 views

### Excel - How to compare columns for equal values

I have an Excel sheet which has two columns. Both columns have numbers. The first column contains more numbers than the second column. I want to find which all numbers in the second column are there ...

**0**

votes

**1**answer

144 views

### Conditional Formatting for one cell value in a list, another cell blank

Looking for how to do Conditional Formatting for the following rule:
If a number in a cell equals one of a list of particular numbers, then information MUST be entered into an additional cell. ...

**0**

votes

**2**answers

56 views

### Populate one column with data from a list and match other column data

I have an Excel 2013 Workbook that has a worksheet (sheet 1) that contains two columns (one column for number and the second column for description, they are separate columns, but 1:1).
On the other ...

**0**

votes

**0**answers

37 views

### EXCEL: I have a column of single-word strings and would like to see if any of these strings are contained in a column of multi-word cells

Example:
In column A I have a list of words "Apple, Banana, Orange, etc." each in their own cell.
In column C I have a column of multi-word strings like C1="I ate an apple", C2="Bananas are gross" ...

**0**

votes

**1**answer

43 views

### Excel 2013 Averageifs

I want to calculate the average of time in a column of cells if the time falls within a range in another cell.
E.g. If a patient comes in between say 6 and 8 pm I want to average data in another ...

**0**

votes

**1**answer

46 views

### sumif(s), sumproduct for many to many relationship

I am looking for a formula/function that will allow me to essentially SUMIF(s) across a BOM. I know my part to FG relationship (Part, FG) and I know how many FG's I need(FG, Qty).
How can I ...

**0**

votes

**1**answer

32 views

### Error outputting a string to cell

I am using a vba macro to automate part of an excel spreadsheet. The following line keeps giving me a runtime error 1004# application or object defined error
Sheets("Material").Last.Offset(0, ...

**0**

votes

**1**answer

25 views

### How to automatically delete rows that contain certain numbers in the ID column

I have a column of numbers in another workbook and I would like to use that set of numbers to delete rows in the main workbook. I will give an simplified example of what I'm talking about below. My ...

**1**

vote

**1**answer

57 views

### How do I allocate different text cells in a row a number and then sum total that row

I have a spreadsheet setup to monitor absence in the office. The columns are the days of the month 1 - 31 and rows are the month/employees
Mon Tue Wed Thu Fri
January 1 2 3 4
...

**0**

votes

**1**answer

34 views

### Deleting rows that contain a keyword in the column

I have an excel file that has a id as one of the columns, each id has multiple entries and follow a set of survey questions. I need to delete all of the rows that have that keyword (which is in a ...

**0**

votes

**1**answer

83 views

### Excel Formula Issue Countif to Sumproduct

Having a nightmare at the moment with some formula's I've been asked to 'fix'. It would seem the users expected them to work with closed source spreadsheets but they don't. So they end up having to ...

**-1**

votes

**1**answer

24 views

### Excel formula: identifying highest weights

I need some help with the following assignment:
Task:
In the product Weight tab, you can see each product is weighted with a value per customer. Please create a Product Code for each customer based ...

**0**

votes

**0**answers

10 views

### summing values associated with names in multiple columns

I'm trying to sum up total values with names. There are 8 total columns rotating between a list of names and then values. I believe a sum if function would work, but I'm too novice to find the right ...

**0**

votes

**1**answer

39 views

### Excel 2010 Formula of 3 variables with 3 results

I haven't been able to figure out how to combine these logic & date formulas in 1 cell.
My parameters would be:
A2 will always have a manually entered date in it
B2 could have one of three ...

**0**

votes

**1**answer

30 views

### Embedded IF(OR(IF) statement

I have made a formula that basically looks for a string of text inside a cell and if it finds it puts it in another cell. I needed to check three cells so I did three separate formulas (because I'm ...

**0**

votes

**1**answer

49 views

### Excel: portable formula to reference data on previous (relative) worksheet

I need to create a fairly simple Excel spreadsheet (workbook). I need 12 sheets (tabs), one for each month.
Each sheet references cells in the previous sheet. For example, the "May" sheet is ...

**0**

votes

**0**answers

31 views

### Convert COUNTIFS formula to SUM(IF) to enable external workbook references?

I need to convert the following formula to one that uses SUM(IF), as I am given a #value error when the external data source is closed.
...

**2**

votes

**1**answer

174 views

### Replace cell references with values without F9 using macro

This is an Excel macro question. Given a series of formulas such as the following:
= A1 + A2
= B2 + B2
etc., ...
I would like to replace each of these cell references with the values without ...

**0**

votes

**1**answer

48 views

### How to I use SUMIF or SUMIFS to automatically add the values of different cells when they meet they meet multiple criteria

Basically I have a spreadsheet with three criteria columns.
I would like to be able to recognize duplicate HTS codes that also have the same country of origin and add the sum of those values, which ...

**0**

votes

**1**answer

269 views

### How do I merge data from other excel files to one master excel worksheet?

I have an uniform ID system throughout all the files, except with multiple entries and differing data named columns. I would like to create a master excel sheet with specific data columns from each ...