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

**1**

vote

**1**answer

43 views

### Excel 2007 how to put a text message in a cell if multiple cells are filled with data

I am trying to put a text message in a cell (say c1) if multiple cells in column A have data in. The cells in column A are A1-3, A5, A6 A8 and will have data from 1-5. I only want the text message ...

**0**

votes

**0**answers

47 views

### IF Function Combined with Median date range check across multiple sheets

I am trying to create a formula that checks if a single cell date falls between a beginning and end date using the median function, and if so returns a value. I have multiple sheets I would like the ...

**0**

votes

**2**answers

35 views

### Excel 2007 - formula to display last updated cells

I have 6 cells, A1-A6 in Sheet1.
A1,A3,& A5 contain numbers 1-3
A2,A4,& A6 contain the date the numbers changed.
A1 & A2 will be filled first. I want to display both cells in Sheet2 in ...

**0**

votes

**2**answers

35 views

### How can I create an IF-THEN statement in Excel with two variables?

A B C(Output)
----------- ----------- ------
| 65 | 0 | 2 |
----------- ----------- ------
| 31 | 99 | 3 |
----------- ...

**0**

votes

**1**answer

28 views

### Use AVERAGEIF across multiple cell ranges in a worksheet

I am using AVERAGEIF using the following to return the answer
=AVERAGEIF('Sheet 2'!H3:K10,I9,'Sheet 2'!K3:K10)
This works well if I have all the information in one sheet, however I have the ...

**3**

votes

**1**answer

43 views

### Using a Defined Name in a Formula

I have a set of cells with values:
A1,B3,C5,D7,E11,F13,G17,H19,I23
I have a formula to select, at random, the value stored in one of these cells:
...

**0**

votes

**1**answer

34 views

### Why doesn't this array formula work when cell reference contains a formual?

I have created an array formula (first formula below) to return the nth value in Data!B:B based on the date value in B4. The formulas appear to work when I have the date in B4 but if I use the second ...

**1**

vote

**1**answer

73 views

### Excel: delete row if empty cell condition is true across multiple columns

I have a data set in Excel that i need to filter out rows where no data exists (i.e.- empty cell). It isn't as easy as deleting a row that has an empty cell in one column but rather if there are empty ...

**0**

votes

**1**answer

34 views

### Excel 2010 - VLookup -

I have two identities associated with one unique value in the table. I need to pull both the values in another sheet using vlookup - is that possible.
Here is the example:
Id Owner Name
A1 Owner 1
...

**0**

votes

**0**answers

26 views

### Excel no longer auto update my calculation?

My excel sheets does not auto update my formulas. It did up to a point and now it no longer do. Just checked the settings under file and it is set to auto? Can some please help. Sheet to big to redo.
...

**0**

votes

**1**answer

89 views

### How do I copy multiple rows to another sheet based on selection with checkboxes in Excel?

This is a screen clip of 2 sheets in Excel 2013. What I am trying to do is that if the checkbox corresponding with the row is checked, the text in the row shows up in the second sheet. There will ...

**-1**

votes

**1**answer

34 views

### Excel 2011 for Mac - formula property dropdown list missing

Basically missing the dropdown properties list on Excel 2011 for Mac. How do I enable/show it?
I've already:
ticked the "show function ScreenTips option" under Excel Preferences > View > ...

**2**

votes

**1**answer

51 views

### How to count number of distinct values containing specific text

I'm not sure if I've phrased that question well so I'll elaborate.
I have 160 lines of file numbers, many of which are duplicates. In each file number there is a value that determines the year the ...

**0**

votes

**1**answer

35 views

### IF statement error message in Excel

The following formula is returning a #VALUE! error.
=IF(L$3<$H11,4*5,(IF("<0",0,(5-(L$3-$H11))*4)))
Why is it returning an error? I know that the error is coming from somewhere in the second ...

**0**

votes

**1**answer

27 views

### How to add up Column B based on whether Column A is a duplicate value?

I have two columns, one with names and the other filled with '1'. Column A has many duplicates in it - this is intended - what I need is a formula to add the 1s in Column B if the corresponding values ...

**0**

votes

**1**answer

34 views

### Matching and returning values to populate column

I have a workbook (Excel 2007) with two sheets. Sheet1 has more than 8,000 employees and their associated unit numbers, which is a 6-digit ID. I am trying to populate Sheet1!C:C with a corresponding ...

**45**

votes

**2**answers

3k views

### What does “A1:A4^{1,2,3}” mean in an Excel formula?

What does the caret operand in Excel do to a range when it is followed by some other numbers in brackets? For example lets say we have the following table:
A B C
1 1.5 0 0
2 ...

**0**

votes

**1**answer

30 views

### Format row that is different than the preceding VISIBLE row

I have an Excel sheet as a database, where each row has a category, and are always sorted by category.
Category Item
1 AAA jjj
2 AAA kkk
3 AAA lll
4 ...

**0**

votes

**4**answers

123 views

### Excel function determine if a cell is visible

In conditional formatting I want to detect if the row above the current cell is hidden or visible. How can I detect if a cell is visible or not?
The only hack I can think of is:
Create a column ...

**0**

votes

**1**answer

16 views

### Find a specific name in a column, bring in the value in the corresponding row, in the column I designate in another worksheet

So I have a worksheet that people will go into and put their name down to sign up for a meeting for a specific date. In a separate sheet, I have their names and I manually input the days they signed ...

**0**

votes

**1**answer

30 views

### Automated Excel Sheet Import from Downloaded Spreadsheet

At work we have a data storage system where we keep information about equipment we've ordered. I want to take select columns from this data and put them in another sheet that is already formatted with ...

**2**

votes

**1**answer

30 views

### Excel Filter and join between multiple tables

I'm reasonable user of Excel (definitely not very skilled though) - I'm using Excel 2013.
I'd like some assistance on how to achieve what I need on table controls. I've created three tables.
...

**0**

votes

**1**answer

92 views

### SUM IF FREQUENCY MATCH

I'm trying to add another criteria for the below formula. On the Payroll Data tab I have column H that has hours in it, some of those hours may be negative so I don't it to count those rows with ...

**1**

vote

**2**answers

43 views

### Excel Convert the correct Date type

I'm currently following the date type dd/mm/yyyy i need to have another row showing the month in mmm type. E.g. From 13/09/2014 to SEP
My formula is =Text(A2, "mmm") which is the correct formula,
...

**0**

votes

**0**answers

49 views

### Prevent Excel from changing formula of last row of table when adding a new row

I have a table with columns named "date", "cost" and "daily". "Date" is a date, "cost" is a number and "daily" is a formula calculating the total cost of each day. The formula looks like this:
...

**1**

vote

**2**answers

65 views

### Excel '10 Formula to find & sum a row based on the intersection of 2 data points. (ie. Name(row), Month(column)

I need an Excel 2010 array formula that will take 2 given data points, say a "Name" (row) and "Month" (column), and return the sum of a row based upon the intersection of these two points within a ...

**0**

votes

**4**answers

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

**2**

votes

**2**answers

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

**2**

votes

**2**answers

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

**0**

votes

**1**answer

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

**0**

votes

**1**answer

425 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

**1**answer

43 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

**1**answer

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

**0**

votes

**0**answers

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

**0**

votes

**2**answers

50 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

**1**answer

69 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

25 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

**2**answers

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

**1**

vote

**1**answer

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

**0**

votes

**2**answers

70 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

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

**2**

votes

**1**answer

107 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

41 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

52 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

23 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

48 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?

**0**

votes

**1**answer

48 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

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

**0**

votes

**1**answer

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

**0**

votes

**1**answer

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