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

learn more… | top users | synonyms (3)

0
votes
1answer
123 views

How to do a sliding window in an excel table?

I have a table with a date column and a value column like this |A |B 1 |date |value 2 |2013-12-10 |1 3 |2013-12-10 |3 4 |2013-12-09 |2 5 |2013-12-04 |5 6 |2013-12-02 |2 Dates can ...
0
votes
0answers
62 views

Is there something better than VLOOKUP?

I'm wondering if there is something like a VLOOKUP for multiple matches in one column. So, for example, if I have the following: Alfred red Jone orange Christina blue Danielle blue ...
1
vote
1answer
41 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
2answers
57 views

What functions can I use to track subscriptions?

I am struggling to make a spreadsheet which will help me take control of member's subscription fees. Fees are paid per month. The spreadsheet has 12 horizontal cells, each one for a different ...
0
votes
2answers
218 views

VLOOKUP with multiple criteria

I have a workbook with 2 sheets: spg and data From the data sheet I wish to do a vlookup in the spg sheet, based on the value of multiple columns in spg. I can do a =VLOOKUP(H5,spg!$D:$I,6,0) but ...
0
votes
1answer
28 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 ...
0
votes
1answer
40 views

multiple IF Calculation excel

I currently have this formula in Excel: =IF(G1+I1>E77,H1) Sometimes I want it to be like this: =IF(C1+E1>G1,D1) I need both these formulas in the same cell but there will only ever be a ...
0
votes
2answers
34 views

Force a one-dimensional array into a constant in Excel

I have a condition in an Excel IF-statement as follows: INDIRECT(ADDRESS(ROW(ME3),MATCH("PremiumWaiver",$1:$1,0)))=1 where INDIRECT(ADDRESS(ROW(ME3),MATCH("PremiumWaiver",$1:$1,0))) eveluates to ...
0
votes
1answer
62 views

How can I find max value and display text next to it in Excel?

I need a formula that will find the highest number in a column and then display the text in the cell immediately to the left of that number, i.e. find the highest grade and display the name of the ...
0
votes
2answers
197 views

Comparing two columns in different Excel spreadsheets

I have two Excel spreadsheets. In the first spreadsheet I have two columns. Column A has 240 Accounts entries and Column B has Values for each account entry. Once again Spreadsheet 2 has its data in ...
0
votes
1answer
29 views

Replace cells in spreadsheet with cells from different spreadsheet in Excel 2010

I have a spreadsheet with phone numbers. 347-286-3341 347-286-3342 347-286-3343 347-286-3344 347-286-3345 etc... I have another spreadsheet with phone numbers and corresponding names. ...
0
votes
3answers
48 views

Excel Using Reference for Worksheet names [duplicate]

I've looked around to try and resolve this, however I am unfamiliar with the coding. Here's my line of code, ='[WorkBook]BOM'!G6 What I want to do is make the Workbook call to a cell so I can mass ...
0
votes
3answers
46 views

Excel counta function fixed reach [duplicate]

I have a planning sheet in Microsoft Excel 2010 for my company. I have made a screenshot of the planning sheet which you can see below. In the column CAP the planner can see how much capacity that ...
0
votes
1answer
23 views

Summarizing the sales data to monthly

I'm having the following table of data: Customer 14-Sep-12 30-Sep-12 1-oct-12 28-Nov-12 5-Nov-12 ASTDU 4718.00 3885.00 1680.00 3058.00 2147.00 BJURT 3646.00 4557.00 ...
0
votes
0answers
45 views

Merge CSV files with Excel

Every day, I run a report that generates a .csv file of items that need to be purchased for the company that I work for. The fields look something like this: ...
1
vote
0answers
59 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 ...
0
votes
1answer
55 views

How to change a row of cells on excel 2010 depending on different cell data

Can anyone help me with a problem I'm having on excel 2010 please? I am trying to change the colour of part of a row of cells when certain data is input. For example I want cells A13 to G13 to ...
0
votes
1answer
429 views

Check if a Row Range Contains another Row Range values in Excel

I have an Excel file that contains two different columns having Customer Numbers (let's say CustomerA and CustomerB columns). What I would like to do to check which of the CustomerA values match the ...
1
vote
2answers
62 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 ...
0
votes
0answers
46 views

Excel 2010 - IF Function to transpose data from other sheets

I have Sheet 1 with a table with Columns A - T (first row being headings) containing aphabetical and numerical values. Column Q has a "Yes" or "No" value . The Rows contain the customer data for each ...
3
votes
2answers
936 views

Excel-Count number of visible cells containing certain string

I have a sheet in which I first apply a filter to a certain column to show "Unique records only". Now I have a column which contains, for e.g., values like: Applepie Applesauce Crabapple Banana Mango ...
0
votes
1answer
53 views

how do i extract cells from multiple spreadsheets all with same format into new master file

I have 300 excel files each with the exact same format - 4 worksheets in each file with same column/row headers. I am trying to extract information from select cells of each of these 4 worksheets (for ...
0
votes
1answer
50 views

Dynamically updated floorplan/asset register in Excel

I'd like to create a combined asset register and office floorplan in Excel 2010. Ideally it would have one data sheet with rows containing: ethernet socket number, room number, building, user name ...
0
votes
1answer
33 views

Unique values in multiple columns

I have a spreadsheet with the following:- Prod No Store 1 $ Store 1 Qty Store 2 Sale Store 2 Qty etc A 4.00 1 7.50 2 B 0 ...
1
vote
1answer
34 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= ...
1
vote
2answers
58 views

Formula to search highest value related to a near match reference

I have a list of neutral references which change as per the date so What I am looking for is a formula that will search Product and return the latest date. e.g., A B ...
1
vote
2answers
71 views

I need to find the last Space in a XLS Cell

I have an Excel sheet with text values, e.g. in A1: "This is the text". I need to find the position of the last space character. Is there an excel formula that will give me the result I want? I have ...
2
votes
2answers
101 views

How to identify if a cell contains any of the values in a column

My question is the opposite (inverse?) of How to identify cells in Column A that contain any of the values in Column B. Specifically: I have a list of 19,000 first names and 10,000 passwords. I'm ...
1
vote
1answer
59 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 ...
0
votes
2answers
95 views

Excel saves a file in .csv but it keeps the columns and the rows as they were in the original sheet

I have a problem converting an Excel sheet in a *.csv file. I've searched all day for solutions, but nothing seems to work. After I select the sheet and save it as "CSV(Comma delimited)", pressing OK ...
0
votes
2answers
31 views

EXCEL: IF function only working if Value is TRUE and remitting a #NA if the Value is FALSE: Apple Mac Air - Microsoft Office 2011

Can anyone help me with the following formula? Basically it works when the condition is TRUE but gives me an error when the condition is FALSE. I would like the cell to remit the value "0" if the ...
0
votes
1answer
27 views

Sum a different range for each customer line on a table in Excel

I have a problem I am trying to solve and did not found similar issues in the website. I have the following issue: each customer receives the software upgrade at a different month I want to see ...
0
votes
0answers
40 views

Excel: IF function avoids circular reference by checking for a formula

In Excel, I'm trying to write an IF function which will avoid a circular reference. If cell X has a formula referencing this cell Y, display a value. Else if cell X has a value, divide that value by X ...
0
votes
1answer
35 views

Pick data from another sheet in specific row intervals

I have a sheet that picks values from a different sheet (raw data sheet). Best to show through an example. Col1 Row1 [anothersheet.xlsx]Sheet1!$B$14 Row2 ...
1
vote
0answers
38 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
votes
1answer
25 views

Need Macro to perform formula based on cell not number

I am working on a budget workbook and in column I (I5-I1135) I have numbers that I need to upon prompt (such as CTRL C) have the numbers divide by 10 then multiply by 12.. Example: cell I5 has ...
2
votes
1answer
32 views

Vlookup not registering range changes in calculation

Typing the first vlookup yields the correct result. When I pull down the cell reference to use the same vlookup on other cells, the vlookup recognizes that the formula has changed, but still delivers ...
0
votes
1answer
32 views

Calculating units of time in excel in order to multiply

I would like a formula that can multiply a unit of time, e,g if I enter 1hr and 20 min as 1.20 and multiply by10 i should have 13 hrs 20min displayed as 13.20 but what I would get currently is 11.
0
votes
1answer
191 views

Excel 2007 adding a percentage increase

Value = 20 is a constant number Percentage increase to "Value" in levels of 2%, 4%, 5% up to 20% I would like to enter in a cell the LEVEL of the object and it return the VALUE + the percentage ...
0
votes
1answer
75 views

How to get list of cells that match criteria in Excel 2013

I have three columns in an Excel 2013 spreadsheet. One is an ID, one is a reference column, and one is a referenced by column. Some rows reference other rows, and some rows are referenced by other ...
0
votes
2answers
45 views

EXCEL: If function and refering to cell containing operator

I don't know how to handle a cell which contain an operator and number. Let's say I have 3 rows: 2 0,6 <0,1 In column B I have IF function which checks the number if it's greater or less than ...
2
votes
1answer
95 views

excel vba: to export multiple excel sheet into its designated jpg file

I am working on an excel vba command to export multiple excel sheet to its designated jpg file. I have 16 worksheet in one excel workbook and it would be best to export each sheet into jpg format. ...
0
votes
1answer
171 views

Excel formulas for budgeting sheet (or VBA code)

I'm (an amateur) and I'm creating a sheet to help me with Budgeting. I've got bills that I can enter, but that come at a variety of different intervals. I'd love it if I could enter the total from ...
0
votes
2answers
750 views

In Excel, how do I copy a formula down or over, skipping cells?

Ex: =A1+A2 next cell down A3+A4 next cell down A5+A6, not A1+A2, A2+A3, A3+A4. I am trying to net cells, some are exact duplicates, some are not
0
votes
1answer
94 views

Globe Software Atlas Error: AtlasTable function “Failed to build Atlas forumla”

We have an Atlas Template (Excel document) containing a number of formulas. One of these formulas is causing the exception message: Failed to build Atlas formula: =AtlasTable("Production ...
2
votes
2answers
59 views

How to deal with lots of brackets in a formula?

Say, I have a formula like this (in LaTeX or Maple or other text system): Result: ...
0
votes
1answer
137 views

Two related cells: give a value in one, calculate the other, and vice versa? [duplicate]

How can I have a cell that uses the literal value written into it, or calculates its value when no literal value was given? For example: I have two columns: column B with a price including VAT, and ...
0
votes
2answers
168 views

Excel 2010 line chart; limiting data points to fewer than the max

In Excel 2010, how do I limit the number of data points on a line graph to the latest (or last) 4 data? I update the spreadsheet often, but only the latest data is graphed and reported.
1
vote
1answer
54 views

Excel 2010 Recordset - How to insert a sequential number into an extracted ODBC recordset based on unique identifer

ColumnA ColumnB EnquiryNo VisitNo 1804 0 1804 0 1804 0 1805 0 1806 0 1807 0 1807 0 Hope you can help as I am stumped! My excel ...
0
votes
2answers
277 views

Excel-Overcoming #VALUE error with FIND function

In cell A1 I have entered "Apple". In B2 I enter the formula =FIND("Apple",A:A). However I keep getting #VALUE error. Can anyone explain this and how do I overcome it.