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

**1**

vote

**1**answer

34 views

### IF formula incorporating sequential answers

I am trying to write an IF-THEN formula that outputs sequential numbers (See the example at the bottom). If you need further clarification on the process, just let me know. Here is what I am trying to ...

**1**

vote

**1**answer

18 views

### Excel Copy Formulas Horizontally

On Row 1, Column A I have =Sheet!1K2, on Column B I have =Sheet!1K3, Column C =Sheet!1K3 and so on.
On Row 2, Column A I Have =Sheet!1L2, on Column B I have =Sheet!1L3, Column C =Sheet!1L3 and so on.
...

**3**

votes

**2**answers

11k views

### AutoFill Large Number of Cells in Excel?

I have a formula in Excel that I would like to autofill to 999,999 other cells. How can I do this without dragging the autofill handle down the entire sheet?

**0**

votes

**2**answers

27 views

### Numbering Identical Items in A Column

Suppose I have multiple, repeating names as follows:
John
John
John
John
John
Tom
Tom
Tom
Tom
Tom
Tom
Anna
Anna
Anna
Anna
Anna
Anna
I would like to find a way to tell Excel: while the name is ...

**0**

votes

**1**answer

37 views

### Can I use a worksheet as a function in Excel?

I have a worksheet set up such that I can input a value, do some math to give me a bunch of values, repeat math a few times, and add up all the values and return an output value. It is complex enough ...

**0**

votes

**1**answer

30 views

### Excel-function IF

Column C calculates the earliest date from column A and B.
What would be the shortest function to display Row A1 or A2 in Column D as per the value of Column C?
Overhaul 1 Overhaul 2 Earliest due ...

**18**

votes

**7**answers

132k views

### In Excel, how do I check if a cell is in a list of values (a range of cells)

I've got a range (A3:A10) that contains names, and I'd like to check if the contents of another cell (D1) matches one of the names in my list.
I've named the range A3:A10 'some_names', and I'd like ...

**0**

votes

**2**answers

2k views

### Complex rota hours calculation in excel

OK so there are a number of different shifts in operation here and I want a total hours formula.
problem is the rota is written using a number of different notations for different shifts
some are ...

**3**

votes

**1**answer

76 views

### Match formula excel case sensitive

I have an if(iserror(match formula in excel. Just when I thought I was finished with my sheet I found out that the formula isn't case sensitive. Does anyone know how I can make my formula case ...

**1**

vote

**3**answers

2k views

### Display multiple cells if data on another workbook contains “X”value

I export a complex report daily and I transfer that information manually into another excel sheet. My question is: In workbook 1, can I get a group of cells in a row to display the text in workbook 2 ...

**1**

vote

**1**answer

5k views

### Automatic copy rows with matched column to other worksheet

Now I have a worksheet named sheet1 like the left side of image
I want to automatic copy all rows of sheet1 that have the column C = NO to worksheet sheet2 like the right side of image. And each ...

**-3**

votes

**2**answers

39 views

### How do I get Excel to look up customer names from another sheet?

I have two sheets in Excel i.e. sheet1 and sheet2.
In sheet1's column A there are account numbers of the customer and in column B there are names of the customers.
Now I want that in column A of ...

**3**

votes

**1**answer

117 views

### How to automatically colour cell background with value selected from custom dropdown list in MS Excel 2010

In MS Excel 2010 Professional is there a way to automatically copy the background colour of the custom dropdown values from somewhere else in a spreadsheet?
I have some Data Validation on a row of ...

**1**

vote

**1**answer

128 views

### How to combine data from multiple sheets into 1 main sheet in calc

I have an excel file which gets generated from various database servers on fly by executing sql. Separate sheet gets generated for each server. Now I simply want to combine data of all sheets. Each ...

**-2**

votes

**0**answers

7 views

### macro to collect data from number of sheets to main page and need to add the sheet name that where the data collected

Please help me..,I need a macro to collect data(Student names) form number of Sheets(Class Name).all student names collected from sheet1, then a new column added with the sheet name (i mean ...

**0**

votes

**1**answer

4k views

### Any trick to calculating percentages when the base is a negative number

I am trying to calculate percent change when the base number (at time 1) can be a negative and the resulting number is positive (at time 2). My problem is the percent change from -4 to +4. A change ...

**-2**

votes

**0**answers

28 views

### please make excel sheet [on hold]

Suppose you have two Excel Sheets as follows and it’s more than 1000 rows and you cannot do manually:
Sheet 1, Available List:
Area tower Br# Siza Rent Sale Height ...

**-1**

votes

**1**answer

49 views

### Using Microsoft Excel 2013 to Calculate Work Hours

I am trying to develop a table that will calculate our techs' response times.
In column C is when the call was received and in column F is when the tech responded. Columns D and E are to be ...

**0**

votes

**1**answer

22 views

### Adding Index to Match formula

I have the formula below working correctly, it is returning the max value in column G when the other conditions apply (valumes in B and F). However now I know what the Max number I want to return the ...

**0**

votes

**0**answers

10 views

### Keeping cells in the same row lined up when columns in the same row have 2 different data sources

I am working on a worksheet that references data on a different worksheet (within the same workbook). Each row in the referenced worksheet represents an order. Data from the referenced worksheet ...

**0**

votes

**2**answers

35 views

### Conditionally Formatting Across Cells

I am organizing an event and would like to do the following:
I have a General sheet with all the invitees names, addresses, etc. I also mark here if they replied and how many people will be ...

**0**

votes

**2**answers

1k views

### Positive result, negative result and current balance. How do you make starting balance show current result?

I have 3 columns:
Column A shows positive result and if the result is negative then it is in a column B.
Column B shows negative result and if the result is positive then it is in a column A.
...

**1**

vote

**1**answer

26 views

### Excel return a list of stringe where 'TRUE'

Ok so I have couple of cells listing warnings:
A B C
1 Warning1 TRUE
2 Warning2 FALSE
3 Warning3 TRUE
Now I want to return list of warnings that are set to ...

**2**

votes

**0**answers

235 views

### VBA - Arrayformula to a range stored as array variable

I need to apply a formula array to dynamic ranges in my spreadsheets, which for a fixed size range works fine using for example:
Selection.Offset(2, 0).FormulaArray = ...

**0**

votes

**2**answers

42 views

### Excel 2013 - How to remove duplicates from 1 column but leave empty cells in place of duplicate?

I have a sheet with 2 columns. first column has unique values, while the second column has many duplicates.
I would like to remove duplicates from the second column , however the cells that ...

**-6**

votes

**1**answer

35 views

### How can I use the IF condition in a formula? [closed]

Need some help with the Excel function using IF condition.
The data input is just a number format and keeps changing.
Excel Table
Home 6:00
Travel 9:00
Office 10:00
Travel back Home 18:00
...

**0**

votes

**1**answer

20 views

### Looking to total cells, but leave some blank in between

I've written a worksheet for selling work. I have one column "C" for the cost, and the column next to it "D" for a subtotal. Depending on the work to be performed, not all rows will have values to ...

**0**

votes

**1**answer

29 views

### Google docs - click cell to open data in popup

Is it possible to have a cell that just says "click me" or whatever, which opens a popup so you can see the full data? I want to keep all my data contained in one row per item, but one cell per item ...

**0**

votes

**1**answer

16 views

### How to use excel 2010 functions to caculate the sum of distinct unique values?

How to use excel 2010 functions to caculate the sum of distinct unique values?
remove the duplicate values
sum of distinct unique values
but I don't need to know who are the distinct unique values, ...

**0**

votes

**0**answers

16 views

### How do I filter multiple values that are contained within a cell in Excel? [on hold]

Excel lets you filter up to two values contained within a cell using the standard filter. I know Advanced Filter allows you to specify more criteria, but how do you search within a cell (ie. contains ...

**0**

votes

**2**answers

15 views

### How can I write-protect a worksheet, while permitting link jumping to another worksheet to enter comments?

There is a very important MS-Excel sheet that I maintain, which I need to share with other colleagues. For every row in the particular worksheet that I maintain, multiple colleagues could leave ...

**-1**

votes

**0**answers

32 views

### How do I SUM values in rows if several columns meet particular criteria?

Each week our company receives standard report which can't be modified.
Description 01/11 02/11 03/11 04/11
"All-in-one C340" 10 1 20 4
"All-in-one C345" 10 ...

**2**

votes

**10**answers

58k views

### Excel isn't calculating the SUM of cells [duplicate]

I am trying to use a very simple formula which is =SUM(B9:B11). However the cell doesn't compute for some reason.
I've used Excel for years and have never had this problem. Any idea why it may be ...

**0**

votes

**3**answers

77 views

### Compare multiple columns for any two or more cells within the same row having the same content

I need a formula to compare multiple columns for any two or more cells within the same row having the same content. If that is true, then display "TEXT A" (can be anything such as "TRUE"). If all of ...

**0**

votes

**2**answers

26 views

### Using COUNTIFS for a series of values at once

Working a step higher then COUNTIFS, I appose a challenge to write a formula without VBA code. The basic data is combined from 1000s of rows with:
Column A: rows with values from 1 to 3
Column B: ...

**0**

votes

**1**answer

16 views

### Change background color of rows which are present in another column

I have data in the A and B column. B column's data is mostly duplicates of A's data, but not always. For example:
A
Budapest
Prague
Paris
Bukarest
Moscow
Rome
New York
B
Budapest
Prague
Los Angeles
...

**4**

votes

**2**answers

2k views

### How do I download a stock price in Excel 2013? Is there an equivalent to Google Doc's GoogleFinance spreadsheet function?

In a Google Docs spreadsheet, I can use this cell formula:
=GoogleFinance("GOOG", "price")
to download the latest price of a stock. Is there an equivalent function in Excel 2013?
Earlier versions ...

**0**

votes

**1**answer

19 views

### Looking up the row number of a shared identifier in Excel, and counting the number of cells in that row that meet specific criteria?

I have a sheet that contains a lookup value in columns. I want Excel to look for each value in another sheet, and count the number of instances of a certain word in the row in which it found that ...

**1**

vote

**3**answers

1k views

### Is there a way to give an Excel table a name that is worksheet-scoped instead of workbook-scoped?

When you create a table in Excel (I'm using Excel 2007) you can give it a name that shows up in the Name Manager dialog. However, the name has workbook scope. I'd like to have a table with a name ...

**0**

votes

**1**answer

17 views

### MS Excel: Count the max spaces between a number frequency

I have a column of numbers (i.e. 7,2,5,2,6,7,5,3,7,8,9,2,3,5,6,7) What I am trying to do is count how many numbers are between each 7. Not just the first, second, third, etc. but each instance. So, ...

**1**

vote

**1**answer

36 views

### Excel: Keeping references/links when transposing

I have a worksheet in where I have table (let's call it the summary table) with references to a another table (among others) which is 2x40 (rows x columns). I would like to tranpose this into a 40x2 ...

**1**

vote

**1**answer

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

**1**

vote

**1**answer

16k views

### Need a formula that looks at the first digit or letter in a cell

I have a column of numbers. Some begin with the digit 8 and the rest begin with other digits. I want to write a formula that will return "2" if the number in column A starts with 8, and "1" if the ...

**0**

votes

**1**answer

33 views

### Excel formula to count unique values in two columns

I have two columns and I want to get distinct count for each of the account codes:
Account 12 12 34 56 90 90 91 91
Account code a b a a a b a a
...

**0**

votes

**1**answer

26 views

### What is this argument of AGGREGATE function doing in my formula?

I need to understand this part ($B$1:$B$15<>"") in the following formula used to represent a column of data with blank cells removed:
...

**1**

vote

**3**answers

6k views

### Find a non-case-sensitive text string within a range of cells

I've got a bit of a problem to solve in Excel, and I'm not quite sure how to go about doing it. I've done a few searches online, and haven't really found any formulas that seem to be useful. Here's ...

**3**

votes

**5**answers

19k views

### How to separate number by its decimal point in Excel/Calc?

I've got a number in a spreadsheet like so: 28.686279
When I open this sheet in either LibreOffice Calc or Microsoft Excel, is there a formula function that returns the "whole number", I.e. 28. Also, ...

**0**

votes

**0**answers

17 views

### microsoft excel 2010 formula writing

I am writing numbers in a cell, but need the value of these numbers to change according to the letter being assigned. For example:
70 if I add a Q on the cell before (or after) it, I want it to ...

**3**

votes

**1**answer

51 views

### Excel Formula to Check if a Word Begins with a Keyword

As the picture shows, I have 3 keywords from cell A2 to A4: a, bb, and ccc.
I have 5 words to check, listed in B2:B6: aa, bb, bc, ccand d.
I'd like to check, for each word in Column B, that if it ...

**2**

votes

**3**answers

37 views

### How to compute the number of occurrences of a string in the entire workbook?

I want to compute number of times a specified string appears in my entire workbook in some column. I have tried this formula =COUNTIF(sheet1:sheetn!C2:C150; "*string*") But it renders the #VALUE! ...