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. ...
What is a named function in Google Sheets and when is it best to use this new feature in your spreadsheet? Google Sheets new Named Functions feature enables you to refactor long formulas into what appears as a native function in your Google Sheets spreadsheet. Use this feature in Google Sheets if you find you are using a complex formula more than once within your Google Sheets. A recent need I found was with a finance spreadsheet which helped to calculate the right amount of tax to withhold. The original formula referenced a tax table that provided the variables to complete a linear function. ...
How do you use the Google Sheets SWITCH() formula? The SWITCH() formula in Google Sheets enables you to compress a series of IF statements, even nested IF statements, into one succinct function. Take a recent example where I refactored the following formula which helped to add the appropriate years, months or days to an existing date. Here was the original formula which would calculate the next date according to an increment type: ...
How can you set a default value when using the VLOOKUP function? As VLOOKUP throws an #N/A error when the searched item cannot be found in the first column range, wrap the VLOOKUP function in an IFERROR formula and set the value to the default sought. For example like this: = I F E R R O R ( V L O O K U P ( s e a r c h , r a n g e , c o l u m n _ i d x , m a t c h _ t y p e ) , d e f a u l t _ v a l u e ) Where default_value is the placeholder where you would the data inserted into your spreadsheet if the item is not found in the first column of the range. ...