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

learn more… | top users | synonyms (5)

3
votes
0answers
20 views

Nested IF function returning tiny value instead of zero

Whilst creating a balance sheet for a lotto syndicate, I encountered this phenomenon; Note the negative zero value in D5. With no formatting, its actual value is -0.0000000000000230926389122033 ...
2
votes
0answers
49 views

How to output a CSV file from a matrix of Excel formulas using multiple files?

I am receiving on a weekly basis a list of Excel spreadsheets from a supplier (around 40 spreadsheets) with data in a specific format. Since the format is a bit complex and can't be converted to CSV ...
2
votes
0answers
25 views

What function (or functions) can I use to pass worksheet title to a reference?

I have a number of cells on a worksheet that reference another worksheet. Pretty straight forward except I have to type the name of each worksheet in each formula which is becoming monotonous. What I ...
2
votes
0answers
69 views

Excel Index Match with IFs?? + options for next in list

OK I have been working on this for some time and can't quite get things right in the various combinations I have tried, so I am asking for advice. I have a management data table where column C is a ...
2
votes
0answers
2k views

How to copy a column with conditional format by “value” - Libreoffice 4.1

I have a column that contains the data and a set of rules for the conditional format of the cells. i.e. the conditional format changes the background colour of some cells if they are negative. I ...
2
votes
0answers
2k views

Openoffice Calc - use cell font color in formula

I have some openoffice calc sheets where some rows are maked with the font color red. All other rows are in the default font color. Can I use the font color of a cell within a formula to react to ...
2
votes
0answers
254 views

How to get new data to automatically slot into correct column in Excel?

I am trying to move new data into an existing database with set columns. The existing database has a column for every possible type of data, but not all new entries contain all columns. So I can't ...
1
vote
0answers
15 views

Align rows from different columns (that contain some cells with identical values) using simple formula in Excel

I have 2 tables in Excel and I am trying (via Excel formula) to match/align some rows (ones that contain identical letters for the sake of the example included below). Btw, I don't mind aligning Table ...
1
vote
0answers
31 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
0answers
37 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 ...
1
vote
0answers
21 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 ...
1
vote
0answers
17 views

Return cells with values in a column when we change the date on a different sheet and remove blank cells

I have one excel sheet which I normally record the expenses everyday. Column 1 will contain the money a person is given and Row 1 will contain the date of the month. What will keep on changing is the ...
1
vote
0answers
25 views

How do I incorporate IFERROR into this formula?

I need to know how to include an IFERROR statement in this formula in Excel 2007. =VLOOKUP($E3,HSA!$E$3:$G$20,2,FALSE)*$D3 I want the formula to return a 0 instead of an #N/A.
1
vote
0answers
129 views

ArrayFormula + Average giviing me div/o error

I have the formula on B2 if(D2="", "", AVERAGE(D2:AF2)) This gives me the average of all numbers in the row, and takes off the ugly error when there is no numbers. I tried spreading this formula to ...
1
vote
0answers
45 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 ?
1
vote
0answers
46 views

Return an array from a lookup

I have a table with column A containing vendors and column B containing product codes. One vendor has many product codes. In another place I need an array of product codes for a specific vendor. The ...
1
vote
0answers
126 views

Using getpivot in excel to create flexible reports based on user inputs

I am trying to make a report in Excel that sits on a very large set of data (set up in proper pivot form). I want to be able to select a value for a few fields (or select a value from 1 field but ...
1
vote
0answers
600 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
0answers
144 views

automatically transfer any data on excel spreadsheet to master spreadsheet

I work in a library and I am working on a workbook to track our orders from one company. I have three sheets where each sheet represents a different branch of our library. I am trying to have it set ...
1
vote
0answers
79 views

Microsoft Excel hyperlink function to a specific location in a Word doc

I have a long column of numbers in Excel that I have mail merged to a Word doc. I would like to write a function in Excel that will hyperlink each cell from this column to its corresponding mail ...
1
vote
0answers
48 views

How can I move columns based on the column header title to another workbook?

I have to average grades based on each objective for a new report card we have to complete this year. Column one has students names, each additional column will have the objective associated with the ...
1
vote
0answers
42 views

Excel for Mac 2011 move column 2 in sheet 1 to column 1 in sheet 2

I would like to move column 2 into column 1 for each successive sheet, one sheet per week, automatically. Is that possible? I do it with this formula ='1st week'!D2 and manually changing '1st week' to ...
1
vote
0answers
131 views

How to create a formula which finds a cell that matches a range of other cells then produces a number if the cell appears.

am having trouble with finding a suitable formula for my spreadsheet in excel. In column A i have a list of postcodes (439 postcodes). In column B-BL i also have postcodes (these postcodes are in ...
1
vote
0answers
172 views

Conditional Formatting a date/time comparison

The function I'm working on specifically compares & colors a daily recurring time range set in 2 different cells. A specific date in the target cells (produced by a formula) compare to that daily ...
1
vote
0answers
240 views

How might I evenly assign arbitrary categories smoothly across a large population?

Does Excel have a feature or set of features that can be used to accomplish the opposite of sorting? So, rather than sorting rows so that similar values are contiguous, I want similar values optimally ...
1
vote
0answers
329 views

What is the best way to create charts of “database-style” tables and data in Excel?

I'm a biologist and I frequently have to do analysis on relatively large (for Excel) datasets. Usually a few charts are needed to display the data, but creating them seems to require laying out out ...
0
votes
0answers
10 views

Automated weighted counts in a Pivot Table (Excel)

I have data for sales people who work with different clients. Sometimes the sales person has multiple deals with the same client. I want to be able to work out the count for each sales person for each ...
0
votes
0answers
14 views

Complex coutif formula

I hava a dataset that on the headers has: (String Employee Name, String Day off, time Check-in, time Check-out). What I want to do is get a table that has row labels from Monday through Friday, and ...
0
votes
0answers
13 views

Excel, pair 2 columns, then find all rows where column J is in two different Column D

SO I would like to have a formula or macro, to look at a table in Excel, pair two columns say J and M. Then find rows where M is connected to more then one value of Column J, Column J is Super Group, ...
0
votes
0answers
20 views

Excel performances: INDIRECT+ADDRESS VS CELL+OFFSET

Using Excel I can achieve the same result using two different approaches, here are the formulas: A) =INDIRECT(ADDRESS(5+MATCH($A$16,Aliquote!$A$5:$A$15,0),1,1,1,"Aliquote")) B) ...
0
votes
0answers
24 views

How to combine two sheets based on their dates?

I have two sheets: one is stock index, one is air pollution value. I want to match the air pollution value to each trading day. How can I do it on Excel? the first sheet looks like: 01/01/1997 39 ...
0
votes
0answers
18 views

All Data Option Filter Validation

I am creating a dashboard that has five inputs that filters my data by the custom data validation combination. My dashboard updates correctly based on the custom combinations, but I am unable to add a ...
0
votes
0answers
10 views

Using Hlookup to populate a worksheet from a data sheet

I am trying to develop a worksheet that requires a list of equipment based on an area header. I would like to be able to pull the separate lists based on this area header. Here is sample data I am ...
0
votes
0answers
19 views

Search for ID column in all worksheets and then search within those columns

I have multiple workbooks with multiple sheets on each. I am looking for a way to return the value under the column ID#, like to search all the sheets in the workbook. The header ID# might be in a ...
0
votes
0answers
38 views

Lookup ProductID on another sheet and return the lowest cost and its column header

I have recently been working with formulas and larger amounts of data than usual and need a little help. What I am trying to accomplish is the following. I have two workbooks (which I could make ...
0
votes
0answers
30 views

Can someone help me with an excel function that would make merging sheets easier?

I have about 61 spreadsheets that I want to merge into one big workbook. Int the one work book I have tabs that are named the same as the spreadsheets that I want to import the data into. There ...
0
votes
0answers
11 views

Matching data between two separate worksheets and transposing the data from another column next to the matched data

I have two worksheets. I am trying to input the stock number next to the matched claim number. How would I go about doing this? The first sheet is my inventory list. The second is a list we get from ...
0
votes
0answers
24 views

Microsoft Excel 2011 concatenate each row in a cell

I am trying to use an if function for each row for a result in a single/destination cell. The if function checks if a row meets a criteria, if it does, it adds the row details to the destination cell ...
0
votes
0answers
45 views

Average If function not returning correct value

I am trying to do an average if function, it is the average of some cells which provide a number based on a formula in that cell subtracting dates in other cells giving a number representing a span of ...
0
votes
0answers
14 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
0answers
22 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
0answers
26 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
0answers
11 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
0answers
37 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
0answers
27 views

paste data with blanks that does not erase formatting in destination sheet

I am trying to collect data using ms excel mobile which does not support a number of the functions I want to utilize. I have set up identical spreadsheets for mobile and full featured excel. The ...
0
votes
0answers
30 views

SUMPRODUCT & ROUND

Is there a way to get the product of the array with conditions to round to the 2nd decimal place before summing? Current formula w/o rounding: ...
0
votes
0answers
37 views

How to Sum based on two critera, date range and name

I have a table that has timesheet data for a number of users. Column A has the resource Names. Row 1 contains each week of the year, starting with the 5th of Jan. The table contains hours booked by ...
0
votes
0answers
33 views

finding the difference between two times in libreoffice

I'd like to know how to solve the problem: in cell A(n) is the first time in cell B(n) is the second time in cell C(n) is difference of times I don't want to put each time into cell C(n) function ...
0
votes
0answers
79 views

Extracting HTML from Excel to enter in MySQL/Wordpress site

I have a WordPress site and an Excel document. I'm not familiar with Excel formulas so forgive me. The document contains a lot of names, email addresses and telephone numbers. I want to create a ...
0
votes
0answers
50 views

Excel formula If column B starts with… then columns D & E populate from a list

I'm working on a spread sheet where column B is filled with ID numbers. Column D and E correlate directly to the first few digits of the ID number. Example: B= 1.1.1 then D= Imaging and E= T.P. B= ...