Hot answers tagged

44

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

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 ...


4

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


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 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 ...


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) ...


1

Wilson. Here is another approach (assuming the date is in A1:C): =ArrayFormula(IFERROR(VLOOKUP(UNIQUE(A:A),QUERY(A:C,"Select * Order By B Desc",1),{1, 2, 3},FALSE))) Just be sure to format the resulting date column as dates, since by default is will use the raw number format of the data (i.e., the number of days since December 30, 1899, which will be ...


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

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

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

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