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

learn more… | top users | synonyms (5)

0
votes
3answers
30 views

Excel function determine if a cell is visible

In conditional formatting I want to detect if the row above the current cell is hidden or visible. How can I detect if a cell is visible or not? The only hack I can think of is: Create a column ...
1
vote
1answer
33 views

How to drag fill series in excel to adjust sequencing without removing the hyperlinks attached to cells?

I have a range of cells in a column in excel, that have some number in them along with text. For Example: TA_47 TA_48 TA_49 TA_50 TA_51 TA_52 TA_53 TA_54 TA_55 TA_56 TA_57 TA_58 TA_59 TA_60 Now I ...
2
votes
1answer
28 views

How to count number of distinct values containing specific text

I'm not sure if I've phrased that question well so I'll elaborate. I have 160 lines of file numbers, many of which are duplicates. In each file number there is a value that determines the year the ...
6
votes
3answers
10k views

In Excel how can I sum all the numbers above the current cell?

I want to have a column in Excel that consists of a header, a bunch of numbers, and then have the sum of those numbers at the bottom. I'd like the sum to adapt to the insertion of new numbers above ...
0
votes
1answer
27 views

IF statement error message in Excel

The following formula is returning a #VALUE! error. =IF(L$3<$H11,4*5,(IF("<0",0,(5-(L$3-$H11))*4))) Why is it returning an error? I know that the error is coming from somewhere in the second ...
2
votes
1answer
21 views

Excel Filter and join between multiple tables

I'm reasonable user of Excel (definitely not very skilled though) - I'm using Excel 2013. I'd like some assistance on how to achieve what I need on table controls. I've created three tables. ...
2
votes
2answers
524 views

Excel: Select the cell to the left that is greater than/less than some value

I have a data entry form that is used to input data from image analysis. I take segments of images of membranes in ImageJ which I then paste the intensity values into an Excel spreadsheet. A graph ...
0
votes
2answers
62 views

How to calculate with Microsoft Excel the correct amount of minutes?

64 people receive services 105 minutes per day. How can i use Excel to calculate the total minutes of service per day and then multiply by 7, so i have the total amount of minutes per week, and then ...
0
votes
1answer
95 views

How to have excel paste a value in a specific cell according to another data table

I'm working with excel 2010, I have an excel sheet with two tables. The primary table contains a calender and each calender day has a specific numeric value in the cell below the date. The numeric ...
-1
votes
1answer
26 views

Excel data validation: restrict to 10, 20 etc

I am trying to use Data Validation in Excel 2010. How can I restrict entry into a cell to numbers divisible by 10?
0
votes
2answers
79 views

How to compare two columns with other two columns for duplicates in Excel

I need to compare columns C and D with A and B and only if exist same combination of two cells in one row. Hope the image below will explain the situation better.
0
votes
1answer
50 views

How can excel take two seperate cells with comma seperated data and pair them up?

I have looked all over for an answer to this and seem to be stuck fast. I have something like this: Cell B1: 01/01/2014,01/02/2014, 01/03/2014, 01/04/2014, 01/05/2014, 01/06/2014, 01/07/2014, ...
2
votes
1answer
53 views

Is it possible to automatically add cells from a new sheet to a formula in Excel?

I would like to make a new financial spreadsheet for tracking expenses. Since the expense sheets are created as and when I cannot pre-generate a spreadsheet to populate with formulae. I have a summary ...
1
vote
1answer
32 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 ...
0
votes
2answers
80 views

Excel If formula

I am trying to run a simple formula in Excel: =IF(V3>419, "Pass", IF(V3>459, "Merit", IF(V3>499, "Dist", IF(V3>519, "Dist*","Fail")))) I have 2 columns; Overall and Grade Overall ...
0
votes
1answer
1k 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 ...
0
votes
1answer
32 views

Excel assigning different dollar values and adding them

I am creating a spreadsheet and I want to assign different dollar values and add them together. For example I want B to represent 10.00 per hour for each hour I type in then I want C to represent ...
0
votes
3answers
114 views

Saying in Excel: IF this is true, then show me the cell two below

Hy there, i am trying to create an automatic invoice and probably i am making my life much more complicated than i should. here is my problem... i have these tables (short version) Name Street Place ...
3
votes
3answers
101 views

Automating a choosing process in Excel

The title says practically nothing, so I'll try to explain. I have some calculation which is based upon some input parameters, doesn't matter now. Out of it (the calculation) I get a number, let's ...
-1
votes
2answers
209 views

Simple Line Graph in Excel

How can I make a graph with this data: Club distance club speed 9 iron 85.8 71.7 9 iron 89.8 68.6 9 iron 85.2 69 5 iron 132.5 73.6 5 iron 131.3 ...
1
vote
1answer
31 views

Excel Deleting duplicate entries

I want to delete duplicate entries in the following table: The problem is that I only want to delete the duplicates in the left hand column, and the right hand column entries must stay with their ...
0
votes
2answers
29 views

How to parse file datetime in Excel

I am trying to convert a text string like this - Dec 15 21:44 into a date/time format in excel (Polish version). I tried =DATEVALUE function but without success.
0
votes
1answer
2k 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
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
47 views

Formatting cells in EXCEL based on real world date

I am trying to format cells in EXCEL 2007 based on the real world date for due dates on my bills. I know how to do this using Open Office, however EXCEL does not use the same conditional formatting ...
-1
votes
2answers
66 views

Excel possible to fill two cells with one if-formula

I have searched on the internet to look if this is possible : IF(logical test ; true value ; false value ) This is what i try to make for example A2 =IF( B2 = 20 ; "True" & give C2 value ...
2
votes
3answers
153 views

Convert Excel data

I've made a survey in Sharepoint and want to convert the results into something useful in Excel 2003. My problem is the format the responses are in for some questions. An example of two responses ...
2
votes
1answer
376 views

Can I do a Multiple Regression with Excel?

I have performance data for 3 years for about 50K employees. Each employee has 3 ratings associated with them. I would like to create an equation that will predict (or try to predict) the future ...
1
vote
1answer
269 views

How can I reference Excel cells by searching/ matching a cell value on their row?

I've trying to make a simple task list in Microsoft Excel 2012: Task# Parent Task# Task Description Days Due Date 1 Catch the llama 1 2 1 Shave the llama 1 ...
0
votes
2answers
38 views

Drag the cell to create an array of Roman numbers

I need to create an array of cells of roman numbers. So I placed my entry like this cell A1: I cell A2: II cell A3: III cell A4: IV I selected the entries and dragged the corner down, hoping that ...
2
votes
4answers
11k views

Merging columns in Excel with “;” separator

I'm new here and I hope to seek for your assistance on converting data format of my excel to csv. This is my raw data in excel in different row and cells: A B C D E F 1 2 3 4 5 6 ...
1
vote
1answer
93 views

How to cross data from 2 excel sheets

I'm doing a small internship at a local library. My mentor requested my help to a do lenghty job, but it has to be done over the week.. So, this is what he requested me: to look up in 2 different ...
1
vote
1answer
964 views

Filtering by currency in Excel

I have this situation in Microsoft Excel: Cells are formatted by using "Format Cells" and setting the Currency manually for each cell. So, they are not simple strings. I would to sum filtering by ...
1
vote
2answers
19k views

Excel Formula To Get First Non-Zero Value in Row and Return Column Header

I am attempting to find a formula where I can look at a column range within a row and return the column header in that row where the first non-zero value occurs, moving left to right. Below is a ...
0
votes
1answer
12 views

How to add up Column B based on whether Column A is a duplicate value?

I have two columns, one with names and the other filled with '1'. Column A has many duplicates in it - this is intended - what I need is a formula to add the 1s in Column B if the corresponding values ...
-1
votes
0answers
15 views

Displaying data from another tab

I run a daily report where I need to display errors on a roster of our associates. I have one tab ('Roster') with the complete roster and a formula that checks to make sure that the shifts are correct ...
0
votes
1answer
21 views

Matching and returning values to populate column

I have a workbook (Excel 2007) with two sheets. Sheet1 has more than 8,000 employees and their associated unit numbers, which is a 6-digit ID. I am trying to populate Sheet1!C:C with a corresponding ...
36
votes
2answers
2k views

What does “A1:A4^{1,2,3}” mean in an Excel formula?

What does the caret operand in Excel do to a range when it is followed by some other numbers in brackets? For example lets say we have the following table: A B C 1 1.5 0 0 2 ...
0
votes
1answer
22 views

Format row that is different than the preceding VISIBLE row

I have an Excel sheet as a database, where each row has a category, and are always sorted by category. Category Item 1 AAA jjj 2 AAA kkk 3 AAA lll 4 ...
0
votes
1answer
17 views

Automated Excel Sheet Import from Downloaded Spreadsheet

At work we have a data storage system where we keep information about equipment we've ordered. I want to take select columns from this data and put them in another sheet that is already formatted with ...
0
votes
0answers
10 views

Find a specific name in a column, bring in the value in the corresponding row, in the column I designate in another worksheet

So I have a worksheet that people will go into and put their name down to sign up for a meeting for a specific date. In a separate sheet, I have their names and I manually input the days they signed ...
0
votes
1answer
39 views

SUM IF FREQUENCY MATCH

I'm trying to add another criteria for the below formula. On the Payroll Data tab I have column H that has hours in it, some of those hours may be negative so I don't it to count those rows with ...
2
votes
2answers
8k views

What's the difference between “vlookup” and “lookup” in Excel?

What is the main basic difference between VLOOKUP and LOOKUP in Excel? I read the main point is that you can specify a range of columns in VLOOKUP and then pick one to return the value. However, it ...
4
votes
1answer
2k views

Linking Excel 2013 cell to Word 2013 table cell adds unwanted line breaks

I have a problem where I am trying to copy an excel cell that contains content using a formula, into a word document table cell and link them such that the formatting is used in word. What happens is ...
1
vote
2answers
34 views

Excel Convert the correct Date type

I'm currently following the date type dd/mm/yyyy i need to have another row showing the month in mmm type. E.g. From 13/09/2014 to SEP My formula is =Text(A2, "mmm") which is the correct formula, ...
0
votes
1answer
3k views

Excel - Displaying a value in a cell dependent on another cell having text

I have an excel file with 2 sheets. The first sheet is a user input sheet with cells that are properly formatted for us. The second sheet pulls the raw data into table format so that we can inport ...
0
votes
4answers
36 views

Getting last cell in row with data and corresponding data in column A in Excel

I have a spreadsheet that in column A lists dates. Column B may or may not have data in each cell. The question I have is when I grab the last cell with data in column B, how can I also grab the ...
0
votes
2answers
44 views

Using COUNTIFS for a series of values at once

Working a step higher then COUNTIFS, I appose a challenge to write a formula without VBA code. The basic data is combined from 1000s of rows with: Column A: rows with values from 1 to 3 Column B: ...
1
vote
2answers
31 views

Excel '10 Formula to find & sum a row based on the intersection of 2 data points. (ie. Name(row), Month(column)

I need an Excel 2010 array formula that will take 2 given data points, say a "Name" (row) and "Month" (column), and return the sum of a row based upon the intersection of these two points within a ...
0
votes
0answers
24 views

Prevent Excel from changing formula of last row of table when adding a new row

I have a table with columns named "date", "cost" and "daily". "Date" is a date, "cost" is a number and "daily" is a formula calculating the total cost of each day. The formula looks like this: ...