The formula tag has no wiki summary.
1
vote
1answer
39 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 ...
0
votes
0answers
28 views
How can I calculate how often one string appears in a column compared to another string? [migrated]
I have a column in my Google Docs spreadsheet which contains either "OK" or "NOK" (acceptance testing).
How can I calculate the percentage for how often "OK" appears compared to "NOK".
2
votes
2answers
51 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
49 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
42 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
53 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
49 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
56 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
48 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
117 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
62 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
68 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
46 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
151 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
69 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
124 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
83 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
41 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
185 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.
-4
votes
1answer
52 views
Excel Formula - multiple queries, IF AND
I am trying to develop a spreadsheet which automatically identifies the cost of an airfare with reference to two criteria in a table
the class of air travel and
the region in which the travel is ...
0
votes
1answer
80 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
101 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
285 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
255 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.
3
votes
3answers
157 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
340 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
698 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
122 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 ...
0
votes
0answers
71 views
Only subtracting cells in excel if they have a value [closed]
Possible Duplicate:
Only subtracting cells in excel if they have a value
I want to subtract two cells in excel but only if both cells have a value. More specifiaclly in my case the formula ...
1
vote
3answers
298 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
2answers
2k 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
2k 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
248 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
298 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
432 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
102 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; ...
1
vote
4answers
373 views
Is there a way to tell which cells reference a selected cell in Excel?
In Excel, you can tell a cell to reference another cell in order to calculate its value. I'd like to be to do the opposite, to get any cells that reference the currently-selected cell. Is this at all ...
2
votes
3answers
92 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 ...
0
votes
1answer
113 views
Excel Formula: trouble with the low % values not showing up in an in-cell chart? (image included)
I've a few tables that have % values like -4.5%; -16%; 15%; 6%; 2.5%. I'm interested in creating some in-Cell charts that show positive and negative values.
My smaller values aren't showing up, so ...
3
votes
1answer
141 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 ...
1
vote
1answer
147 views
Summing custom labeled cells in Excel 2010
I have a sheet that I'm calculating particular costs. These costs have associated labels to them: Manufacturing, Variable, Both, Neither. I have created a drop down list for next to the costs that ...
2
votes
2answers
380 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 ...
1
vote
2answers
288 views
Excel: ROUND() to the next unit of 5?
I'm creating a lot of graphs, so I'm making a common scale to be able to compare them using MAX/MIN values that I'm hiding in the graph as a separate series. My Vertical AXIS (Y) is in units of 5.
...
0
votes
1answer
129 views
Is there any way to make the formula bar in Excel 2008 on Mac OS X 10.6 dock or attach to the window instead of floating?
Is there any way to make the formula bar in Excel 2008 on Mac OS X 10.6 dock or attach to the window instead of floating?
So far everything I've read says no, but maybe you know the secret!
0
votes
3answers
466 views
Excel: How to sum values in a column based on multiple criteria
I have a list of criteria in row 20, like this:
A20: "My Company, Inc."
B20: "Client Expenses"
C20: "Travel"
The data is below, like this:
B40: "Client Expenses"
C40: "My Company, Inc."
D40: ...
0
votes
1answer
282 views
Formula re-evaluation issues in MS Excel 2010 Beta
This has only started happening recently, where I have say a list of values:
A B C
_ _ _
1| 1 4 5 [=sum(A1,B1)]
2| 2 5
3| 3 6
I have my formula in cell C1 [enclosed in ...
0
votes
1answer
312 views
Excel Macro: compare separate workbook lists and LINK to the data in a corresponding column?
1. How can I compare two matching lists from separate workbooks? And then retrieve the data from the same row but another column - (Index&Match maybe be appropriate for this). But how does this look ...
0
votes
1answer
284 views
Excel Pivot: How can I show the % of the sub-total against the Grand total?
I have 15 colleagues who have answered 'numerous questions' over the years in a questionnaire. The questions used to be standardised and all colleagues had the same questions. Unfortunately standards ...
0
votes
1answer
134 views
Excel formula - sometimes array formula, sometimes not
I want to write some data to an Excel file, and I want the first page to show a summary of the data that are spread all over the sheets. The individual data items on a sheet can be included or ...
0
votes
2answers
67 views
Formula in Excel
Below is the data:
A B C
xxx 2 5
xxx 3 4
bbb 2 3
bbb 2.5 2
nnn 1.5 2
nnn 2 1
I want to ...
