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

**1**

vote

**4**answers

15k views

### Calculating the calendar week in Excel

I am using the following formula to calculate the calendar week if referenced to a cell with date:
=INT((A1-DATE(YEAR(A1);1;1)-WEEKDAY(A1))/7)+2
but it does not work properly. If I enter 24.08.2012 ...

**0**

votes

**0**answers

10 views

### Determine a Scrum team's historical performance

I manage a Scrum team that calculates "Planned" versus "Actual" velocity each sprint (2-week timebox). Velocity is measured in points.
For example, in sprint #1, the team may plan to deliver 50 ...

**-1**

votes

**1**answer

17 views

### Excel CONCATENATE function produces error when referencing a worksheet with space in name

In Microsoft Office Excel 2013 on a Windows 8.1 PC, I get an error message, #NAME?, when trying to do the following:
=CONCATENATE('Calculation Sheet'!D, "- Customer Sales")
Doing the same with an ...

**0**

votes

**1**answer

10 views

### Using ISNUMBER and SEARCH

I'm trying to use the formula =IF(ISNUMBER(SEARCH("180",A1,0),1,0) but for some reason it isn't working. I tried removing the quotes and the same problem occurred, I see entries (e.g. "it is 180 ...

**-1**

votes

**3**answers

44 views

### Formula is not working, cannot find error in the formula

I'm sure that this has an incredibly simple answer but I've looked over this formula for hours and can't figure out the error. Essentially what I want the formula to do is if F3 = "Freshman" then ...

**0**

votes

**0**answers

9 views

### How do I split a worksheet into multiple files dependent on the column values, calculate sub-totals and plot graphs?

I have a large data set and would like to split these into separate files/worksheets and perform the same analysis over each data set.
I have found a macro to split the data into separate ...

**0**

votes

**2**answers

2k views

### Calculate different discount levels in Excel

I have to create a price list with 3 different discount levels called eg. E1, E2, E3.
E1 = 10%, E2 = 20% and E3 =30% discount.
How do I write a formula to tell Excel if the item falls in class E1 it ...

**2**

votes

**1**answer

21 views

### Replace contents of Excel column with realtional data from another column/sheet?

I have two excel sheets that have been exported from MySQL, with relational data in them, and I need to replace the reference ID's with the actual data from the relative column.
For example, in one ...

**0**

votes

**1**answer

22 views

### Excel Office Pro 2010 SUMIF

Hi Im trying to SUMIF(range;"<>#N/A";range) but the cells im selecting to sum are not in a row adjacent, its every 4th cell in the row.
But when i try it gives an argument error each time i try ...

**0**

votes

**3**answers

50 views

### calculating end times with working hours and skipping weekends

In my worksheet, I want to calculate estimated end times of processes.
However, i want to restrict this to a predetermined time constraint. So for example, when i add 4 hours to 14:00 I dont want the ...

**3**

votes

**1**answer

30 views

### Excel, how to use text functions on lookup array without putting in another column?

If I'm using a vlookup, or a match function but I need to do some mumbo jumbo on my lookup material, is there a way of doing this in the lookup array without simply adding another column?
Let's say I ...

**0**

votes

**1**answer

37 views

### Excel - find multiple pieces of data in several different columns at once

I've currently got a spreadsheet on the go which lists contracts. I need to know if a contract is a renewal, new to product, or new to company. I'm currently struggling with the logic behind it and ...

**0**

votes

**1**answer

20 views

### VBA highlighting a cell if corresponding range in the same row is not blank

I am trying to enforce food name in column B of a file if column D to DX (Qty) has any value in it.
Rational is if any new request of food is received which doesnt appear in the file, the user can ...

**-2**

votes

**1**answer

27 views

### Attendence Points Rollover

I have an attendance worksheet where we calculate employee attendance based on points, if employee absent they get 1 point and if tardy or did not complete shift get 1/2 points also if employee has ...

**1**

vote

**2**answers

16 views

### Finding repeating digits at the end of a string of digits

I have a list of 10 digit toll free phone numbers. I would like to identify those that contain at least 3 repeating digits in the last 4 digits of the number. For example:
8xxxxx5444
8xxxxx2220

**0**

votes

**0**answers

16 views

### Column order and names for table objects in Excel

I have a table in Excel that is connected to MS SQL Server table in the database. When I created a connection I specified a query that returns col1, col2, and col3 in this particular order. Then I can ...

**0**

votes

**1**answer

28 views

### Functions (specifically text functions) within a countif function

Is there any way to nest text operators within a countif?
I want to check if the first 4 characters of a file are in the form A**.
=Countif(Left(A1,4),"A??.") doesn't work, but I can put in a
...

**1**

vote

**1**answer

49k views

### Can Excel use formula-input data for cell references?

I have a cell in Excel, to which I want to pull data from another cell that is defined as a value in a third cell.
For example:
A1=5
A2=10
A3=insert syntax here, using A4 as source for cell ...

**2**

votes

**1**answer

12 views

### Can you apply names in OpenOffice Calc the same way you can in Excel?

In Excel, you can apply names to a formula (semi)automatically by selecting the formula/formulae in question and then using the Apply Names function.
This will change something like =B3+C9*D16 into ...

**15**

votes

**5**answers

28k views

### Type math formulas in Microsoft Word the LaTeX way?

I wonder if there is some free solutions for typing math formulas the LaTeX way in Microsoft Word document (Office 2007)?

**1**

vote

**0**answers

28 views

### Matching a column range based on a column label

I have written a spreadsheet for Excel 10 and am struggling with the last bit of formula.
I wrote this:
=SUMPRODUCT(--(ISNUMBER(FIND(B7,'PO SHEET'!$K$7:$K$74))),('PO SHEET'!$W$7:$W$74))
However, ...

**1**

vote

**2**answers

8k views

### Excel compare two columns and list a third column

I have an Excel sheet which has 3 Columns:
A B C D
1 A 1
2 B 3
3 C 4
6
5 D
7 E
I want to check column C for values present in column A and list corresponding values of ...

**0**

votes

**3**answers

65 views

### Excel Formula average without blank cells

I have a row of values and want to find the average of the first 3, not counting blank cells. Example:
A B C D E F G
12 blank 8 blank 7 9 4
The average of the first 3 ...

**-1**

votes

**1**answer

24 views

### How do I isolate certain characters in cell content of an excel to new cell?

How do I isolate certain characters in cell content of an excel to new cell
Example: I have a big paragraph of information wrapped in a cell and I need to separate only few characters of that data ...

**-1**

votes

**2**answers

8k views

### How do I split single cell contents into multiple columns without seperators?

I have an excel spreadsheet that has the following:
Cell A1 ABCD 1234
I would like to split this to be
CELL A1: ABCD
CELL B1: A
CELL C1: B
CELL D1: C
CELL E1: D
I tried using the ...

**0**

votes

**1**answer

19 views

### Run formula from different sheet

Can you run formulas from different sheets? (not simply showing the formula as a string/static text)
Say I type
=Sheet1!A1
where in that cell I have a formula
=concatenate(ice&cream)
...

**0**

votes

**1**answer

22 views

### Copy every nth column on a specific row from one sheet to another (in Google Sheets)

I want a function to select every 2nd column on a particular row and display it in another sheet.
I have two sheets: Sheet1 and Sheet2
In Sheet1 A2 I have a list of numbers that start from 1 to ...

**0**

votes

**2**answers

32 views

### Return data based on matching date range

Our group has an Excel sheet that's used to track rotating responsibilities. A simple example would be laid out like this:
Column A: START has dates defining the start of the time period represented ...

**0**

votes

**1**answer

2k views

### Combine duplicates in Excel, sum some columns and weighted average the others

This question refers to an existing subjexct on our site. I need a variation on this solution. I have a spreadsheet with 8 data columns. The number of records can vary from a few hundred to about ...

**1**

vote

**2**answers

26 views

### Generating coefficients for SERIESSUM formula in Excel

I'm modelling a cash collection spreadsheet where a company sells products each week and wants to collect cash over a number of weeks (n; between 42 and 336 weeks).
Assuming n is ≤ 52:
To calculate ...

**4**

votes

**2**answers

4k views

### How do I download a stock price in Excel 2013? Is there an equivalent to Google Doc's GoogleFinance spreadsheet function?

In a Google Docs spreadsheet, I can use this cell formula:
=GoogleFinance("GOOG", "price")
to download the latest price of a stock. Is there an equivalent function in Excel 2013?
Earlier versions ...

**0**

votes

**1**answer

20 views

### How can I use the value in one cell inside the formula in SAME cell?

How can I use the value in one cell inside the formula in SAME cell???
A1 1000
B1 5
I WANT ANSWER IN A1 =1000*B1
A2 = 100*B2

**1**

vote

**0**answers

21 views

### excel formulas not working in structured table protected cells

Structured Table1=
A B C
QTY Price QTY*Price
1 3 3 =A*B
2
3
Column QTY & Price are not locked
while Column QTY*Price is locked & hidden as it has a ...

**2**

votes

**3**answers

7k views

### Find a non-case-sensitive text string within a range of cells

I've got a bit of a problem to solve in Excel, and I'm not quite sure how to go about doing it. I've done a few searches online, and haven't really found any formulas that seem to be useful. Here's ...

**1**

vote

**2**answers

36 views

### Excel 2010 Advanced Filter issue

I ran into something I can't really explain trying to filter a huge pile of data using the advanced filter...
I had a document with hundreds of thousands of lines (.xlsb) containing a whole bunch of ...

**-1**

votes

**1**answer

24 views

### How to return earliest date

I want to look at the dates of 5 columns of qualification then enter the earliest date in a new column then if it is before today's date turn it red and highlight it.
I can do the second in ...

**0**

votes

**0**answers

16 views

### Search multiple sheets for a term in one column each, copy all matching rows to a new sheet

We have a 3rd party supplied , multi tab sheet that I would like to filter and copy data to a new sheet.
In the original sheet, all tabs can be searched for the term 8A in column C. I want any row ...

**0**

votes

**1**answer

32 views

### How can I find duplicate values in a column delimiting the results by ranges in a different column?

A B C
1 RANGES VALUES FORMULA NEEDED
2 1 x yes
3 1 x yes
4 2 x no
5 3 no
6 ...

**2**

votes

**1**answer

48 views

### Excel Macro Hyperlink.Add Errors

I'm relatively new to VBA code within Excel, so please forgive me if the answer is simple (I expect it usually is). I'm using Excel 2010, for reference. I'm trying to accomplish the following, with ...

**0**

votes

**1**answer

14 views

### MS Excel cell references shifting after using a macro?

Basically this is what happens:
On Sheet1 I have a column of cells that are linked to other cells on sheet2. Looks something like this.
Sheet1!A1=Sheet2!A1
Sheet1!A2=Sheet2!A2
Sheet1!A3=Sheet2!A3 ...

**1**

vote

**1**answer

22 views

### How to get the median based on column headings?

Let's say we have:
EPS Sentiment Revenue Sentiment Formula1? Formula2?
10 3% 20 5% F1a F2a
9 6% 30 4% F1b ...

**0**

votes

**2**answers

28 views

### How can I set a cell value based off of what another cell contains?

I'm building a time sheet that also tracks the costs to certain jobs.
Employees have set Pay Groups, based off the Pay Group, we know the billing rate. However, the list is constantly changing order ...

**0**

votes

**0**answers

8 views

### Formula returning #N/A when referenced cell is filled in?

I have a log I have been using for years with no problems. All of a sudden my formula is returning an #N/A. The formula reads =IF(C6=0," ",VLOOKUP(C6,'ODP LISTING'!B$4:C$35,2)). On the sheet labeled ...

**0**

votes

**0**answers

21 views

### Auto Extracting Data from Excel file on Onedrive

Our team is using One Drive for file sharing. What we wanted to do is to be able to Auto Extract data from 1 excel file on OneDrive to another excel file to be saved on another OneDrive ...

**0**

votes

**1**answer

15 views

### Query two fields and select answer from third field (If or vlookup?)

I need to compare two fields in my data to a table and get the resulting fx value from table.
How the formula would read I would guess:
IF local_in_data = local_in_table AND
expense_in_data = ...

**1**

vote

**0**answers

20 views

### which function would add a row to another set of rows, so it behaves as if they were below each other?

Example: having data in A1:E4 and another data in G1:K1 and i need to use them to look like A1:E5 so that G1:K1 was added below A1:E4.
The reason behind this is that excel is asking to give set ...

**0**

votes

**1**answer

2k views

### Excel - can't get the formula to copy correctly

Have an Excel sheet.
I want B3 = C3 * C2.
So far everything is good.
I want B4 = C4 * C2.
Did these two manually.
I have a lot of rows so I wanna copy the formula.
The problem is, I select them ...

**3**

votes

**2**answers

40 views

### How to automatically exclude maximum and minimum values when calculating an average in Excel

If I want to take a medial average, which excludes the highest and lowest values, is there a way to do this on Excel without having to manually find and exclude the highest and lowest values from the ...

**0**

votes

**1**answer

34 views

### reference to first visible cell above in Excel

How can I get a reference to the first visible cell above the active one after the data in a table has been filtered.
NOTE: I don't need this for summing up the data above. I know how to do that on ...

**0**

votes

**1**answer

39 views

### Why does excel tables break VLOOKUP formulas

I have a worksheet using raw data that Excel does not identify as Tables (ie. plainly putting values in adjacent cells), and on another part of my worksheet, formulas doing lookup in that data, like
...