Hot answers tagged spreadsheet
10
Google Docs
Upload your existing files.
Google Docs accepts most popular file formats, including DOC, XLS, ODT, ODS, RTF, CSV, PPT, etc. So go ahead and upload your existing files.
Familiar desktop feel makes editing a breeze.
Just click the toolbar buttons to bold, underline, indent, change font or number format, change cell background color ...
6
Use a combination of the ARRAYFORMULA, SUM and arithmetic operations
Here's the function to count all of the clicks by a 26 year old:
=ARRAYFORMULA(sum((A:A=26) * (C:C="true")))
There are 3 parts to this operation.
The ARRAYFORMULA takes care of looping over the specified range
The SUM manages counting all of the true results
Essentially, true is ...
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
You can do this with the DSUM function. The general form is:
=dsum([data range], [column to sum], [criteria])
[data range] would be the data in your Worksheet B, something like 'Worksheet B'!A:C where 'Worksheet B' is the actual name of the worksheet.
[column to sum] is the header of the column that you want to add up. In your case, that would be "Hours ...
4
This works, see F35 in your sheet:
SUMIF(E2:E33;">="&E35;B2:B33)-SUMIF(E2:E33;"<="&E36;B2:B33)
column E is done by calculating a value for the dates to be able to compare them (=VALUE(E1))
Note that separating the args for some reasons is done with ;
3
There is a free application known as TrelloExcel out there. I've been using it for about a week or two now - https://github.com/dillenmeister/TrelloExcel
It will very nicely export/import data, but it doesn't seem to handle updating existing items. For what you need it for, I think it will work to get you the task name, description, and due date (but no ...
3
=COUNTA( IFERROR( FILTER('Guest List'!$G:$G ; 'Guest List'!$G:$G = "Yes" ; 'Guest List'!$L:$L = "USA" ) ) )
This was for two columns- I am using this for my wedding guest list- in this case, tabulating "Yes" on sending a save the date, and "USA" for location, so I can calculate postage later. There may be a shorter formula, but this seems to work, so I'm ...
3
A solution to this problem is to utilize the SUMIF function.
=sumif('Hours Spent'!A3:A10001, A3, 'Hours Spent'!C3:C10001)
What's going on here?
We are referencing the other spreadsheet via the 'SheetName'! syntax
We are only including rows in the sum if the value in column A in the hours spent worksheet equals the value in A3 in the current worksheet. ...
2
To test for string you can use =ISTEXT(A1)
To test for an Integer you will need to do something like this:
=IF( (D8 = int(D8)), TRUE, FALSE)
Which basically converts the value to an Integer and then checks if it is the same as the original pre-converted value.
2
If there is a value of 0 in the cell, it won't be flagged as blank. But you can use:
=SUMIF(B:B,0,A:A)
Or more restricted:
=SUMIF(B2:B50,0,A2:A50)
template: SUMIF(range, criteria, sum_range)
Adds the cells specified by a given criteria. Range is the range to which the criteria are to be applied. Criteria is the cell in which the search criterion is ...
2
Logical operators such as OR return single values in array formulas, try instead:
=ARRAYFORMULA(SUM(IF(IF($C$4:$C$4000 <= H55; 1; 0)+IF($C$4:$C$4000 <> ""; 1; 0)+IF(($D$4:$D$4000 = "")+($D$4:$D$4000 > H55);1;0)+IF(($E$4:$E$4000 = "")+( $E$4:$E$4000 > H55);1;0)=4; 1; 0)))
2
This is a bit off topic because it doesn't answer the question of Excel exporting, but it does address the side of good solutions for burndown charts. We use Trello Burndown from Blue Line Game Studios. It does a pretty good job and everyone can update their piece so it's not dependent on sharing an excel spreadsheet. You can find that at ...
2
I couldn't find the trick for the real date cell to work yet, but you could have a work around if nothing else by adding the the single quote (') before the date in a format: YYYY-MM-DD in the Q4 cell.
That single quote means that the it will be parsed as string.
1
Use the conditional format option in the menu format:
Make sure to select the complete column, when applying the conditional format.
See screenshot from the example file I prepared:
Example file: Conditional Formatting. Try to add some numbers yourself add see the result. Be sure to check the settings under Format> Conditional formatting... in the ...
1
I have been building an online database application over Google App Engine for such use-cases. It is called iFreeTools Creator.
Supports multiple data-types for fields including single-line text, multi-line text, numbers, images, geo-location and more.
Forms and views are automatically generated for users based on their authorization profiles.
Users ...
1
There is no current functionality to do this (I'm not on the Beta, so I cannot test the Bulk Export functionality currently on offer, although it doesn't look like its what you need).
However, there is a card on the Ideas stack (sort of a feature request list), entitled CSV or Other Export. This looks like more what you want - however it's not implemented ...
1
Writing HTML in the Gmail is not possible so you'll have to use another client to compose your email. But especially for iframe I don't think that anyone will be able to see it, since it has some security issues and most likely Gmail will block the contents of it. Consider sending it as an image.
1
Put your desired date in a new cell like Q3 in this format: 2/28/2012
Then in the Q4 cell type =YEAR(Q3) & "-" & DEC2OCT(MONTH(Q3), 2) & "-" & DAY(Q3)
This will get the date from the Q3 cell and convert it to a string in the Q4 cell.
This is more helpful if you want the current date. Instead of getting the date from Q3 your can use now() ...
1
The solution below involves a very elegant and generally useful technique popularized by ahab, a Google Docs expert; I suppose he invented it. It uses matrix multiplication.
Move the label "Starting" to the last cell of the Description column, column D.
Move the starting balance to the last cell of the Amount column, column E.
Insert a new blank row 2. ...
1
Looks like I gave in too fast.
7 day running total: =Sum(Filter(D2:D8,A2:A8<=A8,A2:A8>A8-7))
Filter returns a range of data, filtered based on multiple (can have more than 2 conditions). In the case I'm returning D2:D8 (my count column) filtered where A2:A8 (my date column) is less than the current date (A8) and greater than the current date - 7 ...
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
Solution
Place this formula in cell C2:
=ARRAYFORMULA(SORT(UNIQUE(IF(ISNA(MATCH(A2:A,B2:B,0)),A2:A,""))))
What you want is the set difference. This formula will give you all the unique values of column A that do not appear in column B.
Details
MATCH(search_criterion, lookup_array, [match_type]) checks if an element is found in the given array. In this ...
Only top voted, non community-wiki answers of a minimum length are eligible
