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

learn more… | top users | synonyms (5)

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