The formula tag has no wiki summary.
0
votes
1answer
28 views
Calculate age in days/months/years in OpenOffice
In need to find the age in days - months - years in OpenOffice.
There is DATEDIF() in Microsoft Excel. You can use it to find the difference in days/months/years between two dates.
Age Calculation
...
0
votes
1answer
23 views
How to omit header values in Word formula?
I am new to superuser. Kindly pardon if I make some mistakes.
I am working with tables having numerical data in Word (no not even embedded Excel tables - don't ask why).
It so happens that many ...
1
vote
3answers
63 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 ...
0
votes
3answers
31 views
microsoft excel apply formula wrongly
Basically I am facing some problem due to excel auto generate formula that is wrong in such situation.
The formula at B6 is =A6*B1
Then the rest of the rows are using cursor bottom right drag down.
...
2
votes
1answer
29 views
Excel column comparing
I am working on comparing markup values between three different databases. I have each database in a seperate colomn. What is the formula to see if all 6 markup values are the same. To get a Yes if ...
0
votes
0answers
32 views
Delete rows in Excel based on returned value in row cell
I have a worksheet in Excel with values in some rows in column B that have been returned as zero (0) but still have a formula in the cell. I want to be able to delete just these rows using a macro.
1
vote
1answer
76 views
Copying formulas in Excel
I have an Excel spreadsheet containing many sheets, most of which have formulas.
I have managed to copy most of the sheets (with formulas) into a new Excel file. To copy the sheets I used Copy and ...
0
votes
3answers
90 views
Excel formula to bring back one of two values based on another cell being populated or Null
I am trying to create a formula that will reference a cell. If that cell is blank, I want it to bring back a value of "LTL Direct". If it is not blank (anything at all showing), I want it to bring ...
0
votes
1answer
46 views
Excel formula to change a number from + to – when word “Db” is next cell
I have a many long statements converted from text, the credits and debits all are in same column as plain positive amounts, to distinguish the credits to debits the cell next to the amounts shows the ...
0
votes
1answer
41 views
convert scanned physics maths pages to text
How to convert this image to text format with maximum accuracy , most OCR are not able to convert ^ and vector and proportional sign.
1
vote
2answers
58 views
Set a formula on many cells with minimal manual edits
I need to set the following formula on many cells:
=VLOOKUP(MAX(Historical!$A$5:$FZ$99999),Historical!$A$5:$FZ$99999,7,1)
This formula looks up the most recent date in the History table, and ...
0
votes
1answer
29 views
Auto updating similar formulas
I'm trying to see if there's a way to get my Excel sheet to auto update with new formulas as I enter it.
For example if D4 down to D27 was the same formula(=SUM(B4/25)) and I wanted to change it too ...
2
votes
1answer
45 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 ...
0
votes
2answers
117 views
Turn Excel spreadsheet into a formula
I have an Excel spreadsheet that has a complex computation that is not trivial to turn into a macro or a single-cell formula. The spreadsheet has a about 10 different inputs (values a human enters in ...
0
votes
1answer
61 views
How do I create a dynamic formula on Excel?
On Excel, I have a DDE formula on B1 which reads =server|info!someText.data
I want to change the formula so that someText is written on A1 and then reference it on the DDE formula. Something like ...
0
votes
1answer
110 views
Excel 2010: Giving another cell value to an “x” (check) in a cell
Hopefully I'll be able to explain clearly what I would like:
I have a spreadsheet about a car pool. I have to put an "x" (check) in a cell when there are seats available to transport people, but I ...
0
votes
4answers
223 views
Excel - Smooth a curve by calculating / interpolating source missing values
I have a table of values like this one :
18
18
18
46
46
46
46
46
57
57
57
57
57
57
69
69
69
69
69
69
64
64
64
64
64
64
56
56
56
48
48
48
44
or if you prefer :
18
0
0
46
0
0
0
0
57
0
0
0
0
0
69
0
0
...
1
vote
1answer
184 views
merge cells with formula skip duplicates
Our Goal is to merge the names (B) when the data in A is equal. The formula below looks at the next line (A), if it's the same, it merges data (name) from B. The formula is in C, so the result of the ...
2
votes
2answers
258 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 ...
0
votes
0answers
105 views
Excel Pivot Table Calculated Field including LEN
I'd like to setup a pivot table field that counts the number of characters in a given field. LEN([field name]) only returns 1 for every row.
0
votes
0answers
98 views
Kinect - Maximum distance
The kinect can detect people and use measurements when they are pretty close.
But how far can the user be away, while the kinect can still use metrics on it, eg. the user is 1.5 metres tall or whats ...
0
votes
2answers
72 views
Conditional Formatting Non Contiguous Ranges
I have a large spreadsheet, which came prepopulated with data. What I would like to do is the following. If I select I7:15, and apply this conditional formatting
Cell Value is equal to =G7, colour ...
0
votes
2answers
69 views
How do I get Excel to automatically replicate functions across the spreadsheet?
I have a spreadsheet where I'm doing a whole table of calculations. I'm playing with the calculations and seeing what it does to the data. I expect to change the calculations dozens of times (at ...
0
votes
1answer
201 views
AutoFill Large Number of Cells in Excel?
I have a formula in Excel that I would like to autofill to 999,999 other cells. How can I do this without dragging the autofill handle down the entire sheet?
5
votes
1answer
77 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 ...
0
votes
1answer
229 views
How to convert decimal years into a date (format dd-mmm-yyyy or similar) in either Excel or SPSS?
I've found a fair number of formulas online which let me convert birthdate into decimal age in years but I can't find something that converts, for instance, 1.0 into 04-Nov-2011.
Any help would be ...
-2
votes
1answer
65 views
How do I create a range from a cell value?
For example:
A B
7926 3 FROM =COUNTIF(AQ$7926:AQ7970,"=0")
7926 3 FROM =COUNTIF(AQ$7926:AQ7971,"=0")
7926 3 FROM =COUNTIF(AQ$7926:AQ7972,"=0")
7926 3 ...
1
vote
1answer
273 views
Conditional Data Validation
I've been taking a certification course on Excel. It's a beginner class but something caught my attention. Specifically, Data validation.
We learned how to do it using the default conditions but I ...
1
vote
1answer
147 views
How would I create a function in Google Docs (or Excel) to evaluate column a and return column b value.
I'm using google docs speadsheets to create a dashboard. I have two columns of data. I want to have a cell evaluate Column One for a specific name(for example, "Agency A") and return the value in ...
0
votes
1answer
403 views
OpenOffice Calc - how to insert BLANK in a formula?
In Calc, as in most spreadsheet applications, an IF() function exists that can be used to control behaviour, for example inserting this formula in cell C1.
=IF(A1>B1;"A";"B")
will display A in ...
2
votes
1answer
132 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)?
3
votes
1answer
216 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 ...
0
votes
2answers
322 views
Compare Two Columns (from two different worksheets) and show result of another column - excel
I have this scenario:
Worksheet 1 with Column A only
Worksheet 2 with Column A and B
I have to compare Column A (Worksheet 1) and Column A (Worksheet 2).
If a value in Column A (Worksheet 1) is ...
1
vote
1answer
72 views
Sum up totals if name is Persons Name
I have several columns in a spreadsheet with date, item, order etc. but also 2 columns:
Column K - Owed By
Column L - Owed Amount
In column K there will be a couple of different names, say, "Joe" ...
0
votes
1answer
293 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.
1
vote
1answer
150 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
1answer
247 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
4answers
439 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
390 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
242 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 ...
3
votes
2answers
585 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
3answers
1k 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
148 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 ...
1
vote
3answers
822 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 ...
4
votes
3answers
5k 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 ...
1
vote
4answers
3k 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 ...
0
votes
2answers
358 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 ...
4
votes
4answers
358 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 ...
2
votes
4answers
571 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
126 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; ...
