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

**0**

votes

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**0**answers

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

**2**answers

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

**1**answer

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

**0**answers

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

**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

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

**1**answer

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

**2**answers

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

**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

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

**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

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

**4**answers

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

**0**answers

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

**1**answer

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

**1**answer

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

**0**answers

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

**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

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

**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

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

**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

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

**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

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

**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

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

**1**answer

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

**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

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

**1**answer

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

**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, ...