Waffle Bytes

(Part of my administrative assistant series on Google Sheets.) Below is a summary of all the topics covered in Session #3 of the series I have been doing for support staff.  This one covered several "intermediate" formulas available to use with Google Sheets.  We especially focused on formulas that would be useful to those school employees that would be working with large numbers of student data. Session 3 - Intermediate Formulas (November 29, 2017) Split Formula Concatenate Formula Count Formulas Hyperlink Formula

(Part of my administrative assistant series on Google Sheets.) When dealing with a large (or even small) amount of data, being able to quickly get some counts can be done very quickly using the count formulas.  Lots of people know the basic one, but what many do not know is there are several different count formulas that give you great flexibility in counting your data for exactly what you want.  COUNT Counts the number of cells in the selected range that have numeric data entries. Example: =COUNT(A1:A30) Here is an example of it - COUNTA Counts the number of cells in the selected range that have any data type  (non-empty cells). Example:  =COUNTA( A1:A30 ) Here is an example of it - COUNTBLANK Counts the number of cells in the selected range that have no data (empty cells). Example:  =COUNTBLANK( A1:A30 ) Here is an example of it -  COUNTUNIQUE Counts the number of cells with unique data entries in the the selected r...

(Part of my administrative assistant series on Google Sheets.) A question that came up during one of these Google Sheets webinars was how to have a hyperlink in a Google Sheet.  More specifically, how to have "normal" text hyperlinked in a cell instead of having the URL what is visible in a cell.  This actually can be taken care of with the HYPERLINK formula. HYPERLINK Adds an active hyperlink to provided text (or data) within the cell. Example: =HYPERLINK("http://www.google.com","Google") Here is an example of it -

(Part of my administrative assistant series on Google Sheets.) The other task that can be labor intensive without a formula is joining fragments together into a single cell.  I can actually remember spending a whole DAY once creating student accounts that followed a certain pattern so I could upload them for auto-account creation in an online system.  This was for a single grade...and certainly not so "auto" especially considering I could have saved my day as there is an EASY way to do this :-) Similar to SPLIT, the formula needs to be applied to each item you want to split - however, using the fill capability can make that quick work.   CONCATENATE Joins data together in one cell; it can use cell references and/or fixed data.  If it is only 2 pieces of data, it can be shortened to CONCAT Example: =CONCAT(C2, "19") This will join the value of 19 to the value that is in C2. Here is an example of it -  Example:  =CONCATEN...

(Part of my administrative assistant series on Google Sheets.) There are some formulas that are well worth knowing when you are working with student data.  For instance, the number of times that you have a CSV download which has the name in one cell, rather than divided into one cell for the first name and one for the last name, has frustrated me and many others I know time and again.  Luckily, there is the SPLIT formula - which can take care of dividing the name up for you in quick fashion.   SPLIT Splits up the data, using an identified delimiter, putting each fragment into different consecutive cells in the row. Example: =SPLIT(A2, " ") This will use a space as a delimiter.  Here is an example of it -  The formula needs to be applied to each item you want to split - however, using the fill capability can make that quick work.   Where this is awesome is it's flexibility since you provide the delimiter.  So s...

(Part of my administrative assistant series on Google Sheets.) Below is a summary of all the topics covered in Session #2 of the series I have been doing for support staff.  This one covered the basics of using formulas, and then provided examples of several commonly used formulas.  The next session dives into intermediate level formulas. Session 2 - Basics of Formulas (November 8, 2017) Basics of Formula Entry Basic Formulas

(Part of my administrative assistant series on Google Sheets.) These are some of the most basic - and most used functions - in Google Sheets.  If you are doing any of these by hand, it is time to begin using formulas to automate the process. NOTE: When it says "selected data" this could be a typed in number OR a referenced cell address; this could also be a combination of both. Basic Operations : + (addition) Adds two selected data. Example: =C2+30 - (subtraction) Subtracts the second data referenced from the first data referenced. Example: =100-D5 * (multiplication) Multiples the two selected data. Example: =C2*D2 / (division) Divides the first referenced data by the second. Example: =E3/2 Order of Operations If the formula is a longer string, standard order of operations is ALWAYS followed: parenthesis/brackets calculated first powers calculated next multiplication/division calculated next addition/subtraction calculated last ...

(Part of my administrative assistant series on Google Sheets.) Here are some basics you should know about when working with formulas in Google Sheets. 1. Formulas start with an equal sign (=) You must use an equal sign at the beginning of your formula.  Otherwise, no math will happen.  You might note that the menus go gray as soon as you type the equal sign - . 2. Filling with a Formula Just as you can "fill" for quick data entry, you can fill when using the same formula multiple times either across or down. Grab the little blue box in the lower right corner of the cell and drag either over or down to apply the formula in other rows/columns.  Notice the cursor changes to a crosshair when you are filling - 3. Understand Cell References Cell references are when you use a cell address to refer to the location of the wanted data instead of the actual data.  Used in a formula, it allows you to do the following: Utilize the fill feature - each insta...

Getting averages of data in a Google spreadsheet is easier than ever. But did you know there are different ways to average which let you handle all kinds of situations?  Let's start with the basic and work from there... Method #1 : Viewing the Average with the Quick Menu If you highlight any range of cells on your spreadsheet, you can quickly get the average by clicking on the drop-down menu in the bottom right corner - it probably says Sum: by default, but clicking on it you can change it to Ave: - to get the average of the selected cells.  A very quick way to see the average, the drawback is that it is not on your spreadsheet so if you have other formulas that need to use the average it isn't a value on your spreadsheet. Method #2 : Using the Average Formula In any empty cell on your spreadsheet, you can use the =AVERAGE formula.  It is a formula that only has 1 criterion - the range of cells to use in calculating the average.  If they are not cons...

Many people that use sheets know how to count the number of cells with numerical entries using the =COUNT function.  For example, here's a spreadsheet where I've documented my running minutes and I want to know how many days I ran: Pretty simple.  But what if I want to know how many cells within a range have alphanumeric entries?  Or what if I want to know how many cells within a range meet certain criteria? There are actually a whole series of count functions available in Google Sheets: COUNTA : will count the number of cells with any entry (nonblank) in a range.  For example, I've added in my strength & conditioning workouts on those days.  Using =COUNTA(range) I can document the total number of days I worked out whether it was running or another workout. COUNTIF : counts the number of cells within a range that meet a certain criteria.  This formula has two pieces - the range to check, followed by the criteria to match.  If the criter...

Let's say you have put together a sheet with information you THINK is how you want it.  And after using it for awhile, you realize you REALLY want the columns and rows switched.  Can you easily remedy this without re-entering all the data?  The answer is yes!  Before walking through how to easily do this, let me introduce you to a Google Sheets formula you may not know - it's called TRANSPOSE and it's purpose is to transpose the rows and columns in an array or range.  So, here is some data I have in cells A1-E2:  If I want to flip the columns and rows, I add the formula: =TRANSPOSE(A1:E2) in the cell I want to start the transposed information into.  And...viola!  My data is flipped: It needs to be noted that you CANNOT change data in the transposed cells.  As soon as you try to change the data in the transposed area you will get a REF! error because you have data in a cell it need to do the transposing.  The data is dyna...