Hot answers tagged

43

Use the following formula to obtain the query you want. Formula =FILTER(A:A;(A:A="Combat Medic")+(A:A="Universal")) Explained In the reference it is explained by Ahab that + and * can be used as boolean expressions, as OR and AND. The solution with the IFERROR (see revision history) is of no use in this case, unless you want to find a particular string: ...


6

TL;DR =AVERAGE(OFFSET(A:A;COUNT(A:A)-7 + MATCH(TRUE;INDEX(A:A<>0);0)-1;0)) Example Explanation First we count how many numbers are in row A with =COUNT(A:A). Cells containing text won't be counted. Our example would give us 9 From this number we subtract 7 since you want the average of the last 7 values: =COUNT(A:A)-7. This will give us a 2. Now ...


4

Use a FILTER/MATCH combination. Example on your data below: =filter(C52:F57; match(C52:C57; I53:I54; 0))


4

Short answer Try =FILTER( 'Output Checker'!A:P, ROUNDDOWN('Output Checker'!A:A)=A2, 'Output Checker'!L:L <2, 'Output Checker'!M:M ="NO", 'Output Checker'!N:N ="Yes", 'Output Checker'!O:O ="NO" ) Explanation Google Sheets doesn't have a built-in function called CONTAINS. To evaluate that a date-time value met a date only criteria, round ...


3

On Locking Ranges using "$" The first problem I see is the $ sign is placed before the row. This tells the spreadsheet that it needs to LOCK that row (2 & 12) from changing numbers. The drag-handle operation relies on gsheets to find a pattern in your selection and repeat or continue it. If it doesn't find a pattern it repeats the elements you have ...


3

For a single phrase, you should wrap it in double quotation marks. "6 new updates and 2 invitations waiting for you on" If you have multiple exact phrases to test, use double quotes for each phrase, inserting a space between phrases to separate them (as opposed to a comma). "e-statement Notification Account Statement on" "08" 2009


2

if you're a firefox user, there's an add-on called youtube ratings preview. it shows the percentage of ratings on both search results and recommended videos on video page, but not on 'load more suggestions' results. not the ideal i guess, but it helps.


2

There is no way to block a video within YouTube. You can block a user though by navigating to the users profile and clicking block user. If you are looking specifically for a browser extension then please flag this question for migration to SuperUser. (Browser extensions are off-topic here as per the FAQ)


2

Yet another way: =AVERAGE(ARRAY_CONSTRAIN(SORT(FILTER(A:A, ISNUMBER(A:A)), 1, 1), 7, 1)) Explantion: filter out all numeric values turn the column upside down (so that last values are above) by using sort() limit the output of that column to 7 rows (using ARRAY_CONSTRAIN) Take the average ... EDIT: Sorry for misunderstanding. The above averages the ...


2

You might use INDIRECT within a query, say: =query(indirect($B4&"!D:H");"select sum(H) where D ='"&B$3&"' label sum(H) ''") but really you seem to have backed yourself into a corner. You appear to be trying to analyse data that has already been analysed. A more conventional solution may be to collect all the data together in one place (sheet) ...


2

You may use an additional column to filter categories in place. Here's the formula to check if any text matches "golf" or "ski" : =REGEXMATCH(A2,"ski|golf") It will give True if cell A2 contains any word "golf" or "ski". If you want to check more then two words, the easy way is to write them into a separate list, say K1:K3: golf ski badminton and then ...


1

You should move your 3 unique sport values to 3 additional columns: Ski Badminton Golf where you could filter any sport with values: true/false select Cell formula for the additional columns: =IF(IFERROR(FIND(E$3,$B4)),TRUE(),FALSE()) my example UPD: Second solution - Additional one column with unique left-column value and filter by search I've ...


1

Your regular expression looks fine. That screenshot, however, looks like it is from your real-time reports. Real-time reports do not always respect filters, especially if those filters were recently created. The safer option is to allow data to collect for 12-24 hours and then inspect your content reports for the day. You should hopefully see the absence ...


1

Assuming dates in Column A and document names in Column B: =query(A:B,"select B, max(A) group by B") Reference QUERY function


1

It would seem from the description that you need all non-empty values of this column. In this case perhaps this formula would give you what you want: =UNIQUE('Sheet Name'!A$2:A) or if you want not unique, but non-blanks simply =FILTER('Sheet Name'!A$2:A, NOT(ISBLANK('Sheet Name'!A$2:A)))


1

I assume you want to get the values in K that are not appearing in L ? If so (and assuming your data starts on row 2), try: =filter(K2:K, isna(match(K2:K, L2:L,0))) If you only want to see unique values, then try: =unique(filter(K2:K, isna(match(K2:K, L2:L,0))))


1

I think your Q is basically "I want to auto-fill my progression by a set increment" and guess that that increment is 2 and you wish to start at 2, so put 2 in one cell, 4 in the cell immediately below, select the two cells, grab the fill handle and drag down to suit.


1

Google Reader's trends page itself should tell you all that you need. To head to the trends page from Google reader, the sidebar has the link to the trends page


1

You can make the following your Google bookmark and/or homepage: https://www.google.com/webhp?tbs=qdr:y The reason for the link is that there's no way to default the search parameters for "Since"


Only top voted, non community-wiki answers of a minimum length are eligible