# Tagged Questions

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 ...
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: ...
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 ...
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 ...
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 ...
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 ...
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 ...
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-...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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:...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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?
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 ...
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 ...
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 ...
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 ...
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 ...
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. ...
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 ...
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 ...
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,...
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 ...
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
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 ...
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....
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 ...
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 ...
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: |---|---------|---------|---------| |...
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 ...
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 ...
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 ...
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 '...
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 ...
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 ...
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?
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 ...
61 views

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