Hot answers tagged formulas
7
According to Wikipedia:
Users gain one point of link karma
when another user votes up their
submission and lose a point when a
user votes down their submission.
Individual comments may also be voted
up or down by other users and result
in a user's comment karma. Users stop
losing karma after their submission
reaches 0. This is not the ...
5
How about:
= COUNTIF( A1:A100; "OK" ) / COUNT( A1:A100 )
A1:A100 being your range in which the OK/NOK values are defined. If you have more values than OK and NOK, use this:
= COUNTIF( A1:A100; "OK" ) / ( COUNTIF( A1:A100; "OK" ) + COUNTIF( A1:A100; "NOK" ) )
4
This may help you:
Google spreadsheets conditional formatting
How to enter your Apps Script into your spreadsheet:
http://code.google.com/intl/de/googleapps/appsscript/guide.html
4
It seems what you really want to do is 1) mark certain rows and then 2) sum the marked rows. On any spreadsheet, it makes the most sense to create another column and put a number or character in there. Then use the SUMIF formula to calculate the result.
Imagine having a 4 row by 2 column spreadsheet. You want to sum the values in column A if there's a Y ...
3
In Google Spreadsheets, OR is a function (with any numbers of arguments), so you would write
IF (OR(C7 = "U.S.A."; C7 = "Canada"); "The Americas"; "Europe")
See the example spreadsheet I set up.
Also, check the Google Spreadsheets function list.
3
Delim means delimiter
array_1 means the cells you want to use.
=join(",",B1:B4)
Will put a comma between each value from cells b1 to b4. The result appears in the cell where you put the formula.
Note: the cells must be in the form as 1xN or Nx1 array. In other words all in the same row, or all in the same column.
3
I personally use MathIM, which is an online chat room which parses everything between $$'s in your message as TeX. It's perfect for talking to my friends when I need to use math equations.
It seems to be exactly what you are looking for :)
2
The best way I can think of to accomplish this is with importRange function.
Here's the general idea. You have three spreadsheet documents. Doc1, Doc2, Doc3.
Doc1: Range A1:A20 contains list of items you want to validate against
Doc2: Range B1:B20 contains list of items you want to also validate against
Doc3: Range C1:C20 contains range you wish to have ...
2
This seems to be pretty much perfect: http://www.twiddla.com/
It even has support for LaTeX!
http://www.skrbl.com/ seems pretty good too as a slightly more basic alternative.
Also, here's quite a good list of interactive whiteboard related links, many of them whiteboard webapps: http://www.shambles.net/pages/staff/intwhiteb/
2
The pure spreadsheet answer is to create columns D and E to display your information. An example row:
A3: text
B3: (enter USD if known)
C3: (enter LBP if known)
D3: = if( isblank(B3) ; C3/1500 ; B3 )
E3: = if( isblank(C3) ; B3*1500 ; C3 )
The other way you can solve this is with JavaScript, if you know it, using the onEdit trigger. In that case the ...
2
You need to use 2 formulas.
Create a month column and use the following formula to "display" the month and year:
=text(B1,"mmmm YYYY")
I assumed that the data was in column B, you will need to adjust accordingly. Copy this formula down the entire column
Create a column to hold the labels for the calculation.
Put the value for each month in the cells ...
2
Yes, by FILTERing the array:
= JOIN( " vs " ; FILTER(C10:C14; NOT(C10:C14 = "") ))
Thus, the JOIN method will only operate on non-empty cells.
I have set up an example spreadsheet.
Also, check the Google Spreadsheets function list (search for FILTER).
1
Try this formula in google - spreadsheets
=arrayformula(SUM(('Survey Data'!F2:F200="Divisional Exec")*('Survey Data'!A2:A200="Act")))
In Excel - assuming Excel 2007 or later you can use COUNTIFS
=COUNTIFS('Survey Data'!F2:F200,"Divisional Exec",'Survey Data'!A2:A200,"Act")
1
The following Google Apps Script (GAS) will achieve your goal automatically:
function CUMSUM(array) {
// set active spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
// get active sheet
var sh = ss.getActiveSheet();
// get data (2D array)
var aTest = sh.getRange(array).getValues();
// convert 2D array to 1D array
var aData = ...
1
If you're thinking about "loop and if statements", you should look into Google Apps Scripts.
Other than that, it seems you're looking for the TRANSPOSE function, in conjunction with the FILTER function. Take a look at the Google Spreadsheets Function List.
If you can tell me the purpose of the Purchase column in your example, I might be able to set up a ...
1
Something like this? The ranges are different here but the logic is the same - each cell in your range gets a formula like this (this is cell B2 in the screenshot):
=IF($E2=B$1,$A2,"")
Which checks to see if the value in the Name column is equal to the column heading of the current cell; if so, it adds the value you put in the HRS column, and if not, it ...
1
You can do this with a simple formula. Given the range shown in your example, insert a new column to the right of column A. In this new column B, write:
B136: =A136
B137: =if( isblank(A137) ; B136 ; A137 )
Duplicate the formula in B137 into the cells below it.
Let's unpack this:
B136 looks to the left for its value (A136).
B137 looks to the left for ...
1
Actually, Gmail filters don't offer much customization or flexibility. I don't think you will be able to run some calculation with your filters.
Yet, you can use Gmail Stats to perform some calculations on your Gmail inbox. See Google System article on this issue.
1
I've searched the internet (Google, search tools, custom range) for references of the ISBLANK function pre-dating the OP's question. The ISBLANK function already existed during the time of asking: ISBLANK reference before 01/12/2009
Therefore this function will suffice:
=IF(ISBLANK(A2)=TRUE;"Blank cell";"Not blank")
There is however one thing to take in ...
1
You can find them using find and replace if you go to view -> all formulas (ctrl+') and then do you "round( " find this will at least cut down on the manual labor of it all
I couldnt find a way (using find and replace to search for wild card value though
EDIT:
Try out the script below
REF: ...
1
You'll need to have the raw data in a separate sheet or column from the filtered data. Here I'm assuming the raw data is in a sheet named rawData column A.
For the filtered dat you can use:
=FILTER(rawData!A:A, isError(search("example.com", rawData!A:A)))
Put this formula in cell A1 of a new sheet where you want the filtered data.
1
There's no way to use a formula in a Gmail filter's query. However, you might be able to create a filter that will match most of the results you want with the OR operator. Here's an example:
subject:(60 | 70 | 80 | 90 | 100) subject:coupon would match $80 coupon for foo
Only top voted, non community-wiki answers of a minimum length are eligible
