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

**4**

votes

**5**answers

4k views

### How to stop numbers converting to date format on copy paste in excel?

When i copy data from a site and paste in excel numbers are converting to date format
EXAMPLE: 2-1 converting to 1-feb how to stop this. i have so many records so cant do manually. is there any quick ...

**2**

votes

**4**answers

3k views

### Can one list everything that uses a particular cell in Excel 2010?

I've been tasked with making changes to an Excel 2010 spreadsheet I know little about.
One of the biggest changes the client wants is to remove numerous cells that are no longer needed by his ...

**2**

votes

**2**answers

1k views

### How does one find the definition of a function in Excel 2010?

I've been tasked with making changes to an Excel 2010 spreadsheet I know little about. I was pretty good with Excel about a decade ago, but I'm not really sure how to look-up the definitions of ...

**2**

votes

**1**answer

812 views

### Convert time to decimal equivalent it Excel / Emacs

I'm trying to create a chart of my times for my runs (e.g. 25:30, for 25 minutes and 30 seconds).
How could I convert this "analog" time to the decimal equivalent (e.g. 25.5 minutes)?
The problem is ...

**0**

votes

**2**answers

670 views

### Excel - Enter Qty of Hours as hh:mm And Perform Calculations on It

I guess this is two related questions in one question. I'm creating an Excel spreadsheet and I need to enter Qty of Time Spent in the format hh:mm in one cell and in another I'll be calculating the ...

**1**

vote

**3**answers

3k views

### How to map the details between Excel sheets?

I have an Excel worksheet with two sheets and two columns each with the below input:
sheet 1 data:
column1 column2
------- ...

**1**

vote

**4**answers

3k views

### Excel: struggling to use CF to highlight differences between columns in the same row

I have a list of vaules in column A and column B. I need to highlight where there is a difference between values that are in the same row.
I've used a simpe formula in B1: $A1<>$B1 (format bold). ...

**1**

vote

**3**answers

1k views

### Is there a free Powerpoint to PDF converter that preserves mathematical formulae?

I'm on a Mac. I tried using OpenOffice, but mathematical symbols often get mangled.

**2**

votes

**3**answers

2k views

### Excel notation that gives FIND the ability to look for multiple strings

So I've been working with a spreadsheet that has a list of schools names. I pull data from this spreadsheet to create a roster that limits the size of text in each cell. In order to get the school ...

**0**

votes

**1**answer

154 views

### Excel vlookup help

=IF(N101<>"",ROUND(VLOOKUP($N101,$I$30:$U$39,12,FALSE)/(1-(U$96+U$98)),0),0)
That is the current VLOOKUP in the spreadsheet that is returning the wrong data. I didn't write it, it was on a ...

**17**

votes

**7**answers

266k views

### How to compare two columns and find differences in excel

I have two columns in Excel that I want to compare and find the differences between them.
Suppose
Col A has 50 numbers i.e 0511234567
Col B has 100 numbers in the same format

**25**

votes

**10**answers

53k views

### Formula for updated currency exchange rates in Excel or other spreadsheets?

What kind of spreadsheets support a formula like the following:
=exchangeCurrency("USD"; "EUR"; 3000)
The result should be 3000 USD exchanged into Euros. Possibly a date could be provided for a ...

**2**

votes

**1**answer

971 views

### Split Address in Excel Cell

I have an Excel column where each cell contains the contents of an address. What I'd like to do is split THE FIRST TWO words into a their own separate columns.
For example:
123 South Bend Rd, Make ...

**6**

votes

**2**answers

9k views

### How do I insert formula in between sentences?

How do I insert formula in between sentences? For example, in A1 i want to write:
Welcome =C1
So =C1 will replaced with anything in C1 cell.
If in A1 I write Welcome, then in B2 I write =C1 then I ...

**3**

votes

**4**answers

17k views

### How to separate number by its decimal point in Excel/Calc?

I've got a number in a spreadsheet like so: 28.686279
When I open this sheet in either LibreOffice Calc or Microsoft Excel, is there a formula function that returns the "whole number", I.e. 28. Also, ...

**0**

votes

**1**answer

70 views

### Conditional formula with multiple and/or

I want a formula to do the following:
if C2 is >=55 and D2 is >=30 OR
C2 is >=60 and D2 is >=20 OR
C2 is >=62 and D2 is >=5, "yes" ,"no"

**2**

votes

**1**answer

796 views

### Random Excel bug when referencing a different sheet

I am getting a very strange error anytime I try to use a formula pointing to a cell in a different sheet from the active one (even as simple as "=Sheet2!A1"). This started happening all of a sudden, ...

**0**

votes

**1**answer

421 views

### Count the number of contiguous empty cells at the end of a row

I have rows of data, some cells are empty and some contain a number. I need to identify how many contiguous empty cells exist at the end of each row.
I've tried using a string of nested IF ...

**5**

votes

**2**answers

77 views

### Formatting Dates

I have the following formula:
=D56&" "&"("&D57&" Days"&")"
D56 has a date within the cell. I want to format the date like 5/5/2011 but I'm not sure how to do it.

**2**

votes

**1**answer

2k views

### Assigning a weighted value to a column

I'm working on a spreadsheet in Excel. Column A is student's name. Column B is a placement test score. Column C is a benchmark score. Column D is an effort score. Column E is for a TOTAL score. ...

**1**

vote

**1**answer

243 views

**3**

votes

**2**answers

3k views

### Non identical random numbers

I have the following formula in Excel (not vba):
=RANDBETWEEN(1,10)
Then I auto fill down 10 rows. How can I re-write the formula so the numbers don't repeat.

**5**

votes

**3**answers

415 views

### Excel: autofill formulas in lists

I have a rather huge Excel sheet where the first 20 columns contain input data, further 15 columns contain formulas which use the input data for calculations.
I frequently remove the entire input ...

**2**

votes

**2**answers

4k views

### Sum contents in column from starting cell on down without setting an explicit last cell index

I have an Excel spreadsheet and would like to sum the numbers in a column starting at one cell (say, B5) and continuing down to the last number in that column.
I know I could just enter a large ...

**5**

votes

**1**answer

3k views

### Shortcut to accept suggested formula in Excel

I'm working with a lot of formulas in Excel 2007 and I would like to know what shortcut key you need to press to accept the suggested formula.
When I type in =DEG the first suggested formula is ...

**1**

vote

**2**answers

2k views

### Compare two cells with different values in Excel

So I have two columns:
Values can be "Yes - Start", "Yes - End", "Yes - Both" and "No"
Values are the same as column 1
They don't match all the time, so I want to create an additional column that ...

**0**

votes

**1**answer

219 views

### Excel: is it possible to extract a superscript letter from a word?

I'm wondering if these is a way of using a formula to find a superscript letter, rather than just the last letter of a word.
I have lots of tabs, with nearly matching tables on each one showing up to ...

**3**

votes

**3**answers

15k views

### Subtracting cells only if they have a value in Excel

I want to subtract two cells in excel but only if both cells have a value. More specifiaclly in my case the formula in cell E2 is currently =D2-C2 but I only want this to be calculated when D2 has a ...

**2**

votes

**4**answers

682 views

**0**

votes

**2**answers

402 views

### Calculated Values from Formulas in Excel Cells

Please help me ruin my son's vacation! (I'm making him study math).
He has to do a huge amount of Geometry exercises, so I was thinking I'd use Excel to track progress: For each question he does I'd ...

**1**

vote

**2**answers

386 views

### How to get a number from sets of pairs in Excel

In Excel, I want to get the corresponding number for a given value in a set of numbers.
Given values are in disorder.
Example:
1 | 21
2 | 34
3 | 15
4 | 47
5 | 29
Here the minimum value is 15 & ...

**0**

votes

**1**answer

190 views

### How can I access a specific spreadsheet cell (from a list of them) using a formula?

Here's the situation: I have a list of numbers in a spreadsheet, all in a column. In another part of the spreadsheet, I'd like to refer back to the list, and get the value of a particular cell, based ...

**1**

vote

**1**answer

14k views

### Need a formula that looks at the first digit or letter in a cell

I have a column of numbers. Some begin with the digit 8 and the rest begin with other digits. I want to write a formula that will return "2" if the number in column A starts with 8, and "1" if the ...

**17**

votes

**5**answers

54k views

### How to create a formula for every row in a column in Google SpreadSheet?

I use three columns. A, B and C.
In column C I have a formula every row =A1*(1.6*B1) and then for the next row I have =A2*(1.6*B2) in C2.
How can I do so I don't have to type in the new formula in ...

**2**

votes

**1**answer

608 views

### Numbering grouped data in Excel

I have an Excel spreadsheet (2010) with data similar to this:
Dogs Brown Nice
Dogs White Nice
Dogs White Moody
Cats Black Nice
Cats Black Mean
Cats White Nice
Cats White ...

**2**

votes

**3**answers

1k views

### Excel / open/libre office formulae: number of full months left until date

I need a formula which equates to an integer value for the number months left until a set date (2011-10-16) based on the current date.
e.g.
on 2011-03-13 the answer would equate to 8
on 2011-03-17 ...

**3**

votes

**1**answer

3k views

### Excel Range with IF

Is there a function that will create a range (from a range) if they match values? Essentially, I'm looking for something like COUNTIF, that will return the cells that actually match my IF.
Ideally, ...

**1**

vote

**2**answers

196 views

### Seeking a formula to determine duplicate numbers in a spreadsheet row?

Is there a formula to let me count if there are duplicates showing up in a row?
If I have 3 to 6 columns of individual numbers, I'd like to know if any numbers are duplicated in any particular row?
...

**3**

votes

**1**answer

384 views

### Excel: How do I get a dynamic formula which can display a total of unique values in a range?

Example range:
A10=100,
A11=105,
A12=100,
A13=100,
A14=7,
A15=105.
The formula I need would display "3" only as there are only 3 unique values in this set. The spreadsheet will be updated ...

**8**

votes

**3**answers

17k views

### Excel function that evaluates a string as if it were a formula?

Suppose I have a text string like "11+5" or even "=11+5" stored in a cell. Is there a function in Excel that will allow me to actually evaluate that string as if it were a formula?
This would be ...

**1**

vote

**5**answers

17k views

### Sum if ONLY all the cells have a value?

I need to sum 9 cells of data, each one on a separate sheet, but always in the same location. I only want a figure returned when all the cells have data, even if it's a 0. But, if one of the cells is ...

**1**

vote

**2**answers

2k views

### How can I create a formula in Excel that uses DATES?

How do I subtract days from a cell that has dates?
For example, what's a formula that will take 3/2/2011 and return this value 2/28/2011?

**1**

vote

**1**answer

614 views

### Using Excel Linest function without ranges

Is it possible to use the Linest Function when my data is not in a continuous range? I've got my known Y's in J1, J4, J7 etc. and my known X's in A1, A4, A7...
linest(J1, J4, J7, A1, A4, A7) doesn't ...

**3**

votes

**1**answer

863 views

### Absolute reference all cell references in a Microsoft Excel formula?

For a Microsoft Formula like this:
=IF(Data!H5=3,0,IF(Data!H5<=Data!H19,3,IF(Data!H5<=Data!H20,2,IF(Data!H5<=Data!H21,1,0))))
Is it possible to mass convert all the relative cell ...

**0**

votes

**2**answers

3k views

### How to make a relative formula in a Microsoft Word table?

Example
2 2 =A1*B1
produces correct result, but when copied to a new row
the formula make static reference to Row1.
I tried this and it works
2 2 =PRODUCT(LEFT)
BUT must be able to ...

**2**

votes

**1**answer

765 views

### How to change a function/formula in a whole column where I also have empty cells

I have a column with a formula/function that has other cells as parameters. However in the column, every few rows I have a subtotal and blanks, for readability.
Now I decided to change the ...

**1**

vote

**2**answers

378 views

### Get date in Excel and print as plain text in cell

Is there a way in Excel to get today's date, then convert it into plain text so it can't be changed again when you open the file at a later date?

**2**

votes

**5**answers

1k views

### Excel not properly recalculating values

I have an Excel sheet with values in it (this sheet is generated by a custom perl script, but I don't think that's where the problem lies). In it, I have a formula:
...

**2**

votes

**2**answers

26k views

### Displaying a value only if another cell has a value in Excel

I'd like to display an empty box (HTML entity □ □ or any other similar box will do) in Excel in a cell if and only if another cell has a value.
I've tried =IF(ISBLANK(C8),,□), but that ...

**4**

votes

**4**answers

852 views

### Spreadsheet forumula questions to pick a random DVD

I have a list of my (279) DVD’s in an OpenOffice Spreadsheet (version 3.3.0) on my Windows 7 laptop and I want to create a formula to randomly pick a film to watch (for occasions when I can’t make up ...