When working with an array of different data sets you’ll undoubtedly come across the issue where you want to perform a count on your data (or something similar) and you want to exclude the blank or empty cells in your Google Sheets. There are several ways to check if a cell is blank and you can use either type of these expressions to check. Here is a short list of operators and functions that can assist: ...
Continuing my work on RTU assets from yesterday, I found I needed to program some of the common formulas you see in Google Sheets into Javascript to be used in my Suitescript code. These common functions include: PV(rate, number_periods, payment_amount, future_value, end_or_beginning) FV(rate, number_periods, payment_amount, present_value, end_or_beginning) PMT(rate, number_periods, present_value, future_value, end_or_beginning) Each of the above functions comes in handy when trying to calculate the present value of an annuity, investment or mortgage. ...
How do you highlight an entire row in Google Sheets on a table of data where a column in your table containing dates matches the same month and year as today? For this technique, you will need to use the Conditional Formatting section in Google Sheets and a Custom Formula. Here’s the Custom Formula I needed to enter (I’ll explain it underneath so that you can amend it for your use case): ...
How can you highlight a column of dates based upon a condition where comparison is needed with the current column of dates to another column containing dates? I had a requirement where I needed to compare a column of dates to another column of dates and if a specific date was between those dates to highlight one of the columns. For example, I needed to highlight a cell when the start date and the end date crossed over the end of the financial year. As the end of the financial year in Australia is 30th June each year if the start date was before the 30th June and the end date was after the 30th June then I wanted to highlight the row. ...
How can you randomise a range in Google Sheets without needing a plugin or Google App Script? To randomise a range, simply use the RAND() formula in a column to set the random numbers for each element in your range, and then with the INDEX() and RANK() formulas combined, produce the new random range. Here’s an example of what this looks like in a Google Sheet spreadsheet starting with the range you’d like to randomise. ...
How can you check if a cell or value is not null in Google Sheets? There are several ways to check if a cell is not null in Google Sheets with formulas like ISBLANK() or simple operator checks like "<>" with formulas like AVERAGEIF and COUNTIF. Similar to a previous post I wrote on checking if a cell is empty the check for a cell not being null is a little of the same but also a little bit different. ...
Are you looking to learn how to use the Google Sheets QUERY function to select and filter data based on specific conditions? In this blog post, you’ll explore the ins and outs of the powerful QUERY function, particularly focusing on SELECT and WHERE clauses, to help you get the most out of your Google Sheets experience. In the following sections, I’ll start with an introduction to Google Sheets and its basic functions to ensure you’ve got a solid foundation. From there, I’ll dive into the QUERY function, examining its syntax, purpose, and the integral role of SELECT and WHERE clauses within the function. ...
As someone who has been using Excel for several years, I know how important it is to understand the basics of the program. One of the most fundamental elements of Excel is the sheet, which is essentially a grid of cells that can be filled with data. Sheets in Excel are used to organise and manipulate data in a variety of ways. Each sheet can contain a vast amount of information, from simple lists to complex calculations and charts. Sheets can be renamed, reordered, and even hidden to make it easier to work with large amounts of data. ...
Sorting data in Excel is a fundamental skill that can help you better analyse and understand your data. Whether you are working with a small or large dataset, sorting can help you quickly identify patterns, trends, and outliers. Excel provides several options for sorting data, depending on your specific needs. You can sort data by one or more columns, in ascending or descending order, and even customise the sort order based on your own criteria. Sorting can be done manually, using the Sort command, or automatically, using filters or pivot tables. ...
What is a cell in Excel? Once you dive into learning Excel it doesn’t take long before you discover a whole new world with its own language! And cells are a frequent term used, so what is it? Simply put, cells are the individual unit of an Excel grid spreadsheet. Each cell can contain a piece of data, such as a number, text, or formula, and can be formatted in various ways to make it easier to read and understand. ...