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

**3**

votes

**2**answers

386 views

### Convert “Mon Aug 01 09:08:25 CDT 2011” to a usable date/time using Excel

I have a giant spreadsheet with computer names and the date stamp that they last reported into our PGP server that I need to convert to a usable date/time, so I can sort the column by the time the ...

**0**

votes

**1**answer

1k views

### How can I determine if a number is between two values in OpenOffice Calculator?

I'm trying to write a formula that will do different things depending on which number range the number inputted to it lies in.
If the number is between 0-40 inclusive, I want it to return 0.
If the ...

**2**

votes

**1**answer

393 views

### How to calculate AVERAGE(POWER(A1:A2, 10)) in Excel?

I can do this creating a new column B where =POWER(A1, 10) and then I get the result with =AVERAGE(B1:B2). But how to do that without a new column?

**1**

vote

**2**answers

138 views

### How to sum the other cells in a column with Excel?

In cell A1 I can't write =SUM(A:A) (Circular reference). If I write =SUM(A2:A15) when I insert A16 the result won't be affected. How to do this?

**2**

votes

**1**answer

173 views

### How to retrieve non-contiguous cell collections from strings in Excel?

I tried to =SUM(INDIRECT("B4,B6")) but it doesn't work. How to do that?

**12**

votes

**16**answers

78k views

### How to get the current column name in Excel?

What is the function to get the current line number and the current column name for a cell in Excel?

**3**

votes

**1**answer

249 views

### How to make many column formulas ignore the same lines in Excel?

I have multiple columns and at the end of each one I have a SUM and MEAN lines.
Now I want to ignore some lines thus making =SUM(A1:A15) into =SUM(A1:A13;A15) if I want to ignore line 14. Doing this ...

**4**

votes

**2**answers

4k views

### How to make two formulas reference the same cells in Excel?

I have one row that uses =MEAN(A1:A15), and another one that uses =SUM(A1:A15). But many times I have to alter this range to, let's say, A1:A13;A15 in both cells. I would like to alter the range in ...

**1**

vote

**1**answer

1k views

### Cell value string to Int in OpenOffice Calc?

I have "1 hour", "2 hours", in a column in Calc, and I would like to extract the numbers only. I've tried VALUE(A9), but it doesn't work.

**3**

votes

**2**answers

2k views

### Open Office Calc - Round to 5?

Is there a way I can have a cell round a number to the the nearest 5?
For example, the number 114 would round to 115, or 62 would round to 60

**2**

votes

**1**answer

6k views

### Make COUNTIFS count duplicate values only once

I've got all this data on test runs:
controller start end start ts end ts
1 1:13 1:15 1:00 1:30
1 2:08 2:25 2:00 2:30
4 4:02 4:16 4:00 ...

**2**

votes

**9**answers

51k views

### Excel isn't calculating the SUM of cells

I am trying to use a very simple formula which is =SUM(B9:B11). However the cell doesn't compute for some reason.
I've used Excel for years and have never had this problem. Any idea why it may be ...

**2**

votes

**3**answers

13k views

### How to count number of repeat occurrences

I have an Excel Spreadsheet that looks more or less like this:
+------------+-----------+
| First Name | Last Name |
+------------+-----------+
| John | Paul |
| Joe | Jones |
| ...

**1**

vote

**2**answers

353 views

### Excel IF statement

I am trying to look at column E and if it is >0 put a 1 UNLESS column D has a value. If column D has a value, I would like to copy the contents of column D. I was thinking the following formula would ...

**0**

votes

**2**answers

4k views

### Checking if any cell in a column of strings on one worksheet is contained in a column of strings in another worksheet

I have a column of strings in an Excel worksheet. I'm looking for a way to write a worksheet function (not VBA) that will allow me to tell, for each cell, if the value of the cell is contained in a ...

**1**

vote

**1**answer

606 views

### Excel formula using relative row and constant value

I am using the following Excel formula to get the relative row number for a calculation.
=$A9*(C3)
Cell C3 contains a constant value, so I always want to use that value, regardless of the row.
When ...

**2**

votes

**2**answers

2k views

### Get adjacent column value in OpenOffice.org Calc

I'm trying to get the value of adjacent columns in OpenOffice.org Calc.
I have several rows, each with a column that should have its value incremented by one when a column immediately to the right ...

**5**

votes

**5**answers

9k views

### How to return everything between two characters in Excel?

I'm a programmer with lots of languages, but somehow I've never learned Excel macro or formula language. I'm sure there must be a simple way to do this, but I'm stumped.
I have a column of file names ...

**0**

votes

**3**answers

165 views

### Excel: better ways to express this as a formula

X is an integer > 0
For x = 0, return 0; for all other x, return 1
Solutions I have considered:
IF (whatever), obviously. Want to avoid it if possible, just because nested/lengthy formulas using ...

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

813 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

672 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

267k 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

972 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

798 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

417 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

685 views

**0**

votes

**2**answers

405 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

388 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 & ...