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

**1**

vote

**3**answers

28 views

### How do I use the AND function and the MATCH function together to get something that is not “No” and has not been returned previously

I have the following data and need to return all values that aren't "No".
I.e., I want to create, in another column,
a list of all the values from Column E that aren't "No".
There will never be ...

**0**

votes

**1**answer

16 views

### Check which (multiple) cells in column A contain substrings specified in column B?

In column A I have M&A deal descriptions that mention company names. In column B I have company names, for each of which I want to see where it is mentioned in column A.
What I'm having trouble ...

**-4**

votes

**0**answers

17 views

### I want to develop the splitting one row into multiple rows in Excel

You provided an excellent answer on how to convert Excel data in rows into a vertical column or series of columns. The page is: How do I split one row into multiple rows with Excel?
In that ...

**0**

votes

**1**answer

23 views

### Autofill formula to return data from every tenth row

In Excel 2007, how do I autofill a function of =OFFSET(C2,10,0) to end up as:
=OFFSET(C2,10,0)
=OFFSET(C12,10,0)
=OFFSET(C22,10,0)
etc.
as a pattern of referencing every 10th cell in the same row.
...

**1**

vote

**1**answer

4k views

### How do I add values in column B if column A contains the same employee surname more than once?

State of Data: I have column A which has names of employes and column B has number of hours. How do I add the hours if column A contains same employee surname more than once?
I have been using what I ...

**3**

votes

**1**answer

35 views

### Excel date month issue

I am working on projects that come in every 4 and a half month. I want to make a calender so I can see when the deadline is for each project.
I have a formula that adds up 4 months and 14 days. (4,5 ...

**0**

votes

**1**answer

2k views

### Changing cell values based on another cell

Lets say that I have 3 parameters/cells:
High (Cell A1)
Medium (Cell A2)
Low (Cell A3)
the fixed values for these cells are 2, 2 ,2
how can I change the value of cellA1 based on the changes of ...

**3**

votes

**1**answer

72 views

### Extract number from complex string if it falls in a given range

I need an Excel formula (or VBA macro) that will allow me to extract a value
from a string. The string is a sequence of words, separated by spaces, in a single cell. I want the word (representing a ...

**0**

votes

**1**answer

22 views

### Merging five colums but do not merge if row contains two values

I have 5 columns all with numeric data in. R S T U V
The first three columns can be seen here so you get the idea. I want to merge them all into the first column "R" - in the vast majority of ...

**1**

vote

**1**answer

16 views

### How do I take the information from 1 spreadsheet and count it on another spreadsheet based on a set range of values?

I have 2 spreadsheets in the same work book. On the first, I have dates names and others values (y/n, amounts). I need to be able to put in a date range on the second spreadsheet and have it take data ...

**0**

votes

**1**answer

31 views

### How to to divide column containing formulas and hard-coded strings into two separate columns?

I have a column that consists of hard-coded cells as well as formulas. Based on whether a specific cell is either hard-coded or contains a formula, I want to display it in two separate new columns.
...

**3**

votes

**3**answers

8k views

### Convert Text Value to Time Value in excel

I am trying to convert text to time. Basically I copy and pasted the total time of employees but it pastes as text value and then in order to add the time I have to convert it to time and I have to ...

**0**

votes

**2**answers

49 views

### How to lookup a value and return multiple unique values to a single cell - comma separated

I am attempting to lookup a unique identifier (Product Number) which is held in several different stock locations. For example:
__A__ __B__ __C__
1 Item # Warehouse ...

**1**

vote

**2**answers

22 views

### Returning a blank cell in MS Excel formula if 2 source cells are blank

My formula in B3 is =A3+B2-B1. I want B3 to be blank if both B2 and B1 are blank also. Could anyone help me with this please?

**0**

votes

**0**answers

23 views

### If(ISERROR(INDEX combination produces “You've entered too many arguments for this function” error

I have two working formulas and I want to combine them both:
This identifies a unique item number in a list
=IFERROR(INDEX($B$3:$B$16, MATCH(0,COUNTIF($E2:E$2, $B$3:$B$16), 0)),"")
This ...

**0**

votes

**1**answer

22 views

### Sum of Excel Range Between Dates

So I've struggled with this problem several times and usually just end up giving up on it, but this time I want to solve the problem once and for all. I have a straightforward table as follows:
Date ...

**0**

votes

**2**answers

24 views

### Fill one cell depending on value of another

I'm trying to create a Risk Assessment sheet in Excel.
I have one drop-down with days at the moment and I want another cell to be populated depending on what day is selected.
I have managed to get ...

**0**

votes

**1**answer

25 views

### Excel formula related to conditional formatting

Column A has 10 numbers expressed as percentages and is conditionally formatted to highlight the top 40%.
I want column B to show a "1" next to the corresponding highlighted top 40% of numbers in ...

**4**

votes

**1**answer

59 views

### Taking an indirect reference in Excel and making it a direct reference

For many somewhat complicated reasons – it has to do with transposing columns and rows because of third party software limitations, etc. – I have had to use the INDIRECT function in order to make ...

**-1**

votes

**0**answers

21 views

### Why doesn't my formula doesn't copy until the end of an Excel table?

When changing the formula in an Excel table it doesn't copy all the way until the end of the column. I copied information from different spreadsheets to create a new one combining information from all ...

**0**

votes

**3**answers

3k views

### Excel VBA or Formula to convert text to Date with Time type

I have a quite a number of data where its date/timestamp has been imported into excel as text. Excel does not really recognize it as date with time. e.g. 25.08.2011 16:17:59 (mm.dd.yyyy hh:mm:ss)
...

**3**

votes

**2**answers

32k views

### Searching for a value in Excel, returning yes or no

I have one workbook, with two separate worksheets. I want to know if the values that appear in worksheet B also appear in worksheet A, if so, I want to return a "YES". If not, I want to return a "NO". ...

**1**

vote

**2**answers

47 views

### Formula substitution excel

is it possible to do formula substitution easily in excel?
I have a worksheet similar to below example (albeit more complex, hence the need for some function in excel if there exists one)
What I ...

**0**

votes

**1**answer

23 views

### VLOOKUP returning N/A because references contain “.” and “ ” in one table, not in other

OK
I am trying to vlookup a table but the problem is the references on one table are slightly different to the references on the other table. I can get the VLOOKUP function working, but it is ...

**0**

votes

**2**answers

3k views

### Calculate different discount levels in Excel

I have to create a price list with 3 different discount levels called eg. E1, E2, E3.
E1 = 10%, E2 = 20% and E3 =30% discount.
How do I write a formula to tell Excel if the item falls in class E1 it ...

**0**

votes

**1**answer

21 views

### How to find number ranges (with text input) in Excel

In column A, I have some numbers, formatted as text due to leading zeros:
+------+
| A |
+------+
| 0001 |
| 0002 |
| 0003 |
| 0012 |
| 0013 |
| 0015 |
| 0017 |
| 0019 |
| 0020 |
+------+
Using ...

**0**

votes

**2**answers

37 views

### IF statement with multiple criteria and multiple possible results

I have a spreadsheet and I need a formula to give me one of three possible results (good, neutral, bad) based on data from two cells. MTD and YTD on budget would be good, MTD on budget & YTD below ...

**3**

votes

**1**answer

54 views

### INDEX + MATCH function returning wrong values

I have a spreadsheet in Excel 2013 that uses an INDEX and MATCH function to search by an interval time (in I4) to pull values from the corresponding row where it is matched (in column A). It works as ...

**2**

votes

**5**answers

30k 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

**0**answers

17 views

### Sorting data by association?

That title may have been misleading, I don't know quite how to put it.
I have a set of data where a number stands for a description. It currently looks like this:
10 Blue
11 Green
12 Purple
13 Red
14 ...

**0**

votes

**1**answer

3k views

### Combine duplicates in Excel, sum some columns and weighted average the others

This question refers to an existing subjexct on our site. I need a variation on this solution. I have a spreadsheet with 8 data columns. The number of records can vary from a few hundred to about ...

**3**

votes

**2**answers

52 views

### Summing a single cell Across Several Worksheets

I can easily sum a given cell, say B9, across multiple worksheets with something like:
=SUM(Sheet7:Sheet11!B9)
This is the equivalent to :
=Sheet7!B9+Sheet8!B9+Sheet9!B9+Sheet10!B9+Sheet11!B9
...

**0**

votes

**0**answers

25 views

### Filter Unique values in Columns or Cell Ranges from Multiple Worksheets in an Excel Workbook? [on hold]

Excel 2010: Filter Unique values from Columns or Cell Ranges from Multiple Worksheets in a workbook?
Essentially, combining data from the 3 cell ranges/ columns in multiple worksheets
Then filtering ...

**0**

votes

**2**answers

31 views

### Fill cell with sheet name Microsoft Excel

I have an excel file which has around 100 sheets in it. Each sheet is for a student, and each sheet name is the firstname_lastname e.g Mark_Hope so what I want to do is that in each sheet there are ...

**-1**

votes

**1**answer

61 views

### Calculating Stock Profit/Loss in Excel

I'm trying to create simple spreadsheet to calculate profit/loss after every complete transaction I perform on the stock market. This way I'll be able to track my portfolio performance at any moment ...

**0**

votes

**2**answers

22 views

### How do instruct a cell to display a minimum value?

I currently have the following formula in a cell:-
=CEILING(E9*0.85, 1.25)
I would like the cell containing this formula to display a value no less than 20 but only when the value of E9 causes ...

**1**

vote

**1**answer

26 views

### Compare efficiency of excel formulas

Is there any way to directly compare for calculation efficiency side by side two different formulas for achieving the same goal in excel?
Say I want to know which is more processor intensive (or if ...

**0**

votes

**1**answer

25 views

### Insert pop out table into excel cell?

Is it possible to create a pop out table/dialog in a clickable cell in Excel 2010? I would then like the sum of the table to show up in the cell.
Right now I am using a simple workaround, wherein I ...

**0**

votes

**1**answer

33 views

### How do I set a dropdown in excel to populate different data in cells below based upon the option selected from the dropdown?

I am attempting to clean up a spreadsheet that we have that tells us what permissions to grant for certain roles in our ticketing system.
I have used the Data Validation tool to create my dropdown ...

**0**

votes

**2**answers

3k views

### In Excel I need to find data matches between two arrays - one horizontal and one vertical

In Excel 2010 (or 2007 - I have both, although my OS is only Win7 32 bit as a limitation for some legacy applications we run), I need to find how I can find and return the matching value from two data ...

**0**

votes

**1**answer

50 views

### How can I pull specific rows in Excel into a new spreadsheet

I have several spreadsheets containing data I need to analyse. I have a randomized list of numbers that dictate which rows in each spreadsheet I need to code for my data sample.
Is there a formula I ...

**0**

votes

**0**answers

10 views

### Google Sheets: How to print information of user that is printing [migrated]

Well, I am creating a sheet on Google Sheets and I will share it with some people.
The users online can see its contents, but, to maintain the data update information when someone prints that, I ...

**1**

vote

**3**answers

8k 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

**0**answers

8 views

### Spreadsheet: Normalising multiple irregularly time-stamped data from sensors only roughly in sinc

I use the latest version of open office on a windows vista pc. I have .csv barometric data logged from two different sensors (pieces of equipment), that report when changes occur rather than at set ...

**0**

votes

**0**answers

10 views

### What formula in OpenOffice calc can automate game show points as needed? Details in text

I host a trivia game show in which the answer to every question is a number. The eleven teams/players closest to the correct answer without going over receive points - 15, 12, 10, 8, 7, 6, 5, 4, 3, 2, ...

**-1**

votes

**1**answer

40 views

### Show value from every sixth column with a formula that can be filled down a column

I have two Excel sheets. On Sheet A, I have several columns of data, and the average of each column is in row 41. On Sheet B, I would like to have a column that shows the averages from Sheet A. The ...

**1**

vote

**2**answers

1k views

### Creating a column of working days in LibreOffice Calc

In LibreOffice Calc, I would like to create a column of working days, for example:
[...]
2012-01-30 [Monday]
2012-01-27
2012-01-26
2012-01-25
2012-01-24
2012-01-23 [Monday]
2012-01-20
2012-01-19
...

**0**

votes

**0**answers

23 views

### sum rows and columns depending on two values

I got the following table in Excel (Microsoft Excel 2011 for Mac).
It is basically for controlling issues. It shows a number of persons (employees) that are assigned to specific work-packages that ...

**2**

votes

**4**answers

40k views

### Excel 2010: Add text to each cell of a column

How can I add @gmail.com or something similar to a column of usernames without having to type this into each cell? I am using Excel 2010. I have a list of several hundred usernames that need to have ...

**0**

votes

**1**answer

35 views

### Excel - How to check if and in how many rows a certain value/string is present in range

I have a list A of strings and I want to see if and if yes in how many rows each string is represented in range B. My data looks something like this:
List A:
aaa
bbb
ccc
Range B:
1 aaa --- ---
...