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

learn more… | top users | synonyms (5)

0
votes
2answers
12 views

Getting last cell in row with data and corresponding data in column A in Excel

I have a spreadsheet that in column A lists dates. Column B may or may not have data in each cell. The question I have is when I grab the last cell with data in column B, how can I also grab the ...
0
votes
2answers
300 views

Get row number for the row matching multiple criteria in Excel

I have 2 worksheet that work together. Worksheet "Application" has a DATE column, a CODE column (a single digit number), and a VALUE column with a dollar value. Worksheet "Scheduled" also has a ...
0
votes
0answers
13 views

How to add a percentage calculation to raw data in an Excel pivot table [on hold]

I'm trying to figure out how to calculate percentages of values that are downloaded through a data connection in an Excel pivot table. The percentages are targets of values that are downloaded in the ...
2
votes
2answers
132 views

How to concatenate values in Excel?

I have a spreadsheet that looks something like this:    A B C D E ... 1| 1 0 0 1 0 2| 0 1 0 0 1 3| 0 0 0 1 0 4| 1 0 0 0 0 I'd like to concatenate row 1 in column F so ...
2
votes
2answers
23 views

How can I get the relative position of a cell in a range?

I have a range of cells in a workbook, and I want to dynamically replicate them in another workbook. I don't want to use explicit cell references in case the range is moved, I always try to use named ...
0
votes
1answer
13 views

How to search a range of cells for specific txt, then copy to adjacent cell

I have a range of cells, e.g. g1-g1000 that contains two type of entries: xx.xx or xx.xxCR, where xx.xx are numbers. I want to search the range of cells for xx.xxCR and when a cell is found, copy ...
6
votes
3answers
11k views

Excel formula to convert boolean values into {0, 1}

What is the Excel formula to convert boolean values {FALSE, TRUE} into {0, 1}? Supposing there is one shorter than =IF(cond,1,0).
0
votes
1answer
33 views

Formula to update cell based on user changing another cell's content

I want to know if there is a formula to update a cell in Excel with today's date when a user changes any value in the row that contains that cell OR when a user changes the value of 6 / 7 / 8 ...
0
votes
3answers
8k views

Comparing columns in two different excel sheets and if they match copying third column

I have limited working knowledge of Excel and I hope someone will be able to help me with my problem. I have two different work sheets (say F1 and F2) with last name in Column A, first name in Column ...
0
votes
1answer
16 views

Excel: How to give cells chosen by some criteria as a parameter in Variance (or any other) formula?

For the following Excel table: A B C 1 Score Category VariancePerCategory(Calculated) 2 0.3146 Orange 0.034214 3 0.5711 ...
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 ...
0
votes
2answers
38 views

Compare lists and add missing

I have 8 Spreadsheets on one workbook. The first one is a weekly overall view. The other seven are broken into days. Each day lists some names of people that came on site. So Monday might have Joe, ...
0
votes
0answers
18 views

Excel Index match formula [on hold]

How can I use Index match formula to lookup and match below column B value of sheet1 to column A value of sheet 2 if column C in sheet 2 is having a value? and display that in sheet 1 column C either ...
0
votes
1answer
33 views

Count filled cells in row, given from adjacent column not being empty in Excel 2010,

I have a two worksheets, one in which I have a list of tasks as well as several other columns listing the status of the task and one column listing if the task is imortant or not. In the other sheet, ...
0
votes
0answers
18 views

Delineate Excel Formula Without Using Wizards

Is there a combination of excel formulas that I could use to do this without using VBA or the import wizard? I know how to insert comma delimited data from a text file and to split a cell into ...
-1
votes
0answers
10 views

Excel - Formatting one cell from a series of cells

I am trying to format one cell (H) from a series of cells (C, D, E, F). For example if column C = "x" then column H = 120 if column D = "x" then column H = 160 if column E = "x" then column H = 200 ...
-1
votes
1answer
50 views

Excel 2010 display two dates in one cell which sum invoices of that period [closed]

MONTHLY INCOME AND EXPENSES INCOME gen-15 feb-15 mar-15 Pension €1.571,97 € 1.571,97 € 1.571,97 Previous Month €1.143,62 ...
-4
votes
1answer
20 views

Consolidate Text Data in Excel

I have an Excel document containing exam results. The results are in one column and appear as Pass or Fail. I want to know percent of candidates who passed.
2
votes
1answer
46 views

Is it possible to automatically add cells from a new sheet to a formula in Excel?

I would like to make a new financial spreadsheet for tracking expenses. Since the expense sheets are created as and when I cannot pre-generate a spreadsheet to populate with formulae. I have a summary ...
0
votes
1answer
66 views

Excel table: Add only certain values (Oscar predictions ballot)

So for tonight, me and friends made a spreadsheet with oscar nominations and predictions of winners. What I'm trying to achieve is that I just put a '1' or an 'x' or something behind the winner in ...
0
votes
1answer
21 views

How to summarise costs for one week, given an irregular range of dates and costs?

I have a table of all the money I've spent on given dates, from receipts etc. The dates aren't regular, so the table may look something like: A B ╔════════════╦════════╗ 1║ Date ║ ...
1
vote
0answers
28 views

How to drag fill series in excel to adjust sequencing without removing the hyperlinks attached to cells?

I have a range of cells in a column in excel, that have some number in them along with text. For Example: TA_47 TA_48 TA_49 TA_50 TA_51 TA_52 TA_53 TA_54 TA_55 TA_56 TA_57 TA_58 TA_59 TA_60 Now I ...
1
vote
2answers
20 views

How do I Calculate Variance with Missing Values coded as -99?

I am trying to calculate individuals' variances on multiple measures, however, if they skip a question it is coded as -99. I would like my formula to calculate the variance for these measures while ...
0
votes
1answer
26 views

Spreadsheet finding and sorting most frequent appearance of names

I have a spreadsheet that looks like the following: https://docs.google.com/spreadsheets/d/1BN3GNRFCsBeHu9gQaKzIo7bfFRkcDqMJ6VMtidEwPD4/edit?usp=sharing There is a set list of names in Column A. ...
0
votes
2answers
31 views

How do I write an IF Statement for Percentage Formulas?

How to I write an IF statement that if I divide two negative numbers, the results show a negative percentage and not a positive percentage? Example: (69,002)/(11,440) = 603.16% I need for ...
1
vote
1answer
20 views

Conditional Formatting Based on Prefix Character (')

In Excel there are prefix characters which appear before the cell contents. Usually this is a blank character and Excel formats based on its best guess. If an apostrophe is used it treats everything ...
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 ...
0
votes
1answer
30 views

How do i do a “tweaked” VLOOKUP?

So here is the problem: I have 3 lists (adjacent columns) in a spreadsheet. The 1st list contains the values I am searching for The 2nd list contains the first half of table array which I am ...
0
votes
1answer
43 views

Formula referencing different spreadsheet needs to output both text and number formats

So I have a formula that references a different spreadsheet: =IF('Raw Data Import'!G2=""," ",'Raw Data Import'!G2) which outputs text format. However, since this cell is used in a different ...
0
votes
1answer
22 views

Formula for COUNTIFS excluding another column

I'm trying but failing to work out a formula in Excel 2007. I have an large area of data that isn't in a table format. The main columns I care about are: chapter title (text), number of tasks ...
1
vote
1answer
94 views

Excel 2013 Column value determines another columns letter assignment.

I have 5 columns but I need to define column E by column A, If column A has a number that isn't zero in the array, column E should read "Overdue", what formula can I use?
0
votes
0answers
26 views

Excel: Converting two lists into a matrix

I have two lists in my workbook currently Name | System 1 | System 2 | System 3 | John | x | x | | James| | x | x | Peter| | x | ...
1
vote
1answer
18 views

SUM Array using IF AND

I am trying to get a SUM function to check both the month and a check of Y or N to return a total. Currently I am using the array formula =SUM(IF(MONTH(A8:A1000)=1,C8:C1000,0)) to do the month ...
0
votes
1answer
42 views

Excel Formula show with result as well as [closed]

I have a sheet with: A B C 7 5 =A1+B1 So, C1=12. Is it possible that D1 shows the numbers 5,7 used in the sum in C1?
2
votes
4answers
369 views

Robust way of creating formulas in Excel

To simplify the problem, let's say I have an Excel sheet which has different numeric values in the cells A1 to B100 (two columns by 100 rows). In column C, I would like to have a formula that will ...
0
votes
1answer
12k views

If cell B1=1 then DELETE cell A1 (excel 2003 formula) - How to do it?

I work with Excel 2003. If cell B1=1 then DELETE cell A1, and if cell B=0 then UNCHANGED. How can I do this? Example: http://oi33.tinypic.com/mwsm03.jpg Many thanks :)
0
votes
1answer
624 views

Excel: URL with normal Text in a Cell

How can you add an HTML URL to a cell with normal text? I have a cell with normal text, but want to add a URL, but I don't want the whole cell to be the URL. i.e. Please go to this link: ...
0
votes
1answer
5k 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 ...
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 ...
0
votes
1answer
18 views

Nested If Statements Without Invoking Circular Reference Error [closed]

I have a formula that calculates perfectly, except in one case near the end where it becomes negative. I was wondering if it would be possible to have the cell containing the formula say "If this ...
1
vote
2answers
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 ...
0
votes
0answers
34 views

copy and paste according to a order in a column

I want a macro that will fetch data from Series B and Automatically paste it according to the serial of Serial A. ASeries B and Series A data are same but Series B is not ordered. So i'm looking for ...
0
votes
1answer
29 views

What's more efficient, array formulas or individual formulas? [closed]

Simple question: When adding, multiplying, or otherwise doing simple operations on two ranges is it better to use array formulas or individual formulas? =A1+B1 =A2+B2 =A3+B3 vs {A1:A3 + B1:B3} ...
0
votes
1answer
24 views

Combining Excel Formulas

I'm trying to do something on Excel which isn't working. I've managed 2 separate formulas that work separately but can't figure out how to combine them. Separate formulas are: =IF(K3>I3,"","Yes") ...
1
vote
2answers
78 views

Select elements in an array that satisfy a criteria

The goal: Kappa-Sigma clipping in Excel I want to implement a selective linear regression formula in Excel (avoiding VBA). I have a data sample on which I need to apply a linear regression. But my ...
7
votes
2answers
588 views

Formula for finding 'near perfect' matches [duplicate]

I have a list of +-8000 items which is the result after applying numerous techniques to remove duplicates (the list started at over 10000 items). I now have the following issue - Example 1. Jack ...
18
votes
2answers
92k 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 ...
0
votes
1answer
23 views

If statement in excel 2007

I have a if statement in a cell that looks for the value in two different cells depending on which one is >0. Both target cells have a formula that selects a value based upon the value of the cell as ...
0
votes
1answer
39 views

How to set an excel cell's value programmatically without using VBA

I want to be able to change the value of a cell using a test conducted in a different cell. For instance, something like the following. A B 1 do =IF(EXACT(A1,"do"), set(P5="it was ...
2
votes
1answer
45 views

Why is my formula pulling the result CELL NAME and not the value of that cell?

I am creating a worksheet that compares two sets of data pulled on different dates. The column that everything pulls off of is created by an array formula comprised of combined unique values from the ...