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

**1**

vote

**1**answer

84 views

### VLOOKUP not interpolating all cells?

My VLOOKUP formula is not interpolating all cells
AD AE AF
11-6-2014 15:20 90,4 #N/A
11-6-2014 15:21 89,1 #N/A
11-6-2014 15:22 90,4 Good
11-6-2014 15:23 89,1 Good
AF column is my ...

**1**

vote

**1**answer

228 views

### Creating Dynamic, Unique Arrays Dependent on Multiple Criteria or Subset of Data

I have a Table structured in this manner:
City Region Date Value
ABC 123 Jan1 10%
DEF 999 Feb1 7%
The table is fed by user-entry, but there is an important rule:
...

**1**

vote

**2**answers

113 views

### Single formula to search column and return corresponding fields

Am having issues creating a single formula to search the billionaires list by name and autofill the rank, age, net worth, and citizenship field in the search spreadsheet.
This is the formula I have ...

**1**

vote

**2**answers

249 views

### How do I allocate different text cells in a row a number and then sum total that row

I have a spreadsheet setup to monitor absence in the office. The columns are the days of the month 1 - 31 and rows are the month/employees
Mon Tue Wed Thu Fri
January 1 2 3 4
...

**1**

vote

**3**answers

57 views

### Is there a quicker way to enter formulas?

In the firm I currently work in we have a very inefficient and time-consuming process for entering formulas. For example:
Y1=CHOOSE(B3,'[Flacflow balancesflacflow.xlsx]Data'!$A$77, '[Flacflow ...

**1**

vote

**1**answer

132 views

### Excel: Keeping references/links when transposing

I have a worksheet in where I have table (let's call it the summary table) with references to a another table (among others) which is 2x40 (rows x columns). I would like to tranpose this into a 40x2 ...

**1**

vote

**2**answers

626 views

### Calculate full months between dates in Excel 2010

I have dates in two columns and I would need to calculate amount of full months between two dates.
I export the spreadsheet from SAP ERP in .xls format but opening with Excel 2010. DATEDIF function ...

**1**

vote

**1**answer

382 views

### Convert formulas in table to structural references

I have converted my range in excel 2010 into a table. However, the formulas which were before like that(see below) stayed the same and do not convert into structural references.
=IF(AND(ROUND($GX19-...

**1**

vote

**3**answers

379 views

### Trouble using Excel OFFSET function

I have an Excel workbook with numerous sheets entitled "Week1", "Week2", "Week3" ... "Week7". Each week, I input the customers IDs of people buying my product in column C, and -- as I am interested ...

**1**

vote

**1**answer

4k views

### Excel Return Max Text Value Using VLOOKUP

I need to come up with a formula in excel that will identify the max and min software offered on a computer and input it into a new table.
So for instance, in one large table I have column names ...

**1**

vote

**1**answer

1k views

### Excel, compare cells in different sheets in a workbook

I am a real nooby and I have some questions.
I have 5 sheets (A,B,C,D,E). Each sheet has numbers in it (10 rows-15 lines) A1 to I15
Now I want to make a 6th sheet in which I can compare the cell ...

**1**

vote

**2**answers

17k 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 ...

**1**

vote

**1**answer

215 views

### Averageif with criteria being related to a different cell

I have an array of numbers in excel, and I want to get the average of all numbers where the column headers matches a pattern.
Currently, I have data that looks like this:
At the bottom I have an ...

**1**

vote

**1**answer

2k views

### Why do I get HEX2BIN() error for long (but valid) strings?

In Excel 2010, the formula =hex2bin("111",10) gives the right answer of 0100010001. According to the function help, the arguments should be at most 40 bits (10 hex digits). However, the following ...

**1**

vote

**1**answer

708 views

### Microsoft Excel lookup match without sorting data

Have a value list and need to insert points by looking up the data table. Data table has value for points in 1000 increments. Actual value that I lookup will not be rounded to 1000 so I tried using ...

**1**

vote

**1**answer

70 views

### Countifs array with number of days

I'm looking to see how many days people are around between certain blocks.
e.g. with people and the days they are around for listed in the first table (A1:C5) and the blocks in the second table (A6:...

**1**

vote

**1**answer

8k views

### Indirect function for worksheet names in Excel?

I am wondering if there is an equivalent to the indirect function for worksheets or workbooks instead of cells. What I mean by that is I am updating a rather large and unwieldy workbook for payroll ...

**1**

vote

**1**answer

677 views

### Excel 2010 VBA only affecting last row in range

I have some VBA code written in Excel 2010 to force padding around the rows in a table, but part of the code only affects the last row in the range. Here's the full code:
Private Sub ...

**1**

vote

**2**answers

92 views

### Testing a cell for multiple condiction before calculating value

I am working on a workbook that has a formula
=IF(ISTEXT(G4),0,IF(ABS((G4/H4)-1)>=0.2,0,1))
Now if H4 has a value of zero I get the Excel error #DIV/0!. I want to use the ISERROR formula to ...

**1**

vote

**1**answer

19 views

### Can I apply conditional inputting on Excel?

In excel,I want to input unique text in every cell of a column.suppose I adding name of some people on that column,and I don't want to add one persons name twice by mistake.so I want that if I input a ...

**1**

vote

**1**answer

475 views

### Can you define an array formula across multiple columns?

I have a table of Stores (well, for this example anyway):
Store_ID Store_Name ...
1 High Street
2 Low Street
...
I have another table of Employees:
Employee_ID Store_ID Is_Active
1 ...

**1**

vote

**1**answer

29 views

### Replicate a Calculation Sheet for each customer

I created a LibreOffice sheet, with the detailed list of expenses. And a second list with the names and debts of each neighbour.
However, I'd like to generate a personalise replica of the first ...

**1**

vote

**1**answer

203 views

### Excel 2013 Column value determines another columns letter assignment.

I have 5 columns but I need to define column E by column A, If column A has a number that isn't zero in the array, column E should read "Overdue", what formula can I use?

**1**

vote

**1**answer

5k views

### Change Value of Excel Cell when another cell contains specific character

I created a multi-sheet Excel workbook to track workouts. Page 1 is a calendar with clickable links to each other page. Each other page is a specific type of workout. So if you click today's date on ...

**1**

vote

**2**answers

524 views

### Excel: return name of column with highest value in a non-continuous array

I have looked up many answers to find the highest value in a column, and return the column name. However, every answer is dependent on using a continuous array and uses the INDEX function. This is ...

**1**

vote

**1**answer

131 views

### Calculate dates and return a 0 if it is the current date or date passed in Excel 2010

I need to calculate days between today and the 'Last day to offer' and have Excel return a 0 if today is the last day to offer or if the date has passed; otherwise it should return the number of days ...

**1**

vote

**1**answer

73 views

### Return Earliest Date from a range

I have an excel spreadsheet where I want to pull the earliest date in the column based on the criteria in a different column (for a given tech) into a different sheet. Rows on sheet1 contain store ...

**1**

vote

**1**answer

807 views

### Splitting an excel 2010 spreadsheet into multiple workbook files based on column value

I find and clean a lot of contact data, sorted by country, which I am currently using excel to manage. Mainly because it is easier to clean and manipulate the data I find this way, and I can easily ...

**1**

vote

**1**answer

474 views

### Add cell data to new column at the end of each row in excel?

I have a spreadsheet and I need to combine the information in three cells and add the combined information to a new cell on the same row. For example, I have columns A through M and rows 1 through 10. ...

**1**

vote

**1**answer

74 views

### How do I lookup using 2 values, Verticle and Horizonal? (Apple Numbers)

I have a sheet that has an individuals info, one table as their Age (TABLE 1). Another Table has a score in cell (TABLE 2). I want to lookup the VALUE that corresponds with their age and score in ...

**1**

vote

**2**answers

1k views

### Get percentage of distinct values with dependency on 2nd column

I have a survey that I sent out to people. One question asked their sex and another asked if they get headaches. I now have to results in an Excel file and I'm trying to analyze it.
But I'm having ...

**1**

vote

**1**answer

173 views

### Extending formula down the column without symmetry

Suppose this is the formula for my I2 cell =IF(C2<>C1, N2, K1).
If I extend this formula down the I column in excel, the default behaviour is
I3= IF(C3<>C2,N3,K2)
I4= IF(C4<>C3,...

**1**

vote

**1**answer

143 views

### How can I combine the values from one column in rows where another column has duplicate data with Excel Starter?

I may have worded this badly so here is a screenshot:
So in reference to the screenshot I want to combine the three rows I have highlighted. That data from the column I pointed to should be ...

**1**

vote

**1**answer

928 views

### Avg times in mm:ss.0 format

I have a range of times formatted as mm:ss.0. The problem is trying to average these times.
Time AVG
2:44.35
2:42.88
2:47.49
2:48.86
2:56.31 00:00.0

**1**

vote

**2**answers

581 views

### Average using Elapsed Time processing a number of docs

I process documents and want to get a formula showing the average time to process each document.
Here's my data:
number of docs processed (column G)
start date/time (H)
end date/time (I)
elapsed ...

**1**

vote

**1**answer

112 views

### MS Excel 2007 If Then Formula Troubles

This is a simplified case of an issue that I am seeing using MS Excel 2007.
Format your sheet to be currency with 2 decimal places.
Cell A1 type 25
Cell A2 type 20.13
Cell A3 type 4....

**1**

vote

**1**answer

2k views

### How a count of values which have more than 1 occurance in a column

In cell B5 I have a formula:
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10&"")=1))
If column A has the data as shown below, the formula returns the ...

**1**

vote

**1**answer

64 views

### Zero's before Store Numbers in excel

I recently got a spreadsheet from a vendor with a list of store numbers that I need to use for a crystal report that I am running. Store numbers are 5 digits, most with at least one leading zero, and ...

**1**

vote

**3**answers

160 views

### Collect Row # of Matching Elements in Excel

I'm trying to collect the row numbers that match a certain value in multiple columns of an excel worksheet. The behavior I'm looking for reflects the table below:
|---|---------|---------|---------|
|...

**1**

vote

**1**answer

4k views

### Lookup list dependant on the contents of another cell

hope someone can help to crack this one?
I've got a table on my data entry tab (sheet 1) where each row includes two data validation drop down lists (source is on sheet2). I want the options in the ...

**1**

vote

**1**answer

713 views

### Count duplicate cells (values, text & empty) when you compare two columns

I have two worksheets with the following data structures:
sheet a) Id Name abn address sheet b) Id Name abn address
1 AA 123 ac 25 ad 124 ab
015 ...

**1**

vote

**1**answer

297 views

### Matching Dates and Moving the rows to correspond with correct dates

Column A has dates over a given 10 year period (often a single date appears more than once). Column B has each date only once and Columns C-G have values corresponding to each date in Column B.
I ...

**1**

vote

**1**answer

147 views

### Transposing columnar record data to rows in Excel

Can I reference a cell in another worksheet like this?
Example:
Worksheet A
A1 Data1
A2
A3 Data2
A4
A5 Data3
Worksheet B (I want to copy by referencing the cells in Worksheet A)
B1 '...

**1**

vote

**1**answer

641 views

### Make a system call to calculate a spreadsheet cell value

Is it possible for me to make a system call in order to calculate a cell value in LibreOffice Calc?
To clarify, can I set a cell's formula to something like this:
=B3*C4-system("/my/amazing/script ...

**1**

vote

**1**answer

697 views

### Percent Rank and Curved Grading in Excel

I am trying to design a gradebook for a class which follows relative grading. Relative grading here means 30% As, 40% Bs, 30% C and lower. In a class of 11, for example, 3 students will get As, 5 ...

**1**

vote

**1**answer

125 views

### Following Sunday based on date in LibreOffice Calc [duplicate]

I'm looking to create a calculation to display the following Sunday in day-month format based on the current date (=NOW()).
How can I do that?

**1**

vote

**2**answers

111 views

### In Excel 2007, I need to convert a phone number to a serial number, preserving the last 4 digits

Example: Assume the device phone number is 847-630-4459.
Step 1: We add a “1” to the front of the number yielding: 1-847-630-4459
Step 2: For the first 7 digits (1-847-630), we subtract each from ...

**1**

vote

**2**answers

61 views

### sum sign in number cells

In 2 cells, I exactly have these values:
A1: 1+2+3
A2: 2+0.5+10
Is it possible to have sum of these cells (=18.5) in another cell?
I tried =SUM(A1:A2), but it returns 0

**1**

vote

**1**answer

3k 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 to)...

**1**

vote

**2**answers

933 views

### How do I drag down complex calculations in open office.

For example if A1 contains 30 and in A2 I write =A1-6 then I'll get 27 in A2 and if I click the little black squair in the lower right hand corner of A2 and drag it down then I get a column that looks ...