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

**0**

votes

**1**answer

62 views

### Apply one formula to all selected cells

So in Excel 2013 (v15.x), is there still no one-step way to apply an arbitrary formula to all selected cells or am I just missing it? The closest thing I see here on search is the answer to this ...

**0**

votes

**1**answer

20 views

### Excel - Return a (comma seperated), list of values paired to a second value

In the situation whereby I have data like below:
A 22-Aug-14
B 22-Aug-14
C 22-Aug-14
D 22-Aug-14
E 22-Aug-14
F 22-Aug-14
G 22-Aug-14
A 23-Aug-14
B 23-Aug-14
A 24-Aug-14
B ...

**-1**

votes

**1**answer

35 views

### IF/AND Function in Excel returning 0 when conditions are met [closed]

So just like the title says, my formula is returning some of the outputs, but for others, it simply outputs 0, which is the value if false. Perhaps I have too many nested AND functions. Any analysis ...

**0**

votes

**1**answer

66 views

### How do you export an Excel file to CSV with rows have differing number of columns?

I have text file with a header, master, detail, footer record. The types of rows all have a different amount of columns. When I export to CSV, it exports all data and puts in fillers to the longest ...

**0**

votes

**1**answer

31 views

### Excel - Creating UDF from a Formula

I often come across situations where I am writing very similar but verbose formulas that use the same range as an argument for multiple functions.
As a simple example, consider:
=(A+B)/B
Is there ...

**1**

vote

**1**answer

52 views

### Drag a formula, so that it increases by 17 each time

I have the following formulas on the third sheet of my workbook. They are simply cell references to the first sheet; on my 'output' page, I want to display every 17th cell of the 'input' sheet. ...

**-1**

votes

**1**answer

54 views

### how to setup (x) to (x) + 1 ÷ 5 + 3 calculation in excel?

I have been given an equation and also the correct calculation. However trying to set it up in excel is not matching the desired result. I am unable to figure out if i do not understand the equation, ...

**2**

votes

**1**answer

60 views

### Why does Excel Table Format, always use absolute referencing in formulas. Any way to disable it?

The Table format (Home> Format As Table) option is a good feature of Excel. I am using it in Excel 2013.
But when I put a formula and try to copy it across, it does not work. Trouble comes when you ...

**1**

vote

**1**answer

119 views

### How to calculate average bed and wake time?

I have a spreadsheet of two columns. A contains bed time (time I went to bed) and B contains wake time (time I woke up). How could I go about calculating the average of bed time and also wake time?
...

**-2**

votes

**2**answers

163 views

### How to have a value from one Excel sheet propagate to other sheets, while being incremented?

I have an Excel workbook, which has 100 sheets in it, all the sheets are identical apart from A10 in every sheet.
In A10 of sheet 1 there is a value 101 which I typed in manually and A10 of all other ...

**2**

votes

**1**answer

299 views

### Microsoft Excel - Sum drop down boxes with text

Good morning everyone. I require some help with my spreadsheet.
As you will see by the screenshot below, i have added drop down boxes with text that i pick based on different combinations. I then ...

**1**

vote

**2**answers

61 views

### How do I identify duplicate rows in an Excel cell?

I have an excel file with thousands of rows of contact info.
I need to get a list of all the rows that have duplicate first and last names.
I have been able to use conditional formatting to ...

**-5**

votes

**1**answer

75 views

### Formula to find the upper case in the sentence as true [closed]

i have one worksheet which has a sentence in-between uppercase words, i want to know which cells has a uppercase word in the sentence. Please someone help me out with the right formula to find.
I ...

**-5**

votes

**1**answer

29 views

### Deleting some cells in Excel 2013

I have 30,000 contacts on my excel sheet and some have not included their emails. I am a beginner at excel, and would like to know how to delete all cells without emails as well as the corresponding ...

**1**

vote

**1**answer

348 views

### Excel Copy Formulas Horizontally

On Row 1, Column A I have =Sheet!1K2, on Column B I have =Sheet!1K3, Column C =Sheet!1K3 and so on.
On Row 2, Column A I Have =Sheet!1L2, on Column B I have =Sheet!1L3, Column C =Sheet!1L3 and so on.
...

**1**

vote

**1**answer

55 views

### IF formula incorporating sequential answers

I am trying to write an IF-THEN formula that outputs sequential numbers (See the example at the bottom). If you need further clarification on the process, just let me know. Here is what I am trying to ...

**0**

votes

**2**answers

33 views

### Numbering Identical Items in A Column

Suppose I have multiple, repeating names as follows:
John
John
John
John
John
Tom
Tom
Tom
Tom
Tom
Tom
Anna
Anna
Anna
Anna
Anna
Anna
I would like to find a way to tell Excel: while the name is ...

**0**

votes

**1**answer

33 views

### Excel-function IF

Column C calculates the earliest date from column A and B.
What would be the shortest function to display Row A1 or A2 in Column D as per the value of Column C?
Overhaul 1 Overhaul 2 Earliest due ...

**-4**

votes

**2**answers

81 views

### How do I get Excel to look up customer names from another sheet?

I have two sheets in Excel i.e. sheet1 and sheet2.
In sheet1's column A there are account numbers of the customer and in column B there are names of the customers.
Now I want that in column A of ...

**3**

votes

**1**answer

2k views

### How to automatically colour cell background with value selected from custom dropdown list in MS Excel 2010

In MS Excel 2010 Professional is there a way to automatically copy the background colour of the custom dropdown values from somewhere else in a spreadsheet?
I have some Data Validation on a row of ...

**-1**

votes

**1**answer

104 views

### Using Microsoft Excel 2013 to Calculate Work Hours

I am trying to develop a table that will calculate our techs' response times.
In column C is when the call was received and in column F is when the tech responded. Columns D and E are to be ...

**0**

votes

**1**answer

32 views

### Adding Index to Match formula

I have the formula below working correctly, it is returning the max value in column G when the other conditions apply (valumes in B and F). However now I know what the Max number I want to return the ...

**0**

votes

**0**answers

12 views

### Keeping cells in the same row lined up when columns in the same row have 2 different data sources

I am working on a worksheet that references data on a different worksheet (within the same workbook). Each row in the referenced worksheet represents an order. Data from the referenced worksheet ...

**1**

vote

**1**answer

30 views

### Excel return a list of stringe where 'TRUE'

Ok so I have couple of cells listing warnings:
A B C
1 Warning1 TRUE
2 Warning2 FALSE
3 Warning3 TRUE
Now I want to return list of warnings that are set to ...

**0**

votes

**1**answer

25 views

### Looking to total cells, but leave some blank in between

I've written a worksheet for selling work. I have one column "C" for the cost, and the column next to it "D" for a subtotal. Depending on the work to be performed, not all rows will have values to ...

**0**

votes

**2**answers

46 views

### Conditionally Formatting Across Cells

I am organizing an event and would like to do the following:
I have a General sheet with all the invitees names, addresses, etc. I also mark here if they replied and how many people will be ...

**0**

votes

**1**answer

21 views

### How to use excel 2010 functions to caculate the sum of distinct unique values?

How to use excel 2010 functions to caculate the sum of distinct unique values?
remove the duplicate values
sum of distinct unique values
but I don't need to know who are the distinct unique values, ...

**0**

votes

**2**answers

26 views

### How can I write-protect a worksheet, while permitting link jumping to another worksheet to enter comments?

There is a very important MS-Excel sheet that I maintain, which I need to share with other colleagues. For every row in the particular worksheet that I maintain, multiple colleagues could leave ...

**0**

votes

**1**answer

23 views

### Change background color of rows which are present in another column

I have data in the A and B column. B column's data is mostly duplicates of A's data, but not always. For example:
A
Budapest
Prague
Paris
Bukarest
Moscow
Rome
New York
B
Budapest
Prague
Los Angeles
...

**0**

votes

**2**answers

55 views

### Using COUNTIFS for a series of values at once

Working a step higher then COUNTIFS, I appose a challenge to write a formula without VBA code. The basic data is combined from 1000s of rows with:
Column A: rows with values from 1 to 3
Column B: ...

**0**

votes

**2**answers

394 views

### Excel 2013 - How to remove duplicates from 1 column but leave empty cells in place of duplicate?

I have a sheet with 2 columns. first column has unique values, while the second column has many duplicates.
I would like to remove duplicates from the second column , however the cells that ...

**0**

votes

**1**answer

500 views

### Excel formula to count unique values in two columns

I have two columns and I want to get distinct count for each of the account codes:
Account 12 12 34 56 90 90 91 91
Account code a b a a a b a a
...

**0**

votes

**1**answer

38 views

### What is this argument of AGGREGATE function doing in my formula?

I need to understand this part ($B$1:$B$15<>"") in the following formula used to represent a column of data with blank cells removed:
...

**0**

votes

**3**answers

2k views

### Compare multiple columns for any two or more cells within the same row having the same content

I need a formula to compare multiple columns for any two or more cells within the same row having the same content. If that is true, then display "TEXT A" (can be anything such as "TRUE"). If all of ...

**0**

votes

**0**answers

21 views

### microsoft excel 2010 formula writing

I am writing numbers in a cell, but need the value of these numbers to change according to the letter being assigned. For example:
70 if I add a Q on the cell before (or after) it, I want it to ...

**2**

votes

**3**answers

413 views

### How to compute the number of occurrences of a string in the entire workbook?

I want to compute number of times a specified string appears in my entire workbook in some column. I have tried this formula =COUNTIF(sheet1:sheetn!C2:C150; "*string*") But it renders the #VALUE! ...

**1**

vote

**1**answer

27 views

### Excel 2010 : How to compute the number of non blank cells in a column among the entire workbook

Let's say I want to compute the number of non blank cells in some column (A for exemple) among the entire workbook, for a fixed range of cells. How can I do this ? knowing that for just one worksheet, ...

**0**

votes

**0**answers

135 views

### Check if substring of cell in one row matches with another cell on column values in another row

I have a table with 2 columns (ID number, Description) as shown below:
ID no Description
1001 A letter to george william
1001 ...

**3**

votes

**1**answer

167 views

### Excel Formula to Check if a Word Begins with a Keyword

As the picture shows, I have 3 keywords from cell A2 to A4: a, bb, and ccc.
I have 5 words to check, listed in B2:B6: aa, bb, bc, ccand d.
I'd like to check, for each word in Column B, that if it ...

**0**

votes

**1**answer

25 views

### MS Excel: Count the max spaces between a number frequency

I have a column of numbers (i.e. 7,2,5,2,6,7,5,3,7,8,9,2,3,5,6,7) What I am trying to do is count how many numbers are between each 7. Not just the first, second, third, etc. but each instance. So, ...

**0**

votes

**1**answer

32 views

### Excel logic AND and OR functions

Does my formula below need to read (Occupancy=1)*(Gender=2) to consider the occupancy for both genders?
=(MIN(IF((Occupancy=1)*(Gender=1)+(Gender=2),Gross_Rev)))
I guess as a general question, ...

**0**

votes

**0**answers

16 views

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

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 ...

**0**

votes

**2**answers

41 views

### Make Excel marks the repeated rows with a lot of data in each row

I have this Excel archive with 5 columns of data, and then, about 4,000 rows of different data for each column, but I can’t find a way to make that Excel marks the repeated rows?
I have tried using ...

**0**

votes

**1**answer

42 views

### Sum values in range if lower than a threshold otherwise sum the threshold value

As per the title, the best I could come up with is this, however it yields an error. My unremarkable knowledge of Excel prevents me from making further progress.
This example formula should add all ...

**0**

votes

**2**answers

75 views

### Excel: how to remove leading characters of a string with a function?

I have a text column in Excel full of hexademical values like this:
0000000000000c10
00000000000036f0
00000000000274da
00000000000379e0
Which function can help me strip the leading 0's up until the ...

**0**

votes

**0**answers

21 views

### Spreadsheet Function Help - From A Table Of Names And Birth Dates Pull A List Of Names As Of A Reference Date And There About

I have posted this question before, and got half-solution. Pls have a look at this link to get an idea.
http://yogi--anand-consulting.blogspot.in/2014/11/yogifrom-table-of-names-and-birth-dates.html
...

**0**

votes

**1**answer

45 views

### Excel formula - Return value from lower row

I'm looking for a way to search for a value in a single column and return a value in that same column two below it. Example:
A1: Apples
A2: Bananas
A3: Oranges
A4: Pears
If I search for ...

**0**

votes

**2**answers

25 views

### Why is using this code to replace a formula giving me an error?

In Excel I use the following to clear cell E5 and replace it with the word name.
Sheets("Invoice").Range("E5") = "Name"
I can also use the following to replace G11 with the VLOOKUP code I have here ...

**0**

votes

**1**answer

32 views

### Excel Conditional sum on same row

I have a rather basic Excel question, but my Excel skills suck.
What I want is a conditional sum, but I'm not sure how to do it.
Example:
A B C D E
...

**0**

votes

**3**answers

65 views

### How do I get the fiscal year to start on a specific date each year in Excel?

I used the formula listed here to generate a fiscal year based on the current month. I have an odd requirement where she wants the fiscal year to start on 6/20. For example, any dates ranging from ...