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

learn more… | top users | synonyms (3)

1
vote
1answer
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
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
16answers
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
1answer
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
2answers
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
1answer
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
2answers
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
1answer
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
9answers
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
3answers
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
2answers
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
2answers
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
1answer
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
2answers
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
5answers
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
3answers
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
5answers
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
4answers
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
2answers
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
1answer
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
2answers
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
3answers
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
4answers
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
3answers
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
3answers
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
1answer
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
7answers
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
10answers
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
1answer
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
2answers
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
4answers
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
244 views

Dates formula in Excel

Dates formula in Excel - I have attached an image that asks my question.
3
votes
2answers
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
3answers
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
2answers
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
1answer
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
2answers
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
1answer
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
3answers
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 ...