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

learn more… | top users | synonyms (3)

1
vote
2answers
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
2answers
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
2answers
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
2answers
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
3answers
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
2answers
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
1answer
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
9answers
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
3answers
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
0answers
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
3answers
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
2answers
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
2answers
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
2answers
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
1answer
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
1answer
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
1answer
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
3answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
3answers
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
4answers
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
3answers
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
1answer
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 ...