Index.match is an alternative to the traditional vlookup or hlookup. The formula differs in that it can return values in cells both vertically and horizontally as well not needing to be based on the left-most column of a spreadsheet like vlookup. Index-Match works by having Excel scan a table to find a specific cell based on a lookup value that you designate. In one of my examples I use the formula to select a student and a standard and Excel returns the score of that particular standard.
Normal Distribution is an important statistical measure because it allows you to easily work with large amounts of variables and model the observed frequency of events. In the example I use over 2000 ACT scores are analyzed to show that the majority of test takers fall within the standard normal distribution of scores. To put it in easy to understand terms, 68% of testers fall within the average range. In the video I show you how to calculate the data needed to created a normal distribution graph and explain the concept of standard deviation.
The frequency formula allows you to determine how often something (in this case grades) falls between a certain range. The formula uses an array to do multiple calculations and return how many grades fall between, say a 51-70.
Over time I have found that the following five formulas are some of the most useful for teachers when working with data and spreadsheets. Each of these formulas can automate something teachers use often or provide them with a more detailed analysis of data.
1) Averageif – allows teachers to create a condition to a range of data that is going to be averaged. Example: Average on the scores that fall between 60-69.9
2) Countif – allows teachers to count a range of data based on a condition. Example: count only those students who made basic.
3) Concatenate – combines data from cells into one cell. Example: you could add @student.rcschools.net to end of student usernames to quickly generate their email addresses.
4) Proper – allows teachers to change the text to first initial uppercase and the rest of the name lowercase. Example: Export from iNow makes all names capital and you want them to be proper. JASON SLOAN becomes Jason Sloan.
5) If/Then Statements – allows teachers to make conditional statements based on data in a cell. Example: If you wanted to have all scores between 80-85 be proficient you could use an if then statement to automate this process.
In this video I show you how to use the Alignment section in the Home tab. The key features here are wrapping text in a cell, text alignment, and text orientation. Excel allows you to determine where text will be positioned in a cell both vertically and horizontally and also the orientation of the text. Text can be aligned horizontally at 0 degrees or vertically at 180 degrees in the Alignment section. Lastly, it is in this section of the Home tab that you will find how to merge cells.
Welcome back to school! In this blog I want to show you how you can import external data from a webpage. This is a simple process that can save you time when trying to sets of data from the internet. Importing the data is different from a copy and paste because when you import the data Excel creates a connection that allows the data to be updated when you open the spreadsheet.
In each Office product there exists a quick access toolbar in the upper left hand corner that is customizable. In this video I will show you how the toolbar works and how to customize it, making common commands easily accessible.
Having email accounts for all students in a school is a very handy feature in Office 365. However sending stuff in mass to them can be tricky if you try to do it in your normal methods. In this video we take the data we built in Excel yesterday and used it to fill a word document with student specific information and then mail it to them. Using the mail merge function in Word we wrote an email and then populated it with our Excel data and mailed it easily. This is a great method when trying to send emails to hundreds or even thousands of people.
In this video I will show you how we can create simple usernames by concatenating users first initial, last name, and 2 digit birth date. To do this we combine concatenate with a second formula that will pull just the first character in a cell and combine it with other data. In the next blog I will take the information and build it into a mail merge using Word and Excel.
The Watch Window is a handy tool that allows you to monitor a cell or a range of cells as you are working in Excel. The watch window will stay on top and constantly update as you work. It is located in under the Formulas Tab and can be adjusted in it's height and width. when you are working with large amounts of data and want to be able to see something specific it is a good tool to work with.