How can you find the difference between two sets in Python? In Python, you can find the difference between two sets using the .difference() method. The set and frozenset objects contain a built-in method labelled difference() which helps to find the non-matching elements in the source object not found in the other sets passed in to the .difference(*others) parameter. Here is an example demonstrating how to find the difference between two sets using the Python REPL: ...

Can you use multiple criteria to filter data in Google Sheets using the QUERY function? Within the query parameter of the QUERY function the WHERE clause enables users to filter data based on multiple criteria. The three types of logical operators permitted when combining multiple criteria are AND, OR and NOT. Here are some examples demonstrating each of the logical operators, and to assist in demonstrating how these operators work, here is a sheet labelled Data which will be used as the basis for performing the QUERY: ...

How do you reference data in another sheet using Google Sheets? If you need to reference data in the same Google Sheet, there are two means: the sheet reference syntax using the name of the sheet followed by an exclamation mark and the range (i.e. "Sheet2!A1"), or the function called INDIRECT. If you need to reference data in an external Google Sheet, there is a function called IMPORTRANGE that can help. ...

How do you use the HLOOKUP function in Google Sheets, and what are some best use cases? The HLOOKUP function searches for data in the first row of a range and returns a specific nth cell in the column found. HLOOKUP is an excellent function to use in data sets where the primary search needs to be performed on data contained in the first row. The HLOOKUP function contains three parameters. The first search_key is the value to be found in the first row of the range (second parameter), with the index (third parameter) returning the nth cell from the first row. The final parameter is_sorted is not a required field, but if the range is not sorted, set this parameter to FALSE, and it will search for an exact match. ...

How do you change the default aggregate name created in Google Sheets when using the QUERY() function? To change the header label of an aggregate column from a QUERY() function append LABEL aggregate_column 'YOUR LABEL' to your SELECT statement. For example, if you had the following QUERY formula in your Google Sheet and you wanted to change the default label of sum(Sales Qty) to Total Sold then this is how this would look, here’s the original QUERY() formula: ...

How can you aggregate data using multiple sheets with the QUERY function in Google Sheets? To aggregate data sourced from multiple sheets, create a data set using the set notation {} by referencing each sheet then within the query statement of the QUERY function reference columns using ColX (with X being the index number of the column, starting at 1). As the QUERY function contains three parameters, the first parameter data can be a range or a data set created using the {} notation. It’s important to know when creating your custom data set whether the additional ranges from multiple sheets will be added as additional columns or concatenated as additional rows. ...

How do you concatenate two ranges into one contiguous range for use in the QUERY function for the data parameter in Google Sheets? To concatenate two ranges into one for use as the first parameter in the QUERY function in Google Sheets, simply combine your data sets together using the set notation {} and the semi-colon character to separate each range ; e.g. {{Data!A:A, Data!B:B};{Data!A:A, Data!C:C}}. For example, suppose you have the following transactional data on sales and associated direct costs like so on a sheet labelled Data: ...

How do you reference a cell in the WHERE clause of a Google Sheets QUERY function? To reference a cell in the Google Sheets’ QUERY function WHERE clause, simply break the query string by closing with a double-quoted string " append the concatenation symbol & then reference the cell append the & to open up the query string again " so you can continue writing the rest of your query. Do be mindful it does depend on the type of value being referenced in the cell as well. If the cell reference is a string, you will need to make sure you wrap the cell reference in single quotes. ...

What is the ceiling function in Python, and how does it work? The ceiling function in Python is found in the math library and it’s method name is ceil() which takes a sole numeric parameter. To use the ceiling function in Python you will need to import the math library, like so: 1 2 3 4 5 >>> import math >>> math.ceil(1.6) 2 >>> math.ceil(-2.1) -2 As you can see from the above Python REPL code the output produces the greatest integer. ...

How can you apply conditional formatting on checkbox cells in Google Sheets? Conditional formatting in Google Sheets can be applied to a range of checkboxes by applying the conditional formatting condition of Is equal to to TRUE. Here’s an example demonstrating how to set a conditional format on checkboxes. Create Checkbox Range To create a range of checkboxes, select your range and then click on the Data Validation menu item. ...