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

**0**

votes

**2**answers

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

**2**answers

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

**0**answers

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

**2**answers

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

**2**answers

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

**1**answer

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

**3**answers

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

**1**answer

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

**3**answers

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

**1**answer

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

**3**answers

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

**2**answers

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

**0**answers

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

**1**answer

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

**0**answers

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

**0**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**0**answers

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

**2**answers

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

**1**answer

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

**2**answers

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**1**answer

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

**4**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**0**answers

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

**1**answer

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

**1**answer

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

**2**answers

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

**2**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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