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

learn more… | top users | synonyms (3)

4
votes
5answers
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
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
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
2answers
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
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
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
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
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
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
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
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
243 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
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
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 ...
2
votes
4answers
682 views
0
votes
2answers
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
2answers
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
1answer
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
1answer
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
5answers
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
1answer
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
3answers
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
1answer
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
2answers
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
1answer
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
3answers
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
5answers
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
2answers
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
5answers
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
2answers
26k views

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

I'd like to display an empty box (HTML entity &#9633 □ 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
4answers
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 ...