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

learn more… | top users | synonyms (5)

-1
votes
1answer
30 views

using date ranges and numbers

I have a spreadsheet that uses both dates and numbers. For example I have a job that takes 4 hours to complete and needs to be done on 10/11/13. I have several jobs that end on this date but the time ...
1
vote
2answers
6k 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 ...
0
votes
1answer
1k views

Subscripts inside Excel formulae

I have a formula in Excel that evaluates the l_x's (number of lives aged x) in mortality data: =IF(J3>J2,"l_x increased",IF(J3/J2-1>=devianceThreshold,"Deviance too high","")) Accross the ...
3
votes
2answers
649 views

Excel 2010 formula to delete repeated content based on value

I have a spreadsheet with two columns, a name and a value. The name is repeated several times with different values, for instance: Name - 1 Name - 2 Name - 3 etc. I am looking for a formula ...
0
votes
1answer
86 views

vlookup datetime FALSE; return value from 1 of two columns based on exact datetime

Based on data below, I am trying to do a vlookup such that if the datetime in ends in 18:59 return the value in column_3, if the datetime ends in 6:59 return the value in column_4... 10/7/2013 18:59 ...
0
votes
0answers
35 views

Excel for Mac 2011 move column 2 in sheet 1 to column 1 in sheet 2

I would like to move column 2 into column 1 for each successive sheet, one sheet per week, automatically. Is that possible? I do it with this formula ='1st week'!D2 and manually changing '1st week' to ...
0
votes
1answer
2k views

In excel how to do a 'sumif' function that is dynamic to a changing column

I'm trying to use a formula to do a sumif of a column of monthly data that changes depending on the current month. For example say I am entering this formula in column A of tab 2. Since it is ...
1
vote
1answer
50 views

Excel 2010 - Merging multiple rows by the value from the first row

I must find a way to merge multiple rows by the vallue from the first row. For example the image in the following Any idea how to do this?
1
vote
1answer
151 views

Excel 2010 formula to track records and replace them when surpassed or bettered

I've found using game data is an excellent way to learn the ins and outs of Excel. I made a workbook complete with personally made achievements (all of which work), but I can't find a formula that ...
2
votes
1answer
151 views

Remove pseudo-blanks in Excel?

Is there a way to make supposedly blank cells truly blank? I have had a bunch of formulae evaluate to "", whereafter I proceeded to copy the results and paste values. Now when I check for blanks ...
1
vote
1answer
906 views

How a count of values which have more than 1 occurance in a column

In cell B5 I have a formula: =SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10&"")=1)) If column A has the data as shown below, the formula returns the ...
0
votes
1answer
981 views

Use 'if' to check a condition. If condition is not met, then do not change the value in the cell. Keep the previous value as it is

Cell B1 has value 1 Cell B2 has value 1 Cell B10 has no value. I write 'if' formula: B10 = if(b2=b1, b2,) The value in B10 changes to 1. Now, I change B2 value to 2. The cell B10 changes to 0. ...
2
votes
1answer
2k views

Excel array formulas to find smallest date with multiple criteria

The array formula I have will give me the smallest date from a Service table, based on the following criteria: 1) The value in the item column matches the item # in cell A1 2) The value in the ...
0
votes
1answer
336 views

How to create a formula that can count values across multiple columns in specified rows

I have an Excel sheet where I have multiple tasks and multiple products. Each task for each product is rated red. amber or green depending on progress so far. Each task also has an owner. I want to ...
0
votes
3answers
86 views

Excel formula with multiple conditions

I want to tell certain combinations of genes that make bacteria species. So: sample number, gene A, gene B, gene C. The formula should be: If all 3 are negative then it is a rest group If A and ...
0
votes
1answer
384 views

Excel - value of the last changed cell in row

I hope someone can help, I have a number of cells in a row which is filled automatically by another application, I need to read the last changed value in that row into one cell. I know I can create ...
3
votes
1answer
610 views

Calculating difference between 2 times, excluding weekends, then averaging the results

I have an Excel 2007 worksheet where I need to calculate the difference between 2 times, excluding weekends, then at the end of a week or month, be able to find the average of the amounts of time in ...
2
votes
4answers
16k views

How do I make Excel recognise TRUE or FALSE text as boolean values?

I have been using the INT formula to convert boolean values TRUE and FALSE into 1s and 0s. However, I have copied some data I have been given into my spreadsheet and Excel hasn't recognised the text ...
0
votes
1answer
195 views

Move rows to new sheets in excel

I'm having a problem. At my work we often get spreadsheets with locations. We need to spread out each of the locations to a different worksheet. Because then we list nearby businesses to advertise to. ...
1
vote
0answers
103 views

How to create a formula which finds a cell that matches a range of other cells then produces a number if the cell appears.

am having trouble with finding a suitable formula for my spreadsheet in excel. In column A i have a list of postcodes (439 postcodes). In column B-BL i also have postcodes (these postcodes are in ...
0
votes
0answers
44 views

MIcrosoft excel 2003 auto populate cells

What I would like is input text into a cell say A1 and I want that text to then be duplicated in cell D1. But I can not type the formula into D1 itself i.e. no =a1. Is this possible? Thanks
1
vote
3answers
112 views

Collect Row # of Matching Elements in Excel

I'm trying to collect the row numbers that match a certain value in multiple columns of an excel worksheet. The behavior I'm looking for reflects the table below: |---|---------|---------|---------| ...
2
votes
0answers
1k views

How to copy a column with conditional format by “value” - Libreoffice 4.1

I have a column that contains the data and a set of rules for the conditional format of the cells. i.e. the conditional format changes the background colour of some cells if they are negative. I ...
0
votes
2answers
263 views

Return a column value based on looking at 3 other columns

I have a pivot table that has 3 columns with values. I need to perform a search of these columns and return a value from another column. The issue is this - I need excel to look at the 3 columns and ...
0
votes
1answer
150 views

Best way for bidirectional referenced table rows for double-entry booking

I want to move to a Google Spreadsheets setup to handle my private booking. Currently I have four tables: Overview Accounts Income Outcome Except of "Overview" all share following columns: Date ...
2
votes
1answer
252 views

How do make an Excel cell reference a particular sheet according to the current date?

I have an Excel spreadsheet for tracking sales with the first page being for totals, then a separate page for each month's sales, which has subtotals on each page. Can I make the "this month's total" ...
0
votes
2answers
4k views

Excel - how to either have direct data input or cell value from formula

I would like to either populate a cell value from direct key input or have the cell value derived from a formula. Example: either directly input a number into cell A4 or have the value for A4 = A2 + ...
3
votes
1answer
427 views

How to plot coordinates in Excel?

I have a list of goals scored by Ronaldo and Messi and have created a manual heat map of where they have scored goals from. I want to write coordinates in a cell in a list and for the heat map to ...
2
votes
2answers
5k views

Excel-MATCH function doesn't work on pasted values

I'm trying to find the positions of the values(text) in one column in another column. I ran the function: =MATCH(B1, A:A, 0) and I get a #N/A result. But this result is incorrect...as I clearly see ...
0
votes
1answer
462 views

Find and Replace VBA/Macro in Excel 2010

Another Excel VBA question. I'm using Excel 2010 I want to create a Macro that will remove the following 3 symbols in any cell for a selected range: [ ] " For example; If I select a range of ...
0
votes
2answers
142 views

Is there a way to compare two tabs of data and place Difference at end of one of the datasets

I need to find a way to compare two spreadsheets and if there is a Differences, pull data from one sheet to the other. Sheet1 has a list of all Dealers in my Zone. Other Tabs in my workbook have ...
2
votes
4answers
7k views

Generate a comma-separated list of cell contents, excluding blanks

To concatenate a certain four cells, I'd use: =CONCATENATE(A2,",",C2",",D2,",",F2) This would make it so that... A2 = "Matthew" C2 = "Mark" D2 = "Luke" F2 = "John" would result in ...
2
votes
2answers
4k views

Copy cell reference with multiple row increment

I've exported delivery addresses from Pastel into an Excel Spreadsheet. Each address record that Pastel spat out has a lot of data for each customer that I don't need. Given the screenshot here, I ...
0
votes
3answers
296 views

Can I use an IF statement as a parameter in a COUNTIF function?

If someone can help improve the title, by all means. I've been struggling with it for a while... I'm using Excel to analyze a list of filenames for potential issues...special characters, excessive ...
0
votes
1answer
3k views

Excel Spreadsheets: If A:A does not equal C:C, how do i list columns A, B and C somewhere else?

I have an issue with referencing an excel spreadsheet: I have data in columns A and C that's (mostly) equal, I want to isolate and create a separate list for the rows that are not equal. For ...
2
votes
1answer
197 views

Get specific value from table with table name, row and column index

I am trying to create some sort of manual filter. If you look at the image below, you can see two tables named ONE and TWO. The table to the right contains 3 dropdown lists. The idea is that you ...
-2
votes
1answer
3k views

Match up 2 columns in excel from one worksheet to another worksheet

I'm having trouble comparing two worksheets. I have worksheet A and worksheet B. In each worksheet, column A have the customer number and column B have the date. The same client and same date can ...
4
votes
1answer
2k views

Linking Excel 2013 cell to Word 2013 table cell adds unwanted line breaks

I have a problem where I am trying to copy an excel cell that contains content using a formula, into a word document table cell and link them such that the formatting is used in word. What happens is ...
3
votes
3answers
2k views

Multiplying values in Excel 2010 (Same cell)

Another excel question. I'm using Excel 2010. If for example I have a value in cell A1, lets say 2.59 What I want to do is multiply that cell by 1.333 but in the same cell I don't want to have to ...
0
votes
2answers
2k views

can Excel take data from a Text Cell and place it into a Formula Cell?

I am tracking several stocks, and saving their chart data in separate spreadsheets titled by their ticker symbol (i.e. aapl.xlsx, goog.xlsx, etc...). I want to have a single spreadsheet that I can ...
-2
votes
2answers
52 views

Excel - make a sum with some criterias [duplicate]

-----A ------B------------C 1||Name ||31-03-13 ||1-04-13 2||test1 ||>>>> 150 ||>>>> 10 3||test2 ||>>>> 750 ||>>>> 11 4||test3 ||>>>> 800 ||>>>> 12 5||test3 ||>>>> 900 ...
2
votes
1answer
2k views

Is there an Excel formula that can conditionally sum rows based on column headers?

I need to be able to put a formula into the last cell of a row (shown by = in the image) that totals the values in that row that don't have a cell with the text 'Done' at the top of the relevant ...
1
vote
1answer
360 views

Change Excel Worksheet background colour based on values

Hopefully some one can help, but not sure if its possible. I'm using Excel 2010. I want to change the background colour of a whole worksheet to green if the value in cell c20 is equal to or greater ...
1
vote
1answer
280 views

Highlight field which has a passed due date in google drive spreadsheet

I calculated a due date based on Column A with EOMONTH (Column A, 3). The resulted dates are in Column B. What I cannot achieve is to highlight the fields which are older than the current day. In ...
3
votes
1answer
678 views

Find last value in a cell range that meets a condition

I'm looking for a formula in LibreOffice Calc to find the last value in a range that meets a condition. Here is sample data: A B C D E F ...
0
votes
0answers
55 views

Formula to find after how many cells a value repeats itself

I've been looking around like crazy for this: I.m looking for a formula to count after how many cells does the value 'O' occur. For example, in the below: xyz 7 O 7 8 ...
0
votes
1answer
1k views

Counting and comparing rows for same values in excel 2007

I want to compare two columns of different sizes and count the number of rows in the two columns that are a match and report this in a separate cell. I have one column that is a list with the day of ...
3
votes
1answer
196 views

Excel macro copy same entries once in another sheet paper

I'm really new to VBA Excel. Let's say I have paper 1 with in column A many alert types, and column B the place where it happened (I'm working for a security company). What I need to do is read ...
1
vote
3answers
90 views

How can I avoid typing in a value which already exists in another range in Excel 2010?

I am working up a basic project tracking spreadsheet in Excel and would like to have two sheets - one which shows my active projects and one which shows my completed/cancelled/archived projects. I ...
1
vote
1answer
151 views

How do I lookup matching values in two Excel tables?

I have two tables, each with one common index column. One table has multiple entries of the index value with other information in adjacent columns; the other table has one entry of the index value ...