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

**0**

votes

**2**answers

13 views

### Fill a large range with a formula in Excel, without mouse-dragging to extend

I have 5000 elements in column A and 5000 in column B
I want the element wise difference and stored in column C.
The subtraction command I know is =A1-B1 then drag all the way down with a mouse.
...

**0**

votes

**0**answers

11 views

### How do I superimpose a conditional formated heatmap based on one set of values over a different set of values in Excel 2013

I would like one cell to be formatted with a gradient color based on the value in another cell. For example, I would like cells A1 and A2 to be highlighted based on the values in B1 and B2. If I ...

**0**

votes

**2**answers

24 views

### EXCEL VLOOKUP returning #NA when IF condition specifies “ ”

=IF(VLOOKUP(D4,'[05.14_Wave 5 Priority 14 (Big+Cranberry) Specifications.xlsx]Big'!$B:$B,1,FALSE),"Project
BIG",IF(VLOOKUP(D4,'[05.14_Wave 5 Priority 14 (Big+Cranberry)
...

**1**

vote

**1**answer

41 views

### Excel 2013, SUMIFS formula

I think I need to use a SUMIFS function for this but I am not positive. I have a list of codes:
All of these codes are in a category of downtime, each code also has minutes 5,10,15,40,65 etc. My ...

**0**

votes

**2**answers

54 views

### Excel: transforming an indexed sheet with many columns to two columns

I have an excel sheet with rows like this:
Fruit | Apple | Banana | Grape | Peach
Vegetable | Cabbage| Lettuce| Carrot|
and would like a two column output duplicating the index (first column):
...

**0**

votes

**1**answer

12 views

### VBA - auto-populate date when data entered in cell

I have a tracking spreadsheet where I'd like the date a cell is completed to auto-populate in the next column. I found the following code which works for the first (I:I), but I'd like to add the same ...

**-2**

votes

**1**answer

21 views

### Excel 2010 - possible to apply a function to an entire column?

In Excel 2010, is it possible to apply a function (not a formula) to an entire column (without dragging the plus symbol all the way to the bottom)?

**1**

vote

**1**answer

25 views

### Create a formula to find difference between values in regularly spaced rows

Lets say I have 100 rows in column A. That would be 100 cells with a value in each. I only want the value of the difference between every 5 cells. For example, in the first cell, A5-A1, then next cell ...

**0**

votes

**3**answers

30 views

### How do I reference an Excel sheet name in an Excel formula?

How do I reference an Excel sheet name in an Excel formula?

**0**

votes

**1**answer

11 views

### If/Then (# to Text) With Multiple Number Ranges

I am using Excel and trying to create a formula. Part of the formula works, the next doesn't. I'll explain.
Column P is "BPM" (Beats Per Minute) and will have a numeric value, which will be entered ...

**0**

votes

**1**answer

24 views

**1**

vote

**1**answer

21 views

### How can I use if function in excel to count unique numbers which are not greater than given time duration

I'd like to ask something.
I have bunch of transaction data containing date, time, ID
like this:
DATE //TIME //ID
7/6/2015 //9:30:22 //0266731550
7/6/2015 // 9:33:19 // ...

**0**

votes

**2**answers

19 views

### excel copy values of cell if particular cell in same row is not empty

i have column A with first and last name of a person, column B with place of birth(not important in this case) and column C with employment date.
Column C can be empty.
I would like to copy values of ...

**1**

vote

**0**answers

20 views

### Search in text for column of words and return the word that matches

I have two columns of data, the first (Column A) contains a whole load of text, the second (Column C) contains the "key" data.
I want to see if the text in Column A is matched by any of the entries ...

**0**

votes

**1**answer

16 views

### Excel Drop down lists

I have a drop down list of product names. Each of these products has an assigned group of employees. I would like to select the product name and then have the assigned employees names automatically ...

**3**

votes

**1**answer

27 views

### Extract content from the formula in a referenced cell in Excel 2007

I have a cell A3 that says Name.
Cell F16 contains =A3, which displays Name, just like I want it to.
I need cell F26 to take the information from cell F16 and display the referenced location. In ...

**0**

votes

**0**answers

10 views

### Excel - Pieces of data from large chart sheet 1, auto sort on sheet2

I have a large chart and need to take small pieces of data (3 cells each for 14 rows) and have them stack ranked on the second sheet. I need A21:A34 (Branch Name), M21:M34 (% of Goal) and K21:K34 to ...

**2**

votes

**2**answers

27 views

### Excel: Calculate IF column based on another IF column

I am trying to use Excel to create a scorecard for different companies to use. The product I create needs to be a finished product - when a company representative fills out certain information on ...

**-1**

votes

**0**answers

26 views

### Find first value above 0 in Column A and B then find the first value above 0 starting from the row where the previous value came from

I have column A and B which are filled with values, mostly zeroes. All other values are above 0. If a value is above 0 in column A it will always be 0 in column B and vice versa.
I now want excel to ...

**0**

votes

**1**answer

34 views

### Count rows matching criteria for every row in excel

Hi here is a sample spreadsheet:
+--------+-------+-------------+-------------------+
| Animal | Count | Animal Type | Animal Type Count |
+--------+-------+-------------+-------------------+
| cat ...

**0**

votes

**1**answer

12 views

### SharePoint Formula =IF(ISBLANK)

Is anyone able to tell me how to fix my formula. I have tried everything and I continue to get an error message. I am running out of options.
I am trying to hide the SharePoint default date of ...

**1**

vote

**1**answer

11 views

### Inserting formula through VBA in “A1” reference style

I insert this formula through VBA,
Cells(1, 1).FormulaR1C1 = "=LEFT(A2;5)"
But the formula which gets inserted ends up with apostrophes.
=LEFT('A2';5)
This works perfectly with the R1C1 ...

**1**

vote

**1**answer

10 views

### Syntax for proper use of INDIRECT function using SUMIF in Openoffice spreadsheet to specify a functional range

Similar to others who have asked related questions, I have a spreadsheet sheet which summarizes different expense categories on a different sheet (called PC activity)in the same file. PC activity is ...

**0**

votes

**1**answer

32 views

### Excel Formula with conditions

This feels like it should be simple but my head is hurting now and I'm struggling.
I need to add a condition to an existing sheet which already contains formulas and references to other sheets.
...

**0**

votes

**0**answers

11 views

### Copy Blank Count

i want to copy the blank color count under Color column from "5-Jul-15" sheet and "6-Jul-15" sheet to ABC sheet under "5-Jul-15" and "6-Jul-15" column (i.e. C13 and E13) respectively.
It should ...

**0**

votes

**1**answer

36 views

### Searching for multiple sub-strings within a single parent-string

I've been searching all day for a similar question to mine. Many questions come close, but my questions is a bit different:
I have a list of last names in Column A. In Cell B1, I have someone's ...

**6**

votes

**4**answers

185 views

### Average numbers if another column contains a certain substring text in Excel

I'm looking for a way to average the numerical numbers in one column, say B, only if the string in column A contains a certain text.
For example:
(In column A) (In column B)
Hello table . . . 1
...

**-2**

votes

**0**answers

52 views

### lookupvalue and return multiple values in Excel do not work

I need to pull through multiple unique IDs for expenses. i.e. one timesheet consists of 5 expense payments for one date, but each of these expenses for that day contain individual unique ID. I need to ...

**0**

votes

**0**answers

19 views

### Is this possible to generate excel combo box based on cell value?

As you can see, Cell C2 got 1,2,3,4. I assume this is a student id. So, I would like to generate a combo box in Cell B8, using Cell C2's data, separated by ,. So, in this case, the combo box should ...

**0**

votes

**3**answers

22 views

### In excel, how do I group duplicates according to whether or not they meet a certain condition?

I have a list of people with each row showing a person, and their usage of a platform in percentages. if a person uses more than 1 platform, they will appear in multiple rows, each for a different ...

**0**

votes

**2**answers

24 views

### Counting number of each item assigned to customer with unlabelled columns

I have a table in Excel as follows:
1 2 3 4 5 6 7
Customer1 Z U A C D A A
Customer2 B D J S F D B
Customer3 S A C A A B B
Customer4 X H A C D A A
...

**0**

votes

**1**answer

31 views

### How can I get an average of times in Excel?

I Have a column of cells in excel of format hh:mm (The day is NOT included) like:
19:08
19:08
19:40
20:10
1:45 (Any Times before 12:00 noon are considered NEXT DAY)
one (or more of these) is after ...

**1**

vote

**3**answers

55 views

### Summarize tables of several sheets into one final Table Excel and include new sheets in summary

I have got several excel sheets, every single one of them containing a table for user information and one last sheet which contain the sum of all the previous tables. Since my table is very big, is ...

**0**

votes

**0**answers

8 views

### Multiple values of a employee in several rows how displayed in another sheet [closed]

I have employee details in one sheet, and each employees' 30 days works (30 rows) in another sheet. How could I display employee name and his 30 days work in another sheet.

**0**

votes

**1**answer

46 views

### Dynamic array/matrix generating within formula

The following formula:
=TRIM(A1:A20)
would make no sense; you typically should decide a column, write =TRIM(A1) on first cell and copy until =TRIM(A20) ... BUT ... suppose you want/need to use it ...

**1**

vote

**3**answers

50 views

### How can I automatically organize data from a text file in a spreadsheet?

I have a text file like this:
and I would like to organize into Excel like this:
I could do this by hand manually for two data entries, but I would like to be able to automate this for a larger ...

**2**

votes

**1**answer

44 views

### Use Excel to match data from a cell on a worksheet, then copy data from its neighboring column and paste on a different worksheet

I have a spreadsheet with multiple names and phone numbers next to them in each Sheet 1 - Sheet 5.
Each Sheet has about 900 names on column A and their associated phone numbers next to them on column ...

**4**

votes

**2**answers

372 views

### How to count if there's a cell with certain restrictions using Vlookup?

I'm finding if there at least one cell with the first character equal to "!"
Formula I have used without success:
=Vlookup("!";Left(N2:N49;1);1)
Why doesn't it work? And what do I have to change ...

**-1**

votes

**1**answer

38 views

### excel extracting data and concatenating fields? [closed]

I have a large excel sheet which I want to extract some certain fields.
Here is an example:
Column 1 has a list of countries:
Country: USA | UK | India | GER
Column 2 has a list of route IDs:
...

**0**

votes

**1**answer

27 views

### Pulling data from multiple spreadsheets [closed]

Need to run a report pulling data from 10 time tracking spreadsheets. In column C, if the job type = Artwork, then I need to pull in the data from columns A, B, D & I of the same row. There may be ...

**0**

votes

**1**answer

26 views

### True and False 2 sheets

I have to input a value in column I in sheet 1 from excel and in the column "D" from sheet 2 it needs to appear true or false. If there's any value in I than it should be true in "D" otherwise ...

**0**

votes

**1**answer

44 views

### Excel Formula - Match

Can someone recommend a way to write a formula that will achieve the following. I can achieve these things individually but stringing together seems to be difficult.
I have a list of orders with ...

**0**

votes

**1**answer

24 views

### In excel how could I generate a row of data from another sheet?

In excel I have a sheet named "sales", and another one named "factor".
In factor I have some fields for factor ID, customer name, product code, quantities and price.
I want when I add this data to ...

**1**

vote

**1**answer

21 views

### Excel - pivot date values with max function

I am trying to pivot date values in a pivot table in MS Excel. I am getting incorrect date values. I do not want "Grand Total" as they do not apply for dates. Is it possible to do such pivots in ...

**1**

vote

**2**answers

29 views

### How do I use IF in MS Excel-Mac 2011 for 3 variables?

When I type A, B or 0 in one cell, I want to see in other two cells (separately) to show
2,1 if I type A
1,2 if I type B
0,0 if I type 0
Can someone help me how to do this?
I currently only have ...

**0**

votes

**3**answers

43 views

### Excel Formula issue

I've been trying to write a formula to help me with rostering. It seems to work if I put the first few parts in, but after that it says "The formula you typed contains an error."
Can anyone see the ...

**2**

votes

**1**answer

39 views

### How to make a formula-based cell read-only in MS Excel?

I'm trying to create a unique ID for each row in Excel, based on the formula [RowNum + X] (X is given).
The formula autocompletes on each new row, based on Excel Tables.
After creating the new row, I ...

**1**

vote

**1**answer

24 views

### Checking column for value. If true, check same row in another column and count if not blank

I have a spreadsheet of employees. Column A is a list of Dept types (sales, HR, payroll, etc). Column B is the date their appraisal is completed, or blank if incomplete.
I would like a function that ...

**0**

votes

**3**answers

74 views

### How to keep a running balance of payments vs. billings in Excel

I have a row of cells with 12 Monthly columns, G4 (January) through R4 (December), in which I will input the amount paid towards a monthly billing. [Note that an early comment discusses multiple years ...

**2**

votes

**1**answer

90 views

### Is it possible to reference two cells in Excel to find a third cell?

I have a spreadsheet that has a decent amount of data on it. I need to return some of that data to certain cells. The data that I need to return is always near a cell with "Attached Components" in it. ...