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

learn more… | top users | synonyms (5)

2
votes
2answers
93 views

Increment numbers inside a text in microsoft excel 2007

I would like to drag cells downward and increases the number next to letters. It work when there is no letter for me. For example, I want to increment the number this way AG1 AG2 AG3 AG4 ... I ...
1
vote
2answers
75 views

When I copy table data into excel, it ONLY pastes it into column A! How to fix this?

If I use the following link: http://web.utk.edu/~dhouston/excel/exam1.pdf to copy the table and paste it into excel, it will only paste the values into one long column in Column A (and each row has ...
0
votes
1answer
165 views

How to combine data set to create unique combinations [duplicate]

First of all I don't have experience with Excel scripting, just basic formulas. I've seen this other question on SU but I'm not sure if that's exactly what I need or how to adapt it to my needs. ...
0
votes
1answer
161 views

How do I get Excel to identify a country from city list?

I have a list of data with many columns. In one column - let's say C, there is a list of cities/places. There are multiple entries, i.e. one city pops up loads of times in a column in different ...
-1
votes
2answers
36 views

how to calculate items sold in excel?

I have a list of sales of my company in Excel file and I need to find how many items we have sold. I want Excel to count the filled rows in a sheet and tell me how many cells have been filled. ...
0
votes
1answer
97 views

How to Mirror cells in excel 2013 [closed]

I want to make an excel file for sales management of my company. the excel I have created has 4 sheets and every sheet is handled by different persons. I want to dedicate the first column of these ...
0
votes
2answers
53 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
1answer
25 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
2answers
43 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
1answer
21 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
2answers
60 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
1answer
60 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
3answers
52 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
1answer
32 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
0answers
41 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
0answers
22 views

How can I copy new data with additional rows, while including formulas?

As an example I have data in the range A1:D55, and in Column E I have a formula using VLOOKUP in reference to column A. I pull the data again for A1:D55 and the values change (titles remain the same) ...
0
votes
1answer
23 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
1answer
63 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
1answer
22 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
1answer
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
1answer
68 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
1answer
35 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
1answer
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
1answer
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
1answer
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
1answer
124 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
2answers
168 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
1answer
335 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
2answers
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
1answer
77 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
1answer
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
1answer
385 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
1answer
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
2answers
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
1answer
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
2answers
85 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
1answer
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
1answer
105 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
1answer
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
0answers
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
1answer
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
1answer
27 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
2answers
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
1answer
22 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
2answers
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
1answer
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
2answers
57 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
2answers
407 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
1answer
513 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
1answer
41 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: ...