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

**1**

vote

**1**answer

7k views

### Sum up totals if name is Persons Name [duplicate]

Possible Duplicate:
Sum Values in One Range Based on Criteria in Another
I have several columns in a spreadsheet with date, item, order etc. but also 2 columns:
Column K - Owed By
Column ...

**1**

vote

**2**answers

733 views

### Excel conditional formatting of row

Can someone please tell me how to highlight multiple cells in a row based on some values in any one of a given number of cells?
For example, if in Row 2 any of the cells from D2 to L2 have value "A" ...

**1**

vote

**1**answer

4k views

### Excel IF and VLOOKUP function help

The exercise is to use IF() and VLOOKUP() functions to return the tax rate based on the annual income and number of people in the household.
So far I've tried inputting:
...

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

394 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

609 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

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

673 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

973 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

803 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

244 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 ...