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

**0**

votes

**1**answer

17 views

### Frequency formula in Excel 2010

I am just trying to figure a way to get the frequency of occurrence of a number within a large array, not the freq of the number in the array belonging to a range.
Eg, how many times does the number ...

**0**

votes

**0**answers

9 views

### Excel automatically applying styles to new data

I'm having a problem with Excel 2013.
I have a spreadsheet filled with names and related information for applicants. When I contact the applicants, I highlight the cells (using the fill tool) so I ...

**0**

votes

**0**answers

14 views

### Excel locked columns

-Is is possible to lock a column, for example column F, and then set it so if i scroll from column E, all other columns will appear before the F column. I am trying to set F up at the end of a spread ...

**2**

votes

**1**answer

24 views

### Excel averageifs multiple criteria on one range

I know how I can use average(if(... etc using an array, however I'm stuck on how to include other criteria in this
This is what I've got so far:
...

**0**

votes

**0**answers

12 views

### Can Excel automatically create an x-axis range?

I'm doing a basic financial analysis with Excel. The gist of it is I have an input parameter (discount rate) plus a bunch of static data that gets fed into the =NPV(rate, x1...x_i) function. I would ...

**0**

votes

**0**answers

23 views

### Add content from cell in sheet1 to cell in sheet2 when cell in sheet1 is found dynamically [on hold]

Reworded for clarity per site request 1
How do you find a value in a list on sheet1, get the value in the column to the right of it and enter it into a cell in sheet 2, then add a number to it, then ...

**1**

vote

**1**answer

17 views

### EXCEL - How to use two cells for input and output at the same time

I have 3 cells, VALUE, DOWNPAYMENT, DOWNPAYMENT %.
The 3rd cell "DOWNPAYMENT %" calculates the percentage of "DOWNPAYMENT" over "VALUE"
Is there a way for the user to have the function to input a ...

**0**

votes

**0**answers

16 views

### Excell 2013 Calculating across Multiple Sheets number of fields

I am trying to work out the total number of fields across multiple sheets to arrive at the total number of new members each month.
Some months have little as 50 others as much as 250+.
I thought I ...

**0**

votes

**2**answers

18 views

### Defining Variables in Excel

I have two lists of data, x and y, one in a row one in a column. I would like to populate a chart of rows and columns with a complex formula that is the same for each chart point, the only difference ...

**-1**

votes

**0**answers

20 views

### How do I get the data in a cell of the last column of a row? [migrated]

I'm using Google Sheets.
Every week I add a column with new data, and I need the data from the latest column copied into another sheet. The formula needs to pull the cell of the last column in the ...

**1**

vote

**1**answer

16 views

### Get the cell number from double clicking on it and put it into another formula

As part of my ICT work we need to create a battleship game and one way I was thinking of doing it was using
=IF(ISNUMBER(SEARCH("*x*",C3)),"HIT","MISS")
to check if that cell contains x but ...

**2**

votes

**1**answer

19 views

### Adding up amounts in a column for every non-blank entry in another column

I'm having a bit of trouble coming up with a formula to do the following. Say I have a sheet like so:
X 100 1000
200
200
500
Y 400 1250
400
300
150
Z 430 910
330
...

**0**

votes

**1**answer

32 views

### How can I use INDEX MATCH formula with two horizontal parameters?

Not sure if anyone can help me but thought I'd give it a shot...
I'm trying to create a formula to retrieve information from a data sheet. The data sheet contains a significant amount of information, ...

**0**

votes

**1**answer

26 views

### Excel conditional formatting for repeating values

I have a table of data where there are 50 rows and many columns. Each column's value is sorted by the most important result at the top. These are names of people in sports. I would like to see ...

**0**

votes

**0**answers

14 views

### Scores results needs updating as you type in the answer- excel lookup or similar [on hold]

I have a two tables the first is a table where results get typed into
Table Name Rounds
1 2 3 4 5 6 7 8 9 10
bonus Round played ...

**0**

votes

**1**answer

21 views

### What Excel formula can create a string containing sequential numbers? AKA REPT() with a counter

Say I have column A with a number in it. I want to put a formula in column B that gives me that number of repetitions of a string, concatenated together, with the number of the repetition embedded in ...

**0**

votes

**0**answers

15 views

### Issue Using Advanced Filter with Multiple Criteria

I'm trying to use the advanced filter to filter multiple criteria in two separate columns. The example I'm about to show is simplified so it is very important that we stick with advanced filter as ...

**-1**

votes

**1**answer

29 views

### Need Excel formula to calculate remaining budget [closed]

I have a table to track receipts over the next 18 weeks for groceries. I need a formula giving me the remaining balance (changes each week after entering that week's grocery receipts), if I am given ...

**-2**

votes

**0**answers

35 views

### Get list from range of data

I first need a way to do this: "List every unique occurrence from range A and display it in Range B". I can do it but I would like to know if there is a way to do it with merged cells?
Range A can ...

**0**

votes

**1**answer

24 views

### Excel: How to count cells, whose date values represent a day between monday and friday?

I want to count cells, whose values are dates, under the following condition: The date value entered must be a day between Monday and Friday.
Assume the following data and my try:
Column A
--
...

**1**

vote

**1**answer

24 views

### How to count number of unique buyers in excel? [closed]

I am creating a workbook for sold items of my company. and I have a list of items in column O of one of my sheets.
now I want excel to count the number of total buyers and number of unique buyers.
I ...

**0**

votes

**0**answers

3 views

### Google Sheets list key with highest value in key value pair [migrated]

I have a some data in key-value pairs setup like so:
A B
1 Key: Value:
2 Cars 5
3 Bikes 4
4 Vans 9
5 Trucks 6
What I can't seem to ...

**0**

votes

**1**answer

25 views

### Double click on one cell to change text bold and font color of another cell on the same row

I have some code written that was given to me by another user, Twisty (who I seam unable to reply to in the page that was used), which is amazing and what I needed. However, I need it to do a little ...

**0**

votes

**1**answer

15 views

### Microsoft Excel - Functions sum values after mulitplying multiple criterias after looking up tables

have provided a link to the excel worksheet that I need to resolve. I have worked up for two values in cell B9, which I need to do for other criteria which goes beyond 18 or so.
Formula that I used is ...

**1**

vote

**1**answer

32 views

### How to return a value from a range if cell contains that value

I have a list of Names (First and Surname) in A:A. I also have a range called 'Surnames'. How can I apply a formula so that B:B returns the surname only of A:A if that name is found in the range ...

**1**

vote

**1**answer

33 views

### Advanced data consolidation in Excel

I need to Combine/Consolidate Data dynamically between over 20 separate Excel workbooks. The Column names and their relative position at the top row are the same. The positioning of each word under ...

**1**

vote

**1**answer

30 views

### Excel 2010 - Need to sum dynamic range based on trailing 3 months

I am trying to sum a dynamic range for a list of entries 3 months before and 3 months after a specific month. I think I need to use an array to get this, but I'm not able to find what I need to do.
...

**1**

vote

**2**answers

31 views

### Excel - stipulate an average for random number function

I am wondering if anyone knows if there is a way to use the RANDBETWEEN function to generate a series of random numbers in Excel but also make those random numbers come out to equal a specified ...

**0**

votes

**0**answers

16 views

### using excel vba to copy range of cell from another worksheet to another worksheet [closed]

I have worksheet which contains values coming from a datasource which means the values are dynamically changing based on the period we selected.I want to copy these data to another worksheet for the ...

**1**

vote

**2**answers

35 views

### How to categorize text data based on a value in a column?

I am using Excel to generate a list of people working in the company containing information about the employees relevant for our department.
The spreadsheet looks generally like this (fictive data):
...

**1**

vote

**1**answer

24 views

### Excel VLOOKUP with named range not working

I have the following VLOOKUP on a worksheet:
=VLOOKUP(D5,FilePurposes,2)&" in respect of "&C5
Where the named range FilePurposes refers to a block of two columns and three rows that look ...

**1**

vote

**1**answer

34 views

### Sumif & Indirect

In using the following sumif/indirect function
(=+SUMIF(INDIRECT("'"&$A$3&"'!$B$2:$B$102"),$C13,INDIRECT("'"&$A$3&"'!F2:F102"))
my columns (F:F) wont move as I drag the formula to ...

**1**

vote

**1**answer

30 views

### Filter MS Excel table using formulas

I have created an Excel Table (using Excel > Insert > Table option)
[[TABLE1]]
|Name|Location|Salary|
|A |CA |100 |
|B |WA |200 |
|A |CO |800 |
|C |FL |350 |
|C ...

**1**

vote

**2**answers

32 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

**2**answers

20 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

**1**answer

42 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

**1**answer

43 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

**2**answers

31 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

**1**answer

40 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

**2**answers

41 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

20 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

33 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

16 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

39 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

29 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

37 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

27 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

27 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

**0**answers

11 views

### SHGetPathFromIDList does not return pathnames with leading periods [migrated]

I have some Excel x64 VBA code that gets MP3 files, along with track#, size, length, etc., and puts them in some worksheets. The basic code came from John Walkenbach's page and can be found here: ...

**0**

votes

**1**answer

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