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

**0**

votes

**1**answer

808 views

### Counting values in a column if a certain value exists in another column

I'm trying to create a Summary tab in my spreadsheet that will summarize info from another tab. Primarily, the parent tab is a list of tasks, status of the tasks (open or complete), and the persons ...

**7**

votes

**6**answers

20k views

### Using excel, how can I count the number of cells in a column containing the text “true” or “false”?

I have a spreadsheet that has a column of cells where each cell contains a single word. I would like to count the occurrences of some words. I can use the COUNTIF function for most words, but if the ...

**-1**

votes

**1**answer

1k views

### How to make this formula to work in Excel 2007?

How would one make this formula =MAX(LEN(A2:A9)) work in Excel 2007?
I want the value of the longest field in A2:A9 range.
I found this link: http://www.mrexcel.com/td0021.html, but it's old now.

**0**

votes

**1**answer

120 views

### How can I import a TAB delimited file with double quote text qualifier into Excel if a cell value already contains a double quote?

I am trying to import a TAB delimited file with the following format into Excel 2013
"Column A" "Column B"
"tjc0056421151r" "I\gs]hü"nsf^f\F*lqog\düûWp"
"tjc0058088269r" ...

**0**

votes

**1**answer

18 views

### Calculating non-duplicates

I have below data in three columns.
A B C
07002 72J217.4 72J217
07002 72J229.5 72J129
07002 72J229.6 72J129
07002 72J229.7 72J129
07006 61E001.5 61E001
...

**-1**

votes

**1**answer

98 views

### How to insert a button in MS Excel?

Friends I am using MS Excel 2010 and I wish to insert a button in Excel sheet whose name should be ‘1 ADDER’ and I want that whenever I press this button, it should insert 1 in the cell of column A ...

**0**

votes

**1**answer

24 views

### “lastcell” issue with formula in complete column in Excel

I'm currently using a formula throughout a column in an Excel 2010 worksheet. It works fine, but I'm not really using the entire worksheet, I only don't want to have to worry about adding the formula ...

**1**

vote

**2**answers

79 views

### Excel - Find ID-related Value and fill in another workbook

I'm working in a small hospital in India having following problem:
For a special drug we're having a list with DATE,ID,DOSE,DURATION,NEXT VISIT which we us as a daily working list (workbook 1), means ...

**-1**

votes

**1**answer

78 views

### Extracting values from an excel table to build a graph

I have the risk register shown as an excel table. I want to extract the entry corresponding to a specific KRA value and plot its maximum on a graph. I need the method to be automatic so that any ...

**0**

votes

**1**answer

1k views

### Line chart from multiple tables in Excel 2013

I have two sheets, "Graphs" and "Data".
In "Graphs" is a line chart with a single line that gets X and Y values from tables in "Data". I want the chart to be updated automatically when I add ...

**0**

votes

**2**answers

40 views

### Excel 2010 formula to delete repeated content based on value (instead of second highest the lowest)

I've read a Question topic about picking the second highest value. I need the same but a bit different:
Excel 2010 formula to delete repeated content based on value
Maybe it is a bit rude, but I ...

**0**

votes

**1**answer

65 views

### Find multiple cells with the same value in col A then search col C for a particular number

Say I have multiple cells in column A with the same value (e.g the number 22). I want to identify those rows then search for the existence of a particular number in column B (e.g. the number 10); ...

**1**

vote

**3**answers

57 views

### Insert columns and the formula adjusts with it

Lets say I have a number in cell A1 and a number in B1 and the forumla "=B1-A1" in C1.
Now I insert a column to put a new number into the newly made C1 cell and the formula has moved across to D1 but ...

**0**

votes

**1**answer

331 views

### Conditional formatting in Excel 2013 based on inequality between two cells

This is part of a problem for college for which I can't find a solution.
Use conditional formatting to format, in blue and bold, the value in column 8 for which the value in column 5 is greater ...

**0**

votes

**1**answer

24k views

### Excel Multiple Cells Included in an IF Formula

I am trying to compare 6 cells, and get a pass or fail at the end, but in Excel it looks like it will only let me have 5 cells. I'm not sure how to word it.
like for example:
Pseudocode: if (A1 and ...

**0**

votes

**2**answers

68 views

### Excel - Dynamic Cell Update using multiple criterias

I would like to know if there is a way in Excel to create a cell that will display a value using a formula that will calculate the latest entry by date and campaign.
If I have 3 columns DATE | ...

**0**

votes

**3**answers

23k views

### Comparing columns in two different excel sheets and if they match copying third column

I have limited working knowledge of Excel and I hope someone will be able to help me with my problem.
I have two different work sheets (say F1 and F2) with last name in Column A, first name in Column ...

**1**

vote

**1**answer

6k views

### Prevent Excel 2010 user from pasting formatting into cell

Is there a way to lock a cell so that a user can copy/paste a value into it, but not copy formatting to it? Locking the worksheet doesn't prevent the user from changing the formatting of the cell by ...

**0**

votes

**1**answer

100 views

### How to shift the data from one cell to another cell in excel?

Friends I am using MS Excel 2010 and I want As I type any value or word in a1 cell, it should automatically be transferred into d1 cell and a1 cell should get blank again. Similarly if i type any ...

**0**

votes

**1**answer

682 views

### Is there a limit on the number of rows a sum function can calculate in Excel 2007?

I am trying to sum a column of 50938 values in Excel 2007 but keep receiving a "#VALUE!" error. The cells in the column do contain formulas. I also tried to sum the column after copying and pasting ...

**0**

votes

**1**answer

588 views

### How to highlight a cell when I double click on another cell?

Friends I am using MS Excel 2010 and I want when I double click on a cell say a1 it should highlight c1 cell. Similarly when I double click on a2 cell it should highlight c2 cell. It is possible by ...

**0**

votes

**1**answer

86 views

### How to automatic transfer the values in another cell?

Friends I am using MS Excel 2010 and I want As I type any value or word in a1 cell, it should automatically be transferred into d1 cell and a1 cell should get blank again. Please suggest me any way, ...

**0**

votes

**4**answers

5k 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

33 views

### SUMPRODUCT where the product is computed from the row

I have a range AG6:AS38. It looks like this:
AG AH AI AJ AK AL AM AN AO AP AQ AR AS
--------------------------------------------------------
6 | 1 A B C D ...
7 ...

**2**

votes

**1**answer

14 views

### Excel Autofill Linegraph with Time

I must admit that I am not the expert on excel, but I need help creating a graph with some data. In column A, I have over 500 cells filled with temperature values from a probe. Now, technically each ...

**0**

votes

**2**answers

45 views

### Excel formula to identify and group figures

I have two columns A and B
In column A, I have figures in some currency say US Dollars. In column B, I have account codes to which the figures should be grouped.
A B
USd Codes
2,500 ...

**0**

votes

**2**answers

172 views

### How to compare two columns with other two columns for duplicates in Excel

I need to compare columns C and D with A and B and only if exist same combination of two cells in one row. Hope the image below will explain the situation better.

**0**

votes

**0**answers

26 views

### How can I identify filtered columns? [duplicate]

My file has many columns.
its difficult to know whether there are filters or not and I have to scroll to the right to verify each and every column.
Is there a formula to put in a cell indicating ...

**0**

votes

**1**answer

3k views

### In excel how to do a 'sumif' function that is dynamic to a changing column

I'm trying to use a formula to do a sumif of a column of monthly data that changes depending on the current month. For example say I am entering this formula in column A of tab 2. Since it is ...

**0**

votes

**2**answers

376 views

### How do you get excel to ignore zero or blank values in an Average?

I am making a spreadsheet to score recipe ingredients, averaging scores for quality of the ingredient, packaging, and accuracy of serving size, and then using that data to create an average for the ...

**0**

votes

**0**answers

61 views

### SUMPRODUCT & ROUND

Is there a way to get the product of the array with conditions to round to the 2nd decimal place before summing?
Current formula w/o rounding:
...

**0**

votes

**2**answers

52 views

### How can I prevent changes to formula results after first use?

We have an Excel 2013 workbook that we use when we create new user accounts (column A for first name, column B for last name, column C for the password, etc.).
First name Last name ...

**0**

votes

**1**answer

119 views

### Excel Month() function does not detect some date format

I have a simple table like this in excel:
The formula i used was =IFERROR(IF(LEN(B3)>0,MONTH(B3),""),""). I check if a cell is blank by using LEN, if so ignore, else, convert it to month no.
I ...

**7**

votes

**5**answers

36k views

### Is there a function in Excel to find the maximum absolute value of a range?

I'm looking for a function in Excel that looks something like
= MAX(ABS(A1:A10))
except ABS() doesn't take a range of numbers.
The best that I can come up with is:
= ...

**1**

vote

**1**answer

23 views

### How to use a variable once, across the equation, in Excel 2007?

okay, is there a way to declare a variable inside a cell, without calling the cell name 3 times, across the equation(?)?
I came up with this, to calculate the age from a DOB entered using the ...

**0**

votes

**1**answer

56 views

### How to create a dynamic date title in excel 2010 using different tables and data

My friend has asked for help with an excel assignment and they have 2 tables, HRM and Data_Tables.
Data_Tables has corresponding month numbers from 1-12 in column I from I3-I14. In J column there is ...

**3**

votes

**3**answers

2k views

### Convert a formula in Excel into an Array Formula without Ctrl-Shift-Enter?

Question: How might it be possible in Excel to convert a formula into an array formula, without using a hotkey?
Normal Formula
Array Formula

**0**

votes

**1**answer

78 views

### Simple sum of dropbox values for scoring Excel 2013 worksheet

I would like to make a worksheet with dropboxes for scoring ingredients and summing the scores per ingredient. I already made the dropboxes with number 1-3(1=poor, 2=fair, and 3=good), but I would ...

**0**

votes

**2**answers

4k views

### Check if a cell contains any of words from a dynamic list

I want to use a formula to populate the "RESULTS" cells. I need it to check if the adjacent "CHECKED CELL" contains any of the words listed in the "KEYWORDS" cells.
Ideally, I would be able to add ...

**-2**

votes

**1**answer

48 views

### Add a cell content to another cell if a different cell has a specific content

How can I fill a cells value (e.g row 29 col A) with =sum(B1:Z1) but then only add the content of a cell in that row 29 if row 4 of that column says "UK IR". That way I can add all the cells together ...

**0**

votes

**1**answer

212 views

### Attendance Tracker - Deduct points if no points within pay period

I have a spreadsheet where I keep track of whether or not someone called in, was late or left early. On the 1st sheet I have their names, the number of times they've called in, were late or left ...

**0**

votes

**1**answer

15 views

### Hyperlinking or linking in another way to a cell without it being able to change

How do I hyperlink or link to a cell from another cell in a way that when rows are added, and the row numbers keeps changing due to new rows of information being added throughout the sheet, the link ...

**1**

vote

**1**answer

166 views

### Sumifs function where I want to sum a range not just a single row or column

I am stuck.
I am entering timesheet date into an excel sheet:
Column A - Resource Name
Column B - Project Phase
Column C - Resource Team...
Column D to AAA is a date
Therefore a single resource could ...

**1**

vote

**1**answer

270 views

### How can I replace forbidden characters in a file names before saving in VBA?

This will be very simple for somebody who knows VBA better than me. I used a code original provided by user Nixda on this forum to split excel worksheet into multiple CSV files based on column value ...

**0**

votes

**2**answers

68 views

### Excel: Validation rule with value that should be in the list of another column without blank

On excel, I've two tabs: one listing all people participating to an event, with a lot of details. I've a "FullName" column which is basically defined by:
=[@LastName]&" "&[@FirstName]
This ...

**0**

votes

**1**answer

102 views

### Multiple Formulas in one Cell for hotel

I am trying to create a housekeeping spreadsheet where when a room number is entered into a cell a 0 or 1 (0 is for studio and 1 is for a 1 bedroom) is automatically populated in another cell. I have ...

**1**

vote

**1**answer

89 views

### How to count months in intersection of two date ranges [closed]

I need to calculate the number of months in the intersection of two date ranges.
For instance, in Column A I have start dates of employment
12/1/1998, 1/1/2003, 2/1/1995
and in column B I have ...

**0**

votes

**1**answer

784 views

### MS Excel - Cumulative sum of a column based on “id values” in another column?

I have been trying to figure out how to make a formula that calculates cumulative sums of rows in a column based on their id values in another column but still haven't found any solution for it. I try ...

**0**

votes

**1**answer

50 views

### Grab cell data and use in “IF” statement

Is it possible to grab a cell containing a date and paste that date in an "IF" statement containing text, similar to the below:
...

**-1**

votes

**1**answer

102 views

### Excel 2010: How do I reference a specific field using a defined column name and a row number?

I'm using Excel 2010 and am creating a workbook to define details about products in a store (including category, price, accessories). I also want to define default category details to use if the ...