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

**0**

votes

**2**answers

48 views

### Excel- Generate a list from single cell input

My question is basically how can you generate a list in Excel based on a single cell input?
For example, the user types 10 then replaces it with 20 and then 30 etc, is it possible to generate a list ...

**2**

votes

**1**answer

23 views

### Excel - Exclude Cells from Computation

Say I have 5 columns of data with 100 rows (so from A1 to E100). For each row I want to compute the averages of the 5 entries while I always exlude one of the entries. E.g. for the first row I would ...

**1**

vote

**2**answers

37 views

### How do I create spreadsheet that will alert me when a quantity drops below a certain threshold in Excel 2010?

I am trying to make a sort of "inventory helper" spreadsheet that can track quantities. On the summary sheet, I want to be able to enter a quantity into a column called "On Hand" and then if that ...

**0**

votes

**1**answer

20 views

### Using the text values from one column to lead to output in another

I have a list of cities in one column, Column C.
I have a list of countries which relate to each of those cities. How do I get Excel to look at the Column C, compare to my list of countries (I can ...

**0**

votes

**2**answers

55 views

### Array manipulation in Excel formula arguments

my question is pretty specific, and I haven't been able to find a solution. Here's a sample dataset that illustrates the problem:
First Last Sales Months
Kevin Smith $500 10
...

**0**

votes

**1**answer

47 views

### LibreOffice Calc Error 518

I'm trying to achieve the following in LibreOffice (did it with Excel already, but importing this excel-created file produces the same error).
I have 3 columns
(A) (B) (C)
ECTS Mark ...

**0**

votes

**3**answers

48 views

### Average Sum of Pairs of Numbers in a Column

Suppose I have a column of 10 numbers and I want to know what the average of the sums of each number with each other number (except itself) is. So it would be the average of the sum of, for example, ...

**-1**

votes

**1**answer

31 views

### Extracting rows in an Excel worksheet where there are duplicates based on selected columns

I have an Excel worksheet where there are multiple records (rows) and I wish to create a new "tab" (worksheet) by removing the "duplicate, triplicate etc." rows based on checking for identical ...

**0**

votes

**0**answers

38 views

### Expanding abbreviated text with Substitute formula in Excel

I have a spreadsheet contains a list of 24k items found in a grocery store. Most items have abbreviated words such as: w/ for with; choc for chocolate; sauv for sauvignon. Yet the list contains both ...

**0**

votes

**1**answer

22 views

### Calculating total salary

I store my working time in Libre Office Calc like this:
Start End Hours
10:00 15:00 05:00
11:00 14:00 03:00
09:30 15:30 06:00
Total
14:00
Now I want to calculate my salary, i.e. ...

**0**

votes

**1**answer

59 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

19 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

34 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

60 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

29 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

51 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

57 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

100 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

144 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

196 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

54 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

70 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

28 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

241 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

54 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

80 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

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

97 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

29 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

25 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

49 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

307 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

446 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

37 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

20 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

317 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

139 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

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