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

learn more… | top users | synonyms (5)

0
votes
1answer
26 views

Attendace calculation from when the person started

I have a list of young people, I want to calculate their individual attendance to the programme, from the date they joined. NOTE not everyone joined at the same time. [ I can just sum from the date ...
0
votes
1answer
27 views

Need some help showing the difference between two dates

So I am trying to write a function that shows the difference between two dates. I'm currently using the following formula: =DATEDIF(D2,E2,"y")&" Years, "&DATEDIF(D2,E2,"ym")&" Months, ...
1
vote
1answer
36 views

Excel condition comparing two cells then fetching data

I'm relearning Microsoft Excel after moving to Numbers a couple years back. I have an idea in mind that I would like to put in practice, but having a rather hard time trying to do. I'm not sure if ...
0
votes
1answer
35 views

Insert an image into a excel comment [duplicate]

I follow the steps from How do I load external images into Excel? but when inserting the function appears an error: Any idea what is causing this error?
0
votes
0answers
24 views

Can you use unit of measurement combinations with Excel's CONVERT function, and if so, how?

I've been wanting to experiment more with Excel's CONVERT function and came across a useful web page that listed the proper syntax required for it. The url to the page is below: ...
1
vote
2answers
42 views

Auto (drag)incrementing only one cell value in Excel

For example, I have one table with values: 14.10% 1.60% 2.80% and I have a cell with value I want to multiply each table row by, eg L15=300 I want to multiply every value by 300, so I put ...
1
vote
1answer
19 views

How do I add text in a cell based on information from a separate cell in Microsoft Excel?

Column A is a list of city names, and I want to add a column B for a list of the counties that correspond to the cities. Example: Charlotte Hall, Piney Point, and Lexington Park are all located in ...
0
votes
0answers
29 views

What is the most efficient way to standardize time formats and find time durations exceeding specific lengths using Excel 2007?

THE SCENARIO: Let’s say somebody hands you a spreadsheet containing a column for the time duration of 416,000 videos. When the information was entered, it was entered into an Excel 2007 spreadsheet ...
1
vote
0answers
25 views

Calculating averages for every n rows with missing data

I have a column with 30-min averages, and I'd link to make a new column with 24-hour averages. I found this formula very useful: ...
0
votes
0answers
34 views

Formula to find difference/change between 3 or more fields?

Probably a dumb question, but I'm looking to find the difference between 3 values in excel. +-----------+-----------+-----------+ | Nov. 2015 | Dec. 2015 | Jan. 2016 | ...
0
votes
2answers
27 views

Excel, possible array? Not sure how to calculate conditional steps

I have two rows of numbers, 1 and 2, they go from G-Z. I want to calculate the following: (G2-G1, if greater than zero) add to... (H2-H1, if greater than zero) add to... (I2-I1, if greater than zero) ...
1
vote
2answers
45 views

How to calculate the sum of row heights for filtered rows

Background: I have a sheet that pulls data from a database to create product labels (using index-match lookup formulas). The sheet has a macro I recorded which filters out the blank rows and autofits ...
0
votes
1answer
21 views

How to fill a cell according to the type of number

Currently I have 2 suppliers with barcode scanning system in excel. Since I scan them in Column A in excel (with auto-enter to the next cell), I would like to know if it is possible to have ...
2
votes
2answers
144 views

How to insert the =now() function in a cell automatically when I write in another and hit enter [duplicate]

I would like to know how to: Automatically have the =now() function in the column B of Excel, only displayed in each cell (B1, B2, .. Bν) whenever I write something in column A in the respective ...
0
votes
1answer
35 views

MS Excel Dynamic Cell

How can I make excel run like a program such that a particular cell in sheet 1 changes its value according to each successive row operation in Sheet 2. Basically I want to know if a cell can have ...
0
votes
0answers
13 views

How can I nest logical functions on LibreOffice?

This is my test formula: AND($E2<=10;OR($C2>=90;$D2>=90)) but nothing changes on my document. Just the OR/AND formula behaves fine, like in: OR($C2>=90;$D2>=90) Is this the ...
0
votes
0answers
17 views

Excel 2007 backup spreadsheet without formulas

I have a spreadsheet report that refers to multiple other workbooks to update values. Currently no macro is used, spreadsheet depends on cell references to update the values. I need to do a daily ...
1
vote
1answer
38 views

How do I make a formula that updates when I enter a new value?

This question is hard to explain, but here I go. So I have this column of numbers, and I have another cell that I want to put a formula in. I want the formula to subtract the latest value in the ...
1
vote
2answers
34 views

How do I move some data in a column to a new column

I am looking for a formula to split data into new columns. I can do it simply but I want to categorize it. I.E. I have one column with degree information for each person like: BS 1990; MS 1991; PHD ...
0
votes
0answers
26 views

Excel - using info from one sheet and populating it on another sheet

I have a summary spreadsheet where the 1st column has the date and the next columns are titled with different types of work. On the summary spreadsheet it shows how much production was done on each ...
2
votes
3answers
76 views

Why do some Excel users use SUM() when it is not needed?

It's perfectly valid syntax to write =A1/A2 as well as quick to read and write (I'm assuming for the sake of argument that A1 and A2 are numbers, where A2 is non-zero). What I don't understand is ...
0
votes
2answers
24 views

Excel Formula to return the average of the difference between two ranges of cells

Is there any formula that would allow me to calculate the average of the transit periods (Arrival - Departure) of these two ranges of data, without the need of including an extra column? I've tried ...
3
votes
4answers
379 views

Find largest value, Nth largest and others before it? (while ignoring duplicates) [duplicate]

So I have a column say A that looks like this: I 15 0 3 15 M 8 0 8 21 Q 0 5 0 0 I can find the largest value with =MAX(A1:A100) I can find the smallest value with =SMALL(A1:A100,1) How do i find ...
1
vote
0answers
48 views

How to hide zero values from an excel 3d surface graph?

I am currently trying to put together a 3D surface graph in Excel 2013. Currently, it looks like this: The area on the left is the proper chart - displaying actual data I have. The area on the ...
1
vote
1answer
42 views

Assigning rank in OpenOffice (or LibreOffice)

In OpenOffice (or LibreOffice), is there a way to rank rows? For example, let's say you have a spreadsheet where cell A1=20, A2=30, and A3=10. I want to B1 to get a value of 2, B2 to get a value of ...
2
votes
1answer
43 views

VLOOKUP and IF in multiple excel sheets

I have two excel sheets, as below, in a workbook Sheet 1 & 2: Below is the relation of fields between the two sheets EID is same as EmID DepID is same as DependentID DepDOB is same as ...
0
votes
0answers
20 views

Validating a timestamp

I'm trying to write a formula in Excel that allows me to look into the first column, identify a timestamp of today and return the contents of an adjacent cell. I've tried the basic formula's, but ...
0
votes
0answers
53 views

How to combine formulas in excel with a possible macro?

This script can only delete files in a proper Windows file path name format. If a file is embedded within another file, the script cannot delete the embedded file, it can only delete the overall ...
0
votes
1answer
39 views

Create a drop down to specify multipliers in excel

I want to make an excel sheet where people can input how they spend their time. However, I want to give them different dimensions in which they can spend their time, for example "Hours / day" or "Days ...
2
votes
1answer
17 views

Calculate % of filtered values from all values in Excel Pivot Table

I have this pivot table that currently shows how many students failed in a particular subject in a particular academic year. That is, it is now filtered by Pass_Status Fail(which can be either Pass or ...
1
vote
1answer
28 views

How can I filter multiple numbers?

Refer to the image below: If I want to filter the Column A with what I have in Column C, how do I do it ? What I am saying is I only want to see the grades of students who made an A, and those are ...
2
votes
2answers
27 views

How to complete an entire column (non-consistent) based on previous value in Excel?

So let's say I have the followings values: A2=a, A5=b, A6=c, A10=d Basically I want a complete down the previous values as it follows: A2:A4=a,A5=b, A6:A9=c,A10:A99999=d I tried this: I ...
0
votes
1answer
22 views

Find Kth match result in an array?

A while back i was able to find a method to locate errors in my documents. I haven't been able to re-purpose it to search for keywords instead of errors - could use some help with that. Heres what I ...
0
votes
0answers
43 views

SharePoint calculated column to check if the cells of the row have value, if any of the cells have value then take up that value

I need a calculated column formula for following condition: There are three columns "Column1", "Column2" & "Column3" in sharepoint list. I want to check if any of these column have value in it. ...
1
vote
1answer
16 views

check if cell ref is within a range

In a formula I'm using OFFSET to pull a value from another cell at a user defined offset: OFFSET([@[last seen]],-$B$1,0) I'd like to make sure that the resulting offset doesn't fall outside the ...
0
votes
0answers
45 views

True and False in a Pivot Table not findable in SUMIFS criteria range in Excel 2013

I am looking for help with using True and False as search criteria in SUMIFS and using Pivot Tables I am trying to sum revenue from products sold based on whether or not they were on Clearance when ...
0
votes
1answer
14 views

Excel 2007 I need to be able to enter data in a cell on one sheet and have that populate on a different sheet

Thank you in advance, I am attempting to have a workbook that on the 1st sheet has cells that I need to populate with the data I enter on the 2nd sheet. There is no calculations that will be performed ...
0
votes
1answer
85 views

Want to return blank cell if any cell in the range has a numeric value

I’m getting #VALUE errors because there are blank cells.  Is there a way get rid of this?  Also is there a way I can make it so that any numeric value in the range of cells will return ""? ...
0
votes
1answer
43 views

Break even analysis using Excel

In Excel, I have sales data regarding a particular product. I want to figure out when I am going to break even. I.E., given the sales figures for each month, during which month did the total exceed a ...
1
vote
1answer
28 views

Embedding the value from one cell within text in another cell

Here's an image of what I want to accomplish. The "function" is shown as pseudo-code in column E, and the desired result in column F. I want to include the values from columns B and D within the ...
0
votes
2answers
35 views

MS Word field code error “!Syntax Error, :”

I am trying to modify a reference to only show a certain number of characters of text on the left of the string. To test my formula, I used the following field text: { =LEFT( "BLAH12345" , 3 ) } ...
0
votes
2answers
38 views

How to put comma into digits like eastern (especially Indian) style by using a formula in M S Excel?

Friends I want to put comma automatically into the digits like this- 10,00,000 Or 1,00,000 For this I want to use a formula. I also know that I can do this by format cell>number>use 1000 separator ...
0
votes
1answer
16 views

Is there any way to compare two excel formulas?

I have found some tools that compares two excel files, but the problem is that they check only the cell result and not it's formula. For example: A1 = PRODUCT(B1,C1) which gives me a result of 0. B1 ...
0
votes
1answer
26 views

Converting many columns to a column of headers and a column of all the data in excel with worksheet functions

given N number of columns with a header and a list underneath I would like to convert this to a database format with the header in the first column and the items of the list in the second. Column1 ...
2
votes
1answer
53 views

Why doesn't my SUMIF formula work?

I have a timetracker sheet that I use at work. I want to calculate how much time I've spent on each task category. For some reason it's not working. As seen below, they're all 0's. ...
0
votes
1answer
56 views

Excel formula to associate category/subcategory pairs and generate list

I have five columns, which determine an article's ID and the categories with which the article is associated. An example of the data as below: article_id category_id subcategory_id ...
0
votes
1answer
57 views

how can I lock the cursor to specific cell and focus to only a specific cell using VBA code in Excel

I have a list of names in a Sheet Column A. A --- Robby Harry Nick In any cell when I enter a name and click on other cell. If the entered name not present in the list then the focus shouldn't ...
1
vote
1answer
30 views

Microsoft Excel - Formula for Date change in one cell, changes different cells data

I am attempting to have an individual cell change other cell's values based on date. Realizing that my attempted formula is way off, I am hoping someone can assist me in correcting this. Here is ...
0
votes
1answer
28 views

Comparing 2 columns and add blanks in 2nd column

I have 2 columns where some cells have the same value. I would like to sort both columns and put blank cells to another column if it doesn't have duplication. Example: | Test1 | Test1 | | Test2 | ...
0
votes
1answer
41 views

Excel: Changing and updating a future value based on past occurrence and lookup in a table

I have a big dataset, dataset is in such a way that each time a customer is called a row will be created. So the customer Number is the unique ID here. Based on their current and past response type, ...