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

learn more… | top users | synonyms (5)

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
43 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
20 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
133 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
23 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
22 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
378 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
45 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
41 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
42 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
19 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
42 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
44 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
42 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
33 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
51 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
55 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
29 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
27 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, ...
1
vote
1answer
60 views

Get last occurrence in a list in Excel

I have a list in sheet1 and want to get last occurrence of each product right in front of it in bank page. Here is the formula I'm working with: ...
0
votes
0answers
63 views

Using IF statement to display an image

I have a problem displaying a picture (bit image) in Excel after checking for certain conditions in an IF statement. I also have a problem with using arrays in an IF statement. For the below IF ...
1
vote
1answer
33 views

Excel: Can I do sumifs with calculations?

I am doing some recalculations in Excel on a long list to prepare it for statistical analysis in other programs and I am having troubles with one thing I want to do. I have a list of some variables, ...
0
votes
0answers
15 views

Excel 2010/Hancom Automate the inserting process across multiple sheets

I'm in the process of creating a complicated excel spreadsheet which is where my knowledge runs thin. It's currently consisting of 17 sheets. I currently have 10 pages which require a daily entry of ...
0
votes
0answers
14 views

Microsoft Excel 2007 for OC

I have a column with multiples of different answers, like the following: Country Germany US Italy Italy Germany Germany Germany US Italy US I want to: list how many different countries are in ...
2
votes
2answers
98 views

How to round down to the nearest 50?

I want to round down to the nearest multiple of 50 in Excel. So if cell A1 = 318, I want to round it down in cell B1 to 300, and if A1 = 367, round down to 350. Is this possible? If so how?
0
votes
2answers
50 views

LibreOffice Calc IF Function returns True when should return cell

I have been successfully using the IF function up until the last few days. I used this statement to return one of 2 values depending on whether or not an item is shaped: ...
-1
votes
1answer
28 views

Display the last value in a data sheet of duplicates

I have a list of duplicate values and I need to display the last value in the data sheet. In the example below I need to display that the code ER16 has a qtyleft (quantity left) of 4