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

learn more… | top users | synonyms (5)

1
vote
1answer
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
1answer
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
2answers
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
2answers
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
3answers
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
1answer
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
2answers
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
1answer
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
3answers
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
3answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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
2answers
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 ...