The formula tag has no wiki summary.
12
votes
3answers
56k views
Excel: Replace occurences of one character with another in a String?
I want an excel formula that will change Haverford, PA to haverford,+pa. (Text is to lowercase, and spaces are replaced with plus characters.)
The following will get the first space, but not all of ...
8
votes
5answers
20k 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 ...
5
votes
1answer
180 views
Excel formula needed
I'm not so good in excel but i "think" my problem can be solved using excel.. I've got a column with numbers and i'd like to find the best combination of using these numbers. In
our case the best ...
4
votes
2answers
3k 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 ...
4
votes
3answers
2k views
Type math formulas in LaTeX way in Microsoft Word?
I wonder if there is some free solutions for typing math formulas in LaTeX way in Microsoft word document (Office 2007)?
4
votes
2answers
11k views
excel VLOOKUP, how to specify dynamic worksheet name?
I'm trying to create a VLOOKUP formula in excel, however, the table array may be in a number of different worksheets.
For example, I have this forumula
=Vlookup(a1, ...
4
votes
4answers
580 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 ...
4
votes
1answer
917 views
Excel 2007 - How can I write “use this cell” or IF BLANK “use this cell”?
I am trying to show the Days between NOW() and the dates (dd/mm/yy) in, either Column B or Column C - depending which one is NOT blank
A B C
29/03/10 01/04/10
29/03/10 ...
4
votes
2answers
261 views
Restrict and validate date format in Excel
I'm trying to put together a 'Data Validation' formula which restricts the user to entering a date in the following format: dd-mm-yy.
From the research I've done, I've come across what I thought ...
3
votes
3answers
6k 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, ...
3
votes
3answers
3k views
Excel annoyance: How to maintain formulas in a ledger?
A common use of Excel is to create a spreadsheet which functions as a transaction ledger of some kind, where one or more fields in each row is calculated by adding a value or values from the current ...
3
votes
4answers
759 views
How can I show values in the formula bar instead of cell references?
Here is what I have in my worksheet:
A1=2 B1=3 C1=A1+B1
I want the actual cell to show 5 but when I copy C1 I want the formula in the cell to show =2+3, not =A1+B1 or 5 is there a way to do ...
3
votes
2answers
1k views
Excel: conditionally format a cell using the format of another, content-matching cell
I have an Excel spreadsheet where I’d like to be able to create a “key” of formatted cells with unique values, and then in another sheet format cells using the key formatting.
So for example, my key ...
3
votes
1answer
297 views
How can I disable the 'your formula has an error' pop-up in Excel 2007?
This is driving me absolutely crazy, while I'm in the middle of typing my equations into a cell I like to pre-create the parenthesis sometimes when order of operations is being explicitly set, however ...
3
votes
2answers
259 views
Is there a way to express a complex Excel formula in terms of final (non-formula, value-only) cells?
I have a very complicated Excel spreadsheet (formulas accessing cells with other formulas across multiple worksheets) that in the end computes a single cell (output) with input from several other ...
3
votes
1answer
223 views
What does “calcs” formula do in Microsoft Excel?
I encountered an XLS file with this formula in some of its cells. I searched the web for information on the formula, but I couldn't find anything. Could you help?
The formula in question is:
...
3
votes
1answer
734 views
How do I make a random selection in a spreadsheet using more than one criteria
I have a spreadsheet created in OpenOffice (V3.3.0 in Windows 7 32 bit) that I use as a database of my DVD collection. After asking a question in SuperUser, I have it so it can pop up a random title ...
2
votes
4answers
1k views
How to calculate how much I've worked per day?
I'm having a problem when I try to calculate how much time I've worked per day by using an Excel formula. It returns valid results for when I've worked until 12:00 PM but if I passes it, values come ...
2
votes
5answers
8k views
Using excel, how can I count the number of cells in a column containing the text “true” or “false”?
I have a spreadsheet that has a column of cells where each cell contains a single word. I would like to count the occurrences of some words. I can use the COUNTIF function for most words, but if the ...
2
votes
4answers
1k views
Excel formula to get the penultimate value in a row
Imagine I have a row of data in excel, like this:
[A] [B] [C] [D] [E] [F] [G] [H]
[1] Data 5 8 12 32 15 9 89
I want a formula that will return the ...
2
votes
3answers
144 views
Getting the value in one column from a formula on another column in excel
I have an excel table that looks like this:
Date Original Actual
11/10/10 15 12
12/10/10 12 10
...
24/10/10 2 1
25/10/10 -2 ...
2
votes
3answers
988 views
Excel formula to convert boolean values into {0, 1}
What is the Excel formula to convert boolean values {FALSE, TRUE} into {0, 1}?
Supposing there is one shorter than =IF(cond,1,0).
2
votes
4answers
933 views
How do I calculate number of hours > 24 in Excel?
I've got an excerpt of my spreadsheet below. It's a time tracking application containing a start date/day, a session ID and the duration of the session. Column A is populated by adding the duration in ...
2
votes
1answer
134 views
Excel Formula: What is the equivalent of =MATCH(), but for multiple columns?
Problem
This formula will return the row number where the value "Bob" appears. Say the value happens to be in $A$10, the formula would return: 10
=MATCH("Bob",A:A,0)
I would like a formula to do a ...
2
votes
1answer
88 views
Why is Excel showing 'c' as the arg separator for fomulas?
In Excel 2007, I have a cell with
"=if(true,1,0)"
as the contents. If I hit the ENTER key
I get an error dialog which says, "The formula you typed contains an error."
The flyover help for ...
2
votes
3answers
522 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 ...
2
votes
3answers
84 views
Include an OR condition in a COUNTIF?
This question has been asked before and the answer always seems to be to add together two COUNTIFs. This is fine for most applications, but won't work for me.
I have a column that can contain 0, 1 or ...
2
votes
2answers
3k views
Excel: How to return the last Row Number that a contains a specific string?
In excel, is there an excel formula I can use to populate Column B (in my example below) that will return the last row number that contains a specific string, before the current row?
The data is not ...
2
votes
2answers
2k views
How can I write an excel formula to do row based calculations?
Could someone please help me with the following excel formula. Each row in my spreadsheet contains details about a product.
I have one column 'Quantity' (Col B) and 'Profit' (Col F). I want to use ...
2
votes
2answers
23k views
How to set Function “formula” for whole column in Excel 2007?
I want to set "Formula" to all cells in certain column whatever the number of rows was.
But i don't want to set this function to first cell "Header" of this column.
How do i make it ?.
2
votes
2answers
3k views
What is an excel formula that will return me the last row number of a cell within a range that contains specific data?
I have a spreadsheet that will default with values of "No". Over time, these values will be changed to "Yes".
I need a formula (no VB, please, VB is useless to me) that will get me the row number of ...
2
votes
2answers
165 views
Calculating a duration in excel from date and time
I have 2 columns in excel, each column includes a date and a time in the following format: 'dd/mm/yyyy hh:mm'. One is a start date/time, the other is an end date/time. The end date/time could be ...
2
votes
3answers
5k 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
3answers
181 views
Excel formula: can MATCH recognise 'n'&“01”, or 'n'&“02 ”
I have an Excel sheet (source) that has simple ID numbers in column A (01 to 40000). In another sheet (child) I have these same ID numbers in column A but with either an additional 01 or 02 added on; ...
2
votes
2answers
1k views
Excel conditional formatting based on a formula
I would like to use Excel's conditinal formatting of a particular column based on values in two other columns.
I have values in columns A, B, C and D.
column A has text values (any value user ...
2
votes
2answers
32 views
Excel macro checks if there is formula
I want to make a macro that checks if that cell get its value from a formula. I cann't find anything similar. Any tips??
2
votes
1answer
37 views
How do I copy the results of a calculation without copying the formula behind it? Open Office Calc
How do I copy and paste only the results of a calculation without copying the formula behind it? I'm learning to use Open Office Calc. I remember other spreadsheets that had a values function, or ...
2
votes
2answers
103 views
Auto Updating an Excel Filter
Firstly I'm using excel 2010.
I have had a look at other "auto-update" and from what I saw they wanted you to type some information in to make it "refresh" the auto-update.
Essentially what I have ...
2
votes
2answers
72 views
I want to count the number of cells included in a formula
I've been looking for this answer and I'm sure it's embarrassingly simple but I can't find it anywhere.
I have a very simple but large formula =SUM(H2:H5,H7,H22:H23,H28:H30,H33,H41,H43:H46.....etc) ...
2
votes
1answer
467 views
Excel: ROUND & MOD giving me strange DATE results
This is sort of related to a previous question. My formula, which seemed to work fine yesterday now gives strange results.
Today is the 30th of March (30/03/10). It's 10:11am on the clock that the ...
2
votes
3answers
2k views
Use regression equation in excel formula
I have a spreadsheet with a number of graphs on, and I have displayed the trend-lines / regression lines on these graphs. These are not simple linear regression lines, but are high order polynomial ...
2
votes
1answer
2k views
How do I extract excel data from multiple worksheets and put into one sheet?
In a workbook I have 7 sheets(Totals and then Mon to Sat),I want to extract rows which have the word "CHEQ" in its cell (this is a dropdown list with two options-CHEQ/PAID)from all sheets. On my front ...
2
votes
1answer
592 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 ...
1
vote
8answers
4k views
How can I calculate the sum of all positive integers less than n?
I have the following function:
f(n) = f(n - 1) + (n - 1)
f(0) = 0
n >= 0
I have n declared on column A, and need the result of f(n) on column B.
I'm trying to find the Excel formula equivalent ...
1
vote
3answers
98 views
Is there a tool for windows that creates formula-pictures out of a text-formula
I am looking for a tool that would batch process text-files containing formulas (possibly latex style) and convert them into a gif/png/jpg. Since I want to do a batch conversion, the tool ideally runs ...
1
vote
1answer
390 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 ...
1
vote
4answers
10k 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
68 views
Excel - determine average using 1/2 value for flagged cells
Is there a way to calculate an average of a column of numbers using 1/2 the value for cells that are flagged by an asterisk in an adjoining column without having to insert a separate column?
1
vote
2answers
182 views
How does Excel's VLOOKUP handle multi-word lookup values?
I have a worksheet that's returning unexpected VLOOKUP results, and I'm trying to narrow down the issue. I've run into a question I can't answer in my search for the solution.
Consider:
I have a ...
1
vote
3answers
51 views
Complex Summing Values in a Cell
Let's say I have formula result that returns this +10+5+1+40, as text. I want to then sum the those numbers to get the total.
The result could be any number of values, so like +2+5 or +10+5+7+1+8.
...
