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

learn more… | top users | synonyms (5)

1
vote
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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
1answer
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
7answers
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
2answers
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
1answer
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
3answers
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
1answer
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
2answers
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
1answer
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
1answer
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
0answers
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
1answer
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
0answers
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
1answer
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
1answer
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
0answers
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
2answers
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
2answers
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
1answer
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
0answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
2answers
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
0answers
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
10answers
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
3answers
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
2answers
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
1answer
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
2answers
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
1answer
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
3answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
3answers
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
5answers
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
0answers
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
1answer
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
3answers
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! ...