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

learn more… | top users | synonyms (3)

0
votes
1answer
50 views

Excel count all rows that contain certain values for certain fields

I have a sheet with a couple hundred rows. Each row has an 'owner' and a pass/fail field. I am making a second sheet that has statistics for the users. So for each user it will count the number of ...
0
votes
1answer
43 views

Summing of adjacent cells in a single column that are <>0

I have a list of numbers (8780 values in one column -- a sample is posted below) some of them are <> 0 and some of them are 0. List of numbers: 0 0 0 0 0,127227198 0 0 ...
0
votes
2answers
37 views

How to put conditional formatting in a cell from another excel worksheet [closed]

It's hard to describe what I want, I'm not really familiar with Excel condition terms but it is basically just like this: Worksheet= Dr. castro patient consultations A B C D E ...
0
votes
0answers
45 views

“Filter by Selected Cell's Color” is so slow

I have a row includes like 60-70 thousand lines. 30.000 of them colored. And I'm trying to filter them by "Filter by Selected Cell's Color" but it's so slow. It take forever to filter just 70.000 ...
0
votes
1answer
46 views

How to CONCATENATE with variable rows

Sorry in advance if I don't explain this very well, definite newbie here when it comes to spreadsheets. I have a Text only spreadsheet with 4 columns, first 3 are Student's Last name, First name, and ...
0
votes
1answer
110 views

Can Excel's Find formula have multiple find_text criteria?

I have a list of names on a NAMES tab range A1:A100. On another tab 'Data Export', in column A I have an export of thousands of comments where one of those names from the NAME tab can be found within ...
0
votes
1answer
41 views

Apply a function/formula to a range in excel

I have many operations to perform on a set of data in excel. The data is in columns, and the functions are a lot of variations on things like =(A1*C1/B1)+(A2*C2/B2)+... Right now they are spelled ...
0
votes
2answers
40 views

vlookup with blank look up value

I have a Vlookup running and I want it to return 0 if the look up value is empty. example: =(VLOOKUP(A6,F:G,2,0)+1)*C6 +-------+--------+-------+-----------+-------+-------+ | | | ...
0
votes
1answer
57 views

Can't compare a cell to a column in excel

I'm trying to compare a column to a cell in excel several times. The columns are copy pasted from a PDF (only way to get the information). The results are bad, with trailing spaces, some completely ...
0
votes
0answers
34 views

Excel changes formula to numbers randomly

I have had recently a number of instances where Excel has changed formula to the numerical results of the formula : Formula Formula Formula Formula Number Formula Formula Formula I have ...
1
vote
2answers
63 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
71 views

Return the maximum value to the left of the cells containing a given string

For an Excel spreadsheet, I am trying to find the maximum number that corresponds to a cell with a certain string. For instance, if the data is: | A | B | 1 | 3 | Red | 2 | 5 | Blue | 3 | 1 ...
0
votes
1answer
104 views

Count instances of a letter in a row if a certain cell in that row contains a specified date

I have a spreadsheet containing a column of dates (week commencing) with columns of data to the right of it, and a conditional formatting rule that highlights this week's row based on the date. I ...
0
votes
1answer
24 views

Merge 2 sheets, always keeping the value from sheet 1 if there are duplicates?

I have 2 Excel sheets that I'd like to merge. They are an inventory file and are roughly 5,500 rows each, with each row being a product and each column being an attribute. The first column is the ...
1
vote
1answer
211 views

Excel 2010 VBA only affecting last row in range

I have some VBA code written in Excel 2010 to force padding around the rows in a table, but part of the code only affects the last row in the range. Here's the full code: Private Sub ...
-2
votes
1answer
30 views

I want to have one cell that can result in 3 different values based on it's own value

I am attempting to solve for a business problem via an excel document. In this document, I am trying to solve for: "Within a cell, If the value = Option 1, then Answer 1. If the value = Option 2, ...
2
votes
2answers
31 views

Looking up last two entries in a column

This might be impossible but I hope not. I am trying to pull the last two entries from a column and add them together and then divide by two to get a percentage. These entries are updated daily so the ...
0
votes
1answer
85 views

Excel - find and remove text, surrounded by varying characters

I've got an Excel file with ~5,500 rows of inventory items. One cell in each row is a description field and contains HTML. Some (but not all) of these cells contain a link formatted something like ...
0
votes
1answer
69 views

Use INDEX and MATCH function to find value and get a corresponding value from the returned rowindex

I want to combine columns [country code] and [admin1 code] with a "." in between and then lookup that value in Sheet1 in the [admin1code] column. When a match is found I want to get the corresponding ...
0
votes
1answer
44 views

How to have excel paste a value in a specific cell according to another data table

I'm working with excel 2010, I have an excel sheet with two tables. The primary table contains a calender and each calender day has a specific numeric value in the cell below the date. The numeric ...
0
votes
2answers
50 views

When to use “0” and when to use “1”

What is difference from 0 to 1 in the following formulae? Example: =IF(ISERROR(MATCH(B2,A:A,0)),"No Match","Match") =IF(ISERROR(MATCH(B2,A:A,1)),"No Match","Match")
2
votes
1answer
42 views

When copying formulas from one workbook to another, how to make the formula to use the data from the new workbook?

OK, I know it sounds complicated, so I will try and elaborate: I have a lot of files (one created every day) - let's call them W1, W2... Every file has the same number of sheets and their names are ...
0
votes
1answer
71 views

Share cost of activities between participants in spreadsheet

Say you have a spreadsheet you use to fairly share the cost of activities between people (example google doc): A B C D E 1 Activity Cost User1 User2 User3 2 1 ...
2
votes
1answer
126 views

Horizontal Index Match Excel 2007

i have a list of customers rows 1-500 column B, with their purchases showing in columns u-AB and based on the maximum purchases done under a specific column (u-ab), the customer will be tagged as ...
0
votes
2answers
170 views

Excel 2010 formula, How do I write this formula? (VBA)

I need a bit of help from the more experienced Excel VBA users. Basically I have 2 groups of 3 cells (6 cells total) If one group of 3 cells is filled in it needs to accept it and not return a ...
1
vote
2answers
30 views

Testing a cell for multiple condiction before calculating value

I am working on a workbook that has a formula =IF(ISTEXT(G4),0,IF(ABS((G4/H4)-1)>=0.2,0,1)) Now if H4 has a value of zero I get the Excel error #DIV/0!. I want to use the ISERROR formula to ...
0
votes
1answer
23 views

Removing whitespaces from data in a column

I have two columns of data in excel spreadsheet say column A and B. There are more data in column A than in column B. I am trying to find which of the data in column A is not present in column B. I ...
1
vote
2answers
675 views

How do you make a set of cascading dropdown lists in excel?

I'm working on a system to try to take inventory more quickly instead of filling in each row by hand. The reference data is several hundred rows. The first 3 columns in the reference sheet are Class ...
3
votes
2answers
65 views

Why is this Excel formula returning two different values?

I am attempting to use Excel (2013) to look up whether the value 1 turns up in a given set (using the CHOOSE() function, then, if so, printing the value in the cell directly to the right of it using ...
0
votes
2answers
58 views

How can I return a list of specific words found in a cell's contents?

I have two columns, one with a string of text (A1), the other blank (A2). A1 looks something like this: "Briefs on Southern Africa, Rhodesia, East of Suez (Including Soviet Naval Expansion), ...
-1
votes
1answer
34 views

Conditional Formatting from Helloh, for a Noob

I'm going to try my best to explain what I need to figure out. Also, for you to understand exactly how mind bending this is for me, I haven't used an Excel formula since 2005/2006ish. We're doing a ...
0
votes
1answer
140 views

Advanced filtering leaving on unique values and removing duplicates

On the spreadsheet there are two subsets of data that are separated by color based on where I pulled the information from. Within these subsets are duplicate entries. I do not want to simply remove ...
0
votes
0answers
9 views

Pointing to another excel worksheet in a 3d table dynamicaly

I have a 3d table for some stocks of products, all sheets identical and all. If product name is "OIM", the worksheet the data is on is called "ROIM" and so on. Now, I would like to find a dynamic ...
0
votes
1answer
168 views

Excel - Script that search a sheet for a cell containing specific text, and replace the text with a specific value

I want to split a cell with data into other cells based on the data in the original cell. The data from the original cell looks like this (all content in a single cell): ...
1
vote
1answer
25 views

Replicate a Calculation Sheet for each customer

I created a LibreOffice sheet, with the detailed list of expenses. And a second list with the names and debts of each neighbour. However, I'd like to generate a personalise replica of the first ...
0
votes
2answers
77 views

How do I refer to a cell in another worksheet with a long name?

As explained in this other Answer, SheetName!A1 = cell A1 in the worksheet called "SheetName" (without quotations). Say the name for my worksheet is very long. Without shortening "SheetName" and ...
0
votes
3answers
1k views

Formula =IF(VLOOKUP(…)=Cell;…) does not work [closed]

According to this and my own testing, using VLOOKUP can be compared to a specific value inside of IF. E. g.: = IF(VLOOKUP(...) = 20, [then value], [else value]) works. But if I compare the result ...
2
votes
1answer
117 views

Excel/Libreoffice returning TRUE for IF() instead of expression

See the following situation: For some Here cells A1:E3 have plain text data (no formulas), and C4:E4 have the same formula (i.e. typed in one cell and then autofilled to another ones). I wanted ...
2
votes
2answers
586 views

How to add or subtract to, or increment, column letters in Excel?

I'm looking for a simple versatile formula that allows simple mathematics to be done to column positions, returning the appropriate column letter code. For example: Given column A, set to +1, it ...
1
vote
1answer
51 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?
1
vote
0answers
76 views

Using getpivot in excel to create flexible reports based on user inputs

I am trying to make a report in Excel that sits on a very large set of data (set up in proper pivot form). I want to be able to select a value for a few fields (or select a value from 1 field but ...
0
votes
0answers
30 views

incorrect IF formula evaluation

I have the following formula: =IF(SELL_INVESTMENT="YES",1,2) It returns not 1 or 2 but 0. I verified that the cell contains YES and it should return 1. The file contains over 1000 cells. "Trace ...
0
votes
2answers
44 views

Indirect turns up with #ref error

I want to use INDIRECT in a formula like =INDIRECT(CONCATENATE(A1,"+",A2),0); where A1 = 7 and A2 = 8. It comes out with #REF error all the time, but if I use the INDIRECT formula like this: ...
1
vote
1answer
74 views

IF statement with multiple answers

I want to make a table and c4 will be radius required and the minimum weight for that radius requirement will be in D4 The chart is; radius quantity 0,040 - 0,06 : 33 0,061 - 0,10 : ...
0
votes
0answers
91 views

Getting data from closed files with concatenate formula

Each day a program is creating an excel file for me with some data for the current day. Like what is the price for products, how many people are available today and things like that. Based on all ...
1
vote
1answer
57 views

Conditionnal Formatting with Dynamic Custom Formatted Date

My problem is that I want to color in red a list Custom Formatted Dates when the date is overdue. For instance, I got two actions: Buy Bread : Must be finished before 27/01 Buy Milk : Must be ...
1
vote
1answer
27 views

How to connect a date with data on a second tab in excel

I am wanting to know how to connect data with a date on another tab. on the first tab I have the date using =TODAY() in cell A1, then in cell A3 I have Passenger numbers.. On the second tab I have ...
3
votes
2answers
49 views

Define value of a column by another column in Excel

This seems like it should be so simple! I need to define column B by column A If column A is equal to any value in the array "taco,burger,spaghetti" column B should read "food", if column A is equal ...
0
votes
1answer
329 views

Updating another Excel spreadsheet from current spreadsheet

I used the VBA code in "How do you write an Excel formula that will paste a specific value in a different cell?" posted here in May 2013 and it works well to populate/fill another cell in the same ...
0
votes
0answers
12 views

Generating ID#'s in GIS attribute table

In the attribute table within GIS, I have a certain column with ascending values, and I want to assign ID numbers (for easier readability and display) to a corresponding column. So for instance: Feet ...