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

**1**

vote

**0**answers

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

**0**answers

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

**2**answers

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

**2**answers

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

**1**answer

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

**2**answers

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

**1**answer

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

**0**answers

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

**0**answers

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

**1**answer

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

**2**answers

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

**0**answers

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

**3**answers

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

**2**answers

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

**4**answers

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

**0**answers

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

**1**answer

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

**1**answer

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

**0**answers

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

**0**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**0**answers

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

**1**answer

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

**0**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**0**answers

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

**0**answers

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

**2**answers

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

**2**answers

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

**1**answer

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