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

learn more… | top users | synonyms (3)

1
vote
1answer
16 views

How do I allocate different text cells in a row a number and then sum total that row

I have a spreadsheet setup to monitor absence in the office. The columns are the days of the month 1 - 31 and rows are the month/employees Mon Tue Wed Thu Fri January 1 2 3 4 ...
1
vote
1answer
2k 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 ...
13
votes
5answers
42k views

How to create a formula for every row in a column in Google SpreadSheet?

I use three columns. A, B and C. In column C I have a formula every row =A1*(1.6*B1) and then for the next row I have =A2*(1.6*B2) in C2. How can I do so I don't have to type in the new formula in ...
0
votes
1answer
16 views

How to automatically delete rows that contain certain numbers in the ID column

I have a column of numbers in another workbook and I would like to use that set of numbers to delete rows in the main workbook. I will give an simplified example of what I'm talking about below. My ...
0
votes
1answer
2k 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
0answers
9 views

Deleting rows that contain a keyword in the column

I have an excel file that has a id as one of the columns, each id has multiple entries and follow a set of survey questions. I need to delete all of the rows that have that keyword (which is in a ...
0
votes
1answer
23 views

Excel Formula Issue Countif to Sumproduct

Having a nightmare at the moment with some formula's I've been asked to 'fix'. It would seem the users expected them to work with closed source spreadsheets but they don't. So they end up having to ...
-1
votes
0answers
19 views

comparing 2 columns in excel and return the same result in a group

hi m new in excel function, i have a problem like this. i have 2 lists of data DATA I NAME SCORE ----- ----- ANA 10 BUDI 20 TINA 30 DATA II NAME SCORE ----- ...
0
votes
1answer
21 views

Embedded IF(OR(IF) statement

I have made a formula that basically looks for a string of text inside a cell and if it finds it puts it in another cell. I needed to check three cells so I did three separate formulas (because I'm ...
-1
votes
1answer
22 views

Excel formula: identifying highest weights

I need some help with the following assignment: Task: In the product Weight tab, you can see each product is weighted with a value per customer. Please create a Product Code for each customer based ...
0
votes
0answers
8 views

summing values associated with names in multiple columns

I'm trying to sum up total values with names. There are 8 total columns rotating between a list of names and then values. I believe a sum if function would work, but I'm too novice to find the right ...
2
votes
4answers
8k views

How to add/subtract months from given date in Excel

Here is my scenario: I have to populate list with dates and some metadata of upcoming events for the following year in my Excel application. Some of the events are occurring on the same day each ...
0
votes
1answer
17 views

Excel 2010 Formula of 3 variables with 3 results

I haven't been able to figure out how to combine these logic & date formulas in 1 cell. My parameters would be: A2 will always have a manually entered date in it B2 could have one of three ...
0
votes
1answer
22 views

Excel: portable formula to reference data on previous (relative) worksheet

I need to create a fairly simple Excel spreadsheet (workbook). I need 12 sheets (tabs), one for each month. Each sheet references cells in the previous sheet. For example, the "May" sheet is ...
0
votes
0answers
13 views

Convert COUNTIFS formula to SUM(IF) to enable external workbook references?

I need to convert the following formula to one that uses SUM(IF), as I am given a #value error when the external data source is closed. ...
2
votes
1answer
34 views

Replace cell references with values without F9 using macro

This is an Excel macro question. Given a series of formulas such as the following: = A1 + A2 = B2 + B2 etc., ... I would like to replace each of these cell references with the values without ...
0
votes
1answer
29 views

How to I use SUMIF or SUMIFS to automatically add the values of different cells when they meet they meet multiple criteria

Basically I have a spreadsheet with three criteria columns. I would like to be able to recognize duplicate HTS codes that also have the same country of origin and add the sum of those values, which ...
1
vote
3answers
5k views

Find a non-case-sensitive text string within a range of cells

I've got a bit of a problem to solve in Excel, and I'm not quite sure how to go about doing it. I've done a few searches online, and haven't really found any formulas that seem to be useful. Here's ...
0
votes
1answer
25 views

How do I merge data from other excel files to one master excel worksheet?

I have an uniform ID system throughout all the files, except with multiple entries and differing data named columns. I would like to create a master excel sheet with specific data columns from each ...
17
votes
7answers
69k views

How do I get the day name into a cell in Excel?

Given a date, how do I get the day of the week (e.g. "Monday") into a cell in Excel?
1
vote
2answers
54 views

Excel compare cells in columns A and B, show in column C cells only unique to column A

I know there's a formula for this in Excel but I can't get it right. I've got a list of 7000+ names in column A, and a list of 4000+ names in column B. Some are duplicates, appearing on both column A ...
0
votes
1answer
34 views

Hide columns based on content (sum of rows)

I'm trying to hide columns based on the sum of selected rows (i.e., cells) in those columns. For example - if the sum of E9:E12 is 0, then hide column E. Additionally, and probably a little harder - ...
1
vote
3answers
46 views

Is there a quicker way to enter formulas?

In the firm I currently work in we have a very inefficient and time-consuming process for entering formulas. For example: Y1=CHOOSE(B3,'[Flacflow balancesflacflow.xlsx]Data'!$A$77, '[Flacflow ...
2
votes
2answers
21 views

dragging to populate cells in Excel

I am aware that you can fill the cells in Excel as a series by dragging the square in the bottom right corner. for example typing "1" in Cell "A1" and dragging the box down to "A10" will give you a ...
0
votes
1answer
17 views

excel - inserting a value based on a range of values

I need to link a batch code to a specific unit number. The batch codes are the same for a range of unit numbers. The first table below shows the unit numbers; the ā€œ?ā€ marks are where I need to enter ...
0
votes
2answers
43 views

Selectively find duplicates across columns

Column A has the product ID. There are 102 other columns with other data. Column A will be unique unless some data was changed. In that case, there will be 2 rows with 99% of the columns are ...
2
votes
1answer
3k views

Lookups targeting merged cells - only returning value for first row

I have a master worksheet which contains data that I wish to link to another 'summary' sheet using a lookup. However, some of the cells whose data I wish to include in the summary sheet are merged ...
0
votes
1answer
34 views

Double Filter in Excel

I'm trying to "stack" filters in excel, so to speak. I want to filter column A to show anything greater than 30 and then I want to filter column B to show the top ten items. When I do this, however, ...
0
votes
3answers
1k views

Sum Column, but only Filtered Rows

I have a large flat file of a bunch of ungrouped primary key values. I want to filter on a key and populate cell A1 with the value selected from the dropdown list. In the end, I want to see a sum of ...
0
votes
2answers
386 views

Excel Worksheet Function: How to refer to a dynamic column?

Note: Trying without VBA Let's say I have in C1 the word "Hello" From another cell, lets say A1, I want to return C:C, because in the first row 1:1, contains the word "Hello" In A1, I could put: ...
0
votes
1answer
26 views

Get the index of selected value in Excel drop down

I have a drop down list generated in Excel from row values. I want to select from that drop down, get the index of that value in the drop down array, then use that for an offset value to select the ...
1
vote
0answers
27 views

Excel: Keeping references/links when transposing

I have a worksheet in where I have table (let's call it the summary table) with references to a another table (among others) which is 2x40 (rows x columns). I would like to tranpose this into a 40x2 ...
0
votes
3answers
21 views

Is there a way to combine duplicates within a large range in Excel?

I have a large (1.7 million rows) table of sales data on a sheet in Excel, many entries of which are identical other than a unique ID and a timestamp. Iā€™d like to consolidate this data by combining ...
0
votes
1answer
20 views

Copy the content of one cell, and insert within 2 characters, in the middle of a text content from another cell (Excel)

I am trying to batch copy the content of the cells in column A, and insert it within the "><" of the text in the column B. The result should look like the output in column C. Here is an example: I ...
0
votes
1answer
18 views

Hide sheets in shared excel workbook.

I have a shared workbook on which I have hidden some of the worksheets prior to saving. I have closed the workbook. If another user opens the workbook the worksheets I had hidden are visible. If I ...
1
vote
0answers
21 views

Naming worksheets automatically [closed]

I have a spreadsheet that I input data into everyday of every month; each worksheet represents one day of the current month. I have all the formulas I need but each month I have to change the ...
0
votes
0answers
25 views

Return value from table using Multi-dependent drop-down list

I am trying to obtain the value of a column header (or topmost row value for a specific column) based on the selection of properties from 2 drop-down lists. ...
-1
votes
0answers
26 views

Excel “NETWORKDAYS” formula

So I'm trying to compare 2 columns of dates in excel using the "NETWORKDAYS" formula but i have quite a bit of dates to compare. if someone could walk me through how to make it so column "C" shows ...
7
votes
3answers
14k views

Excel function that evaluates a string as if it were a formula?

Suppose I have a text string like "11+5" or even "=11+5" stored in a cell. Is there a function in Excel that will allow me to actually evaluate that string as if it were a formula? This would be ...
-1
votes
1answer
16 views

Excel searching column and copying the cell next to it

i need to find the value from column C in the row B, and copy the value from found result in column D. If it's possible with formula and to copy formula to all cells in column D ? without VB? Thanks ...
0
votes
1answer
11 views

I am trying to match based on a dynamic cell reference with the match function

=MATCH(B$4,CONCATENATE("'",A5,"'!$E:$E"),0) The above formula result is #VALUE! Cell A5 has the value Sheet1, however, after testing this will change to a date e.g. 01-Jul-14 What am I doing ...
3
votes
1answer
99 views

Excel 2010: Formula for Sum and Return Date

I have a workbook with a Summary sheet and a Data Set sheet with a linked table containing 700k+ records of financial data on investments. (In my particular case, it is real estate properties; but ...
0
votes
1answer
39 views

Excel Formula Challenge

I have a fairly specific Excel problem and I am hoping someone is savvy enough to come up with a working formula for me. I am using Excel 2011 for Mac btw. So, I have a list of about 134 ISBNS, for ...
0
votes
1answer
9 views

Cell to relate to information depending on infrmation in another cell

I hope you can understand what I mean. I have tried to draw below to help explain. I need column d to have the information in column a1 and column h to have the information in a2- Keeping it in the ...
0
votes
1answer
17 views

count number of cells that meet certain criteria between two other cells with certain criteria

I am looking for a way to count the number of cells meeting a specific criteria that are located between two other cells. For example: A B C D E 151867377 01:03.5 ...
0
votes
2answers
26 views

Excel - Formula to get total number of worksheets

I have 100+ sheets, it is hard for me to count them one by one. Is there a one line formula to get the total number of sheets? The CELL or ADDRESS function perhaps? I remember using something like ...
17
votes
9answers
46k views

Formula for updated currency exchange rates in Excel or other spreadsheets?

What kind of spreadsheets support a formula like the following: =exchangeCurrency("USD"; "EUR"; 3000) The result should be 3000 USD exchanged into Euros. Possibly a date could be provided for a ...
0
votes
1answer
40 views

SUMIFS using “greater than or equal to” with text values

Using the following simplified example, I want to calculate the sum where Category="Graduation" and Date >= 1986-05-01. Category Date Count Graduation 1985-06-15 1 Graduation 1985-09-12 2 ...
2
votes
1answer
34 views

How do I convert dates from Open Office format to MS Excel format

I am trying to save a file in Libre Office as an excel file but Excel sees the data displays it as a negative number using a series of ###### symbols. I believe the problem is to do with the two ...
0
votes
1answer
42 views

Pie Radar Chart outline series not working

I have been trying to create a Pie Radar Chart as described in answer to this question. Everything works until I get to the section that describes inserting a series to create the chart's outline: ...