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

**1**

vote

**2**answers

26 views

### Vlookup across multiple columns

Within one workbook, I have two spreadsheets.
Sheet A ("Master") looks like the following - it shows a main branch with it sub branches.
Sheet B ("Query") looks like the following:
The ...

**0**

votes

**2**answers

22 views

### Excel: deleting duplicate records based on an int value

In a sheet that has two columns, the first represents a string and the second represents some field value associated to that string as shown below. I roughly have 30,000 rows like that.
My Question ...

**0**

votes

**2**answers

29 views

### Excel: Applying Condittional formatting to every other row

I would like to apply a specific conditional format to every second row in my spreadsheet. In my specific case I have regression results, where the first row indicates the beta coefficient and the ...

**1**

vote

**2**answers

2k views

### Comparing Two Columns in Excel, Returning a Third

I've hit a bit of a brick wall in my Excel-ing. Below is the example and explanation.
Sheet 1:
Column A | Column B | Column C | Column D
_ Name _ | _ Date ._ | _ Cost .._ | _ Code _
...

**3**

votes

**3**answers

3k views

### How to stop numbers converting to date format on copy paste in excel?

When i copy data from a site and paste in excel numbers are converting to date format
EXAMPLE: 2-1 converting to 1-feb how to stop this. i have so many records so cant do manually. is there any quick ...

**1**

vote

**2**answers

2k views

### Convert Word 2007 equations to Microsoft Equation

I am about to submit my article to one of the scientific journals.
However, it is very likely that they would only accept files in .doc, whilst my article is written in .docx and tons of formulas ...

**1**

vote

**1**answer

12 views

### Microsoft Excel lookup match without sorting data

Have a value list and need to insert points by looking up the data table. Data table has value for points in 1000 increments. Actual value that I lookup will not be rounded to 1000 so I tried using ...

**2**

votes

**9**answers

27k views

### Excel isn't calculating the SUM of cells

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

28 views

### In Excel need to copy a range of cells based on single criteria

I am trying to copy/insert a range of cells based on a single criteria. I have tried using the IF function as follows
=IF(B6="RHH1",Sheet2!A4:J15,Sheet2!A18)
The FALSE statement works and the ...

**0**

votes

**0**answers

7 views

### =VLOOKUP() function performed on dollar value displays value without “$”

I'm not entirely sure how to present pleasant-looking spreadsheet representations on SE, so I would appreciate any advice on that issue given on the side.
I want the B2 cell to display the salary ...

**0**

votes

**1**answer

35 views

### Excel if/then formula

I am wondering if there is an if/then formula for the following situation. I am trying to populate a monthly calendar. I want the calendar to show the value in A1 if the value in B1:B100 is equal to a ...

**0**

votes

**1**answer

13 views

### Extracting multiple numerical values from mixed characters in excel worksheet

I have a column containing cells with two numerical values, separated by an asterix...
e.g. 12.5*3.4
How can i extract the smaller number of the two into one new column, and the larger of the two ...

**0**

votes

**1**answer

31 views

### Excel - How do I make the text in a cell flow over into the cell below

I want the text in a cell to flow vertically, that is, flow over into the cell below. When writing in a simple text that is longer than the column width the text flows over automatically into the ...

**0**

votes

**2**answers

21 views

### Two columns (pounds, kilos) convert and fill opposite column depending on which data is entered.

I'm trying to keep two columns of data. One in Pounds and one in Kilos. (1 kilo = 2.204 lbs)
The problem is that when I add a row, it might be in kilos, it might be in pounds. Is there a formula I ...

**0**

votes

**2**answers

28 views

### Cannot delete created excel table

I have created an excel table. However, I now want to delete or change it because my range changed.
However I can't delete it in the excel Name Manager:
Any suggestion why I cannot delete it?
...

**1**

vote

**1**answer

34 views

### Countifs array with number of days

I'm looking to see how many days people are around between certain blocks.
e.g. with people and the days they are around for listed in the first table (A1:C5) and the blocks in the second table ...

**0**

votes

**2**answers

23 views

### Understanding IF() statement

I am trying to update a spreadsheet that was created by an employee that is no longer with the company. I have been successful in decoding and update all but 1 formula.
ROUND(C6,0)*IF($D$3="Y", 42, ...

**1**

vote

**1**answer

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

**1**answer

37 views

### Compare two spreadsheets in a workbook, find the differences and record the differences on the first spreadsheet

I need a way to compare two spreadsheets in a workbook, find the differences, then write those differences on the spreadsheet in sequential order in the same column below the existing numbers.
Sheet1 ...

**0**

votes

**0**answers

7 views

### Access Database Forms - Query placement and functionality

The question actually has two parts, the first is that I currently have a working access database and query for the database. I'm trying to make a search page for the database, but I would like the ...

**0**

votes

**1**answer

32 views

### Defined Name to give text or date value instead of numeric value

I had been trying out Define Name in Excel. I defined a name for a cell in which I would put a text or date value or I may leave it blank.
But when I use this defined name in another cell, the second ...

**0**

votes

**1**answer

15 views

### Nesting multiple IFERRORS with Index and Match

I feel like a dummy. I basically need a column that says - If column a2=0 then index and match column C, if that returns an error index and match column e, else index and match column a
Current ...

**1**

vote

**3**answers

5k 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 ...

**-2**

votes

**1**answer

33 views

### How to calculate monthly sum from the entries for each date in Excel?

I have colums for my workout logging:
Date
Running (minutes)
Pushups
Pullups
Crunches
and the rest of my workout entries.
I insert my achievements each day.
How can I script Excel to ...

**1**

vote

**2**answers

27 views

### Simple, Excel, =LOOKUP(xxxxx), not returning the correct value

OK this is infuriating as I know this should work...
I have certain traits in column A. Each trait has a corresponding value in column B and a numeric indicator of that value in column C. Like ...

**0**

votes

**1**answer

21 views

### Excel, how to subtract time to span before midnight

I am trying to subtract hours to a time that is close to midnight, but excel is outputting all hash (#) symbols complaining of the operation.
Adding time is easy and possible, as described by this ...

**2**

votes

**1**answer

2k views

### Lookups targeting merged cells - only returning value for first row

I have a master worksheet which contains data that I wish to link to another 'summary' sheet using a lookup.
However, some of the cells whose data I wish to include in the summary sheet are merged ...

**2**

votes

**1**answer

35 views

### Excel - sum values based on a column that match another column in another table

I've been working my head around this, but haven't figured it out yet, and I'm sure it's dead simple.
Suppose the following example:
There are two tables, one containing animals, and the other one ...

**6**

votes

**2**answers

32k views

### What is the Excel hotkey to re-calculate all formula in sheet?

I have a sheet that contains many formulas. But they do not recalculate automatically when I change input.
I'm looking for a hotkey, that can re-calculate the sheet. According to this page, F9 ...

**-1**

votes

**1**answer

34 views

### Excel function to return values in Array1 but not in Array2

Array1 = {1,2,3}
Array2 = {3,4,5}
What function will take these two arrays (as ranges on the worksheet) and return the values that appear in Array1 but not in Array2? In my example, the desired ...

**0**

votes

**2**answers

22 views

### Referencing text from one cell into an equation in Excel

I have text in a cell (A1) that is also the name of a worksheet within my workbook. How do I go about referencing a specific cell on that sheet? I want it to look like the formula below without having ...

**0**

votes

**2**answers

577 views

### Count number of cells with certain criteria

Unfortunately I need to use excel to solve an issue.
I have a (huge) sheet with many columns.
I need to create a summary of some sort.
What I need to know is how to count the number of cells (in a ...

**1**

vote

**2**answers

32 views

### MS Excel Column content replication across two sheets

Microsoft Excel functional Question
How do I replicate (copy a column contents of Values to another sheet column) of a whole column (with data content) from a primary sheet to an secondary sheet in ...

**0**

votes

**1**answer

27 views

### Nesting the IF Function in Excel when there may be #Value! errors in the target cell

I'm trying to get some help to change a formula:
=IF(S4>8000,3,IF(S4>6000,2,IF(S4>4000,1,IF(S4<4000,0))))
So that it returns a value of 1.5 if the target cell contains an error value. ...

**1**

vote

**1**answer

26 views

### How can I summarize cells based on date in other cell?

I have one Excel sheet with columns "project name", "project date", and "effort estimation".
On a second sheet I want to summarize those projects according to the months of the year.
So I have ...

**-2**

votes

**1**answer

33 views

### How to compare number with number in other column and get corresponding value

If suppose I have data like
column1 column2
504231 504109
504109 504201
504201
504299
then I used the following formula ...

**0**

votes

**3**answers

37 views

### In Excel replacing a column with another column

I have a list of Variables in Column A > list of items
another list of variables in Column B > with corresponding Item Codes for >> column A
And in Column C > I have list of item codes randomly ...

**0**

votes

**1**answer

47 views

### Insert columns, within a range, based on cell reference

So am trying to tell VBA to insert a certain number of columns between two other columsn based on a cell reference.
I've managed to get excel to insert columns within my range:
Sub INSERT()
...

**1**

vote

**1**answer

69 views

### Get the row and column for a cell in a named range?

Given a cell that exists in a named range, what's the cleanest way to get the row and column numbers for that cell inside the named range? In other words, I want the row and column number relative to ...

**0**

votes

**1**answer

18 views

### Excel drop down list in a drop down list in a drop down list

In excel I want a single cell drop down list that automatically progresses from manufacture, to model, then to part. An example would be first I click on a cell it would show four manufactures, when I ...

**0**

votes

**1**answer

23 views

### How to put conditional formatting in a cell from another excel worksheet

its hard to describe how i want, im not really familiar with excel condition terms but it is basically just like this
Worksheet= Dr. castro patient consultations
A B C D E
...

**1**

vote

**1**answer

38 views

### How can I stop excel from rounding to .00?

My Excel formulas are producing answers that only give zeros after the decimal place. For example,
=QUOTIENT(10.90,0.0450)
gives the answer of 242.00, it should be 242.22. How can I keep this ...

**0**

votes

**1**answer

93 views

### Mathcing data that includes “string1” and ends with “string2”

In Excel or Access(preferably) I want to do the following:
I have company names matched with their zip codes like:
Ozer LTD-55200
Gelibolu INC.-34759
and also I have some strings like ze and ...

**2**

votes

**2**answers

147 views

### How to get the reference of the result of an array formula in a list?

I've got two arrays, each one includes shop ids, x coordinates and y coordinates for us and the competition.
I'm looking to get for each shop of ABC corp, its closest competitor in list XYZ corp.
...

**0**

votes

**1**answer

209 views

### Excel 2013: VLookup for cells that share common characters within cell but are both surrounded by other non-matching text

I am pulling information from 2 different databases. The databases use different naming protocol for the exact same item/specified placement however they always have certain components of the name in ...

**0**

votes

**1**answer

34 views

### Use INDEX and MATCH function to find value and get a corresponding value from the returned rowindex

I want to combine columns [country code] and [admin1 code] with a "." in between and then lookup that value in Sheet1 in the [admin1code] column.
When a match is found I want to get the corresponding ...

**2**

votes

**3**answers

209 views

### Excel formula: can MATCH recognise 'n'&“01”, or 'n'&“02 ”

I have an Excel sheet (source) that has simple ID numbers in column A (01 to 40000). In another sheet (child) I have these same ID numbers in column A but with either an additional 01 or 02 added on; ...

**1**

vote

**4**answers

6k views

### How to add/subtract months from given date in Excel

Here is my scenario:
I have to populate list with dates and some metadata of upcoming events for the following year in my Excel application. Some of the events are occurring on the same day each ...

**1**

vote

**3**answers

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

**1**answer

26 views

### Excel calculations

I have a spreadsheet calculator that adds several numbers to one another to make a total
Hence A1+A2+A3 = A4 assume 2+3+4 answer = 9 = A4 (and linked to D6 in Sheet 2)
I want to store this answer ...