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

**1**

vote

**1**answer

398 views

### Table AutoExpansion incorrectly changes formula

I have a table in an Excel sheet where one column is calculated.
When adding a new row by typing, the table automatically expands, but wrongly changes the formula of that column in the row above. It ...

**0**

votes

**2**answers

20 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

**3**answers

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

**1**

vote

**2**answers

536 views

### Filter and reorder columns in Excel

I've got a service (phplist, a newsletter manager) that exports a user list with several fields. At the end of it, each user has one or more lists to which he has subscribed.
The problem is, that ...

**0**

votes

**1**answer

11 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

**0**answers

10 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

**2**answers

19 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

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

**4**

votes

**1**answer

2k views

### Linking Excel 2013 cell to Word 2013 table cell adds unwanted line breaks

I have a problem where I am trying to copy an excel cell that contains content using a formula, into a word document table cell and link them such that the formatting is used in word.
What happens is ...

**0**

votes

**2**answers

20 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

26 views

### Excel Averageifs with multiple conditions in one column

I am looking for some help for how to tidy the second formula below in bold. I am looking to calculate the average 'Pupil_Score' (this is also my first try with named ranges) based on race, I am ...

**0**

votes

**1**answer

4k views

### Copy/move values matching a pattern to another column

I have a worksheet where we need to move all the values matching a pattern to different columns.
For example, we have a B column with values that are one or more letters followed by one or more ...

**0**

votes

**1**answer

16 views

### Subtract between two sets if one has value in the same row

In one row I have two possible places for times to be completed, Day Shift and Night Shift.
I want to do a simple subtraction where the cells are completed (Example =O9-S9 or =K9-G9 where there are ...

**0**

votes

**1**answer

14 views

### Excel If Statement Override and Sum

Here is what I have:
=IF(SUM(J19:J29)=Quote!J55,Quote!J55, "ERROR")
The sum of the rows must equal the total in a cell of another sheet or it reads ERROR.
Then I have Alternative totals that can ...

**0**

votes

**1**answer

290 views

### Can I force MS Excel table column to use the specified pattern?

I need to develop a tool that enforces strict format of some table. The fields in this table follow specified patterns like number:number (11:20. 2:00), agreed date (11/20/2012 but not ...

**0**

votes

**1**answer

923 views

### Formula to copy a specific character from one cell to another

I am using MS query to pull in data. I need to create a column that copies the first character in another column. For example:
Cell A1 - E00633
Cell A2 - B00842
Cell B1 - I want this cell to ...

**0**

votes

**0**answers

3 views

### How Do You Replace a Formula With Its Result? [migrated]

I've been searching and searching but haven't found what I think is the right answer. I'm not an advanced user of excel. At best, somewhere between beginner and advanced.
I created a Google ...

**0**

votes

**2**answers

6k views

### Comparing columns in two different excel sheets and if they match copying third column

I have limited working knowledge of Excel and I hope someone will be able to help me with my problem.
I have two different work sheets (say F1 and F2) with last name in Column A, first name in Column ...

**0**

votes

**1**answer

22 views

### how to create persistent random numbers &letters in excel?

Below is link for example sheets https://docs.google.com/spreadsheets/d/1D-D29csRGAAZl_h7fyMwLJ6am0FpZAtCZ5r0XlmQVqo/edit?usp=sharing. This is a live spreadsheet, which can't be pasted in this ...

**0**

votes

**0**answers

12 views

### Google Docs Formula [migrated]

I have a problem creating a formula for my sheets. I want google docs to put either "yes" or "No" in a specific cell depending on the values in other cell. in other words I want it to put "Yes" when ...

**1**

vote

**3**answers

49 views

### Checking whether a label exists between two other labels in a column

I have a column (call it A) in Excel lists off different items and their properties. Every item has the word "Type" associated with it and so there is a cell with that label in column A so that in ...

**0**

votes

**1**answer

34 views

### Calculate the difference in hours from now and next workday's 9am

How do I calculate the difference in hours from now and the next workday's 9am?
If NOW() is Thursday 6pm and the next workday is Friday, the function would then return 15.
If NOW() is Friday 5pm and ...

**0**

votes

**1**answer

16 views

### Insert formula for each row in Excel or LibreOffice

I have the following data in LibreOffice:
As you can see there are 3 sets of measurement. I want to automatically feel in the D column with the following formula automatically. For example D2 will ...

**-1**

votes

**1**answer

31 views

### Excel- SUM formula not working on IF formula converted data [closed]

In excel I used an if formula to change values to 1 or 0. This worked fine. I tried to sum the data from J2:J2103 but it always shows a zero for the total. I used auto sum and it showed the total to ...

**1**

vote

**1**answer

1k views

### PivotTable — how to sum the result of a countif formula by group?

Current Setup
A table (Table1) that has the following fields:
Organization Name
Person Name
Several fields that users can put an "X" into (each represents a "module" that users would need access ...

**0**

votes

**1**answer

14 views

### Variable file name in cell reference as part of longer string using OneDrive

I want my spreadsheet to be easily replicated with each new year. I've already got an input section that allows me to enter the present year (for instance 2014 in A1), generate the prior year ...

**0**

votes

**1**answer

49 views

### Excel Date Manipulation Function

I have an excel table that contains start times and end times and the added hours. Here is what it looks like
***********************************
* Start Time ** End Time ** Hours *
...

**0**

votes

**1**answer

634 views

### Line chart from multiple tables in Excel 2013

I have two sheets, "Graphs" and "Data".
In "Graphs" is a line chart with a single line that gets X and Y values from tables in "Data". I want the chart to be updated automatically when I add ...

**1**

vote

**2**answers

579 views

### Cumulative average using data from multiple rows in an Excel table

I am trying to calculate a cumulative average column on a table I'm making in Excel.
I use the totals row for the ending cumulative average, but I would like to add a column that gives a cumulative ...

**0**

votes

**1**answer

35 views

### Access table data by row index

I have a Table in Excel that is laid out as follows. What I require is a way of accessing a table row by index for a calculation.
To be precise, I have been asked to work out an overall coefficient ...

**0**

votes

**1**answer

2k views

### Excel - Displaying a value in a cell dependent on another cell having text

I have an excel file with 2 sheets. The first sheet is a user input sheet with cells that are properly formatted for us. The second sheet pulls the raw data into table format so that we can inport ...

**0**

votes

**0**answers

17 views

### Looking up the row number of a shared identifier in Excel, and counting the number of cells in that row that meet specific criteria?

I have a sheet that contains a lookup value in columns. I want Excel to look for each value in another sheet, and count the number of instances of a certain word in the row in which it found that ...

**0**

votes

**2**answers

46 views

### Excel Formula- Having a number automtically divide in half after 6 months

I'm tracking Transitional Housing in a Microsoft Excel- we pay a client's full rent for 6 months, and then half of that for the following 3. Is there a simple excel formula that can automatically half ...

**0**

votes

**2**answers

30 views

### how to check for a value in excel and populate column based on it

I have the following query :
My input table is
Basically I want to lookup columns for each ID.
Value3>Value2>Value1
If value3 and value column has data then ouput should show ID having value3
If ...

**1**

vote

**0**answers

36 views

### What does the TABLE function do?

I got a the formula {=TABLE(C7,C5)}.
I cannot find any table name from ribbon Name Manager, so I think there is no table. So, what is this formula doing ?

**0**

votes

**0**answers

20 views

### excel formula required to clear many specified cells in different sheets on the same workbook - [on hold]

i.e. on sheet 1 I want to instruct many different specific cells in other sheets in same workbook to clear text - so as i can rewrite into these cleared cells at a later time therefore cannot have ...

**0**

votes

**0**answers

5 views

### Formula to copy rows to a new worksheet based on a single value in a row

I need to extract complete rows from a spreadsheet to a new spreadsheet based on a specified value in the row. Cannot be advanced filtering since that only allows me to copy into same worksheet. Is ...

**-1**

votes

**2**answers

41 views

### formula to work percentage [closed]

I am trying to find a formula that will perform a specific function.
I have two values, for example 15 and 50. 15 (b6) is the number of questions I have correct and 50 (c6) is the total number of ...

**1**

vote

**2**answers

29 views

### Appending Group Labels to Discrete Values in Excel

Is there a way in Excel to, after providing a list of groups and a column of discrete values, map a group label to each discrete value? How can I tell Excel to essentially check which group the value ...

**0**

votes

**1**answer

25 views

### Add sum for records with duplicate titles

I have a spreadsheet with quantities that I needed added up for school names that are identical. Not all school names are identical and need to be added.
My data is 3000 rows long, below is a ...

**1**

vote

**2**answers

340 views

### Excel Lookup adds double quotes to string

I'm using this formula to get value form a table
=LOOKUP(C11;Alias[A];Alias[B])
where C11 = 12354567
But when I use
=LOOKUP(LEFT(C11,7);Alias[A];Alias[B])
where C11 = 12345678, the formula does ...

**0**

votes

**1**answer

18 views

### Compare to column in excel and return a value from corresponding third column to a new column

for example i have 3 column:
I whant the result in a new column as:
by comparing C with A return B

**2**

votes

**3**answers

26k views

### How can I combine these 3 conditions in a single Excel formula?

I need to create an excel formula based on the following:
1. If Cell >= 500 Then the Cell text should be "Critical"
2. If Cell >= 400 Then the cell text should be "Medium"
3. If cell < 400 ...

**10**

votes

**4**answers

2k views

### Is it possible to get the sheetname?

How can I get the sheet name in a cell? (I have more than one sheet, I want to get the current on each sheet)

**8**

votes

**4**answers

10k views

### What is a formula to remove the whitespace from an Excel cell when trim doesn't work?

I have a table of Excel data that I copied from an html table in Internet Explorer. A column of table values all seem to have a space at the end of them, but Trim won't remove it. When I paste-special ...

**0**

votes

**1**answer

44 views

### Auto sequential numbering based on cell criteria with two numbering sequences and ability to ignore duplicates

In the example below the numbering in column A skips rows that have the word "TOTAL" in column C but picks right up again in the next rows. The rows with the word "TOTAL" in column C have their own ...

**1**

vote

**3**answers

3k views

### Lookups targeting merged cells - only returning value for first row

I have a master worksheet which contains data that I wish to link to another 'summary' sheet using a lookup.
However, some of the cells whose data I wish to include in the summary sheet are merged ...

**0**

votes

**0**answers

17 views

### Get information from one worksheet and move it to another worksheet [on hold]

Basically, I could do this manually, but I have a lot of information and it would take me a lot of time. I am trying to find the right formula.
I have 8 worksheets in Excel.
Details
Date ...

**-1**

votes

**0**answers

16 views

### Why is Index and Match Formula showing incorrect value?

I am trying to pull some value from 2 combined column using this array formula.
{=INDEX($E$27:$E$35,MATCH(1,(H27=$B$27:$B$35)*(I27=$C$27:$C$35),0))}
The problem is the value shown is always ...

**0**

votes

**3**answers

62 views

### Excel VBA or Formula to convert text to Date with Time type

I have a quite a number of data where its date/timestamp has been imported into excel as text. Excel does not really recognize it as date with time. e.g. 25.08.2011 16:17:59 (mm.dd.yyyy hh:mm:ss)
...