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

learn more… | top users | synonyms (5)

1
vote
1answer
25 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
26 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
12 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 ...
-1
votes
0answers
15 views

Displaying data from another tab

I run a daily report where I need to display errors on a roster of our associates. I have one tab ('Roster') with the complete roster and a formula that checks to make sure that the shifts are correct ...
0
votes
1answer
21 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 ...
34
votes
2answers
2k 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
21 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
3answers
27 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
0answers
10 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
17 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
21 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
39 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
34 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
24 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
31 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
36 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
0answers
16 views

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

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
28 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
144 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
17 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
45 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
0answers
24 views

Excel Index match formula [closed]

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
34 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
14 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
21 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
12 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 ...
0
votes
2answers
40 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
67 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
2answers
23 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
32 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
33 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 ...
2
votes
1answer
53 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
35 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
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
19 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?
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
24 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
21 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
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") ...
0
votes
2answers
29 views

How to parse file datetime in Excel

I am trying to convert a text string like this - Dec 15 21:44 into a date/time format in excel (Polish version). I tried =DATEVALUE function but without success.
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 ...
2
votes
1answer
47 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 ...
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
47 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 ...
0
votes
1answer
26 views

Excel IF Statement identify when cell has contents [duplicate]

How do I tell MS Excel to use the contents from one cell if data exists (this data can be positive negative or zero). If the data does not exist in said cell, use contents from another cell?
0
votes
1answer
16 views

Combine GoogleSheets functions

I'm trying to combine multiple functions into one formula in GoogleSheets spreadsheet. I've tried learning more from Google's Help center and through YouTube but didn't came to satisfactory result. ...