Blog

Stop Watch Event Recorder

This is a MACRO-ENABLED Excel File for timing your events and recording the time on a log table.

The START button starts the timer. The STOP button stops the timer while the RESET button resets the timer and and stores the time on the log table.

This is an editable workbook, so you can tweak to suit your purpose.

“To Do List” With Priority Setting

This is a Microsoft Excel Template that records up to 12 events to be carried out.

The priority of the event is set from “1”, top priority to “5”, least priority.

After every completed event, you click the checkbox in front of it. The percentage performance is calculated automatically.

 

QUIZ WHIZ APP

 

SimpleSchool QUIZ WHIZ- An editable and interactive quiz platform for both individual and group assessment with 1000+ Question-Bank and Timer. Good for mental drills, reviews, set induction, evaluation of concepts, group quiz competition and more…
It can be used on PC and on Interactive White Board.

It Contains 3 sections:

1. Instruction Page

The Instruction/Home page is what you see when you load the application. It contains information and instruction on how to use the App.
 

2. Questions Bank

This is a database where questions are stored. You can store up to 1000 questions under 10 categories (100 questions per category). You can make each category store questions on different subjects.

 

To Upload or Type Questions in the Questions Bank

 
  • Double-Click and type in the Category/Subject.
  • Double-Click and type the questions in the questions box one after the other under your chosen category.
  • Type the options in the corresponding boxes.
  • Type the letter of the correct option in the ANSWER column.
  • For a question to appear on the quiz platform, click the CHECK BOX in front of the question.
  • You can click “Select all” at the top to click all the check boxes at once.

3. Quiz Platform

This is the section where the quiz is displayed.

 

SimpleSchool QUIZ WHIZ is used majorly for two purposes:

  1. QUIZ Competition: This is a standard Quiz Competition where participants choose their questions and answer them accordingly. The timer can be set to any time of choice. Participants receive and instant prompt when they are right or wrong.

 
To Start The Quiz Competition
  • Click the Quiz Test button
  • Fill in the participants (you can have up to 10 names), Question category, marks per question, timer and No of questions.
  • Participants names are arranged and selected in the order.
  • Select question number according to participants preference from the questions drop down.
  • Click the Start Button if you want each question timed.
  • After the question is answered, the Marks Box shows up. The App tells you if your answer is correct or wrong.
  • Give marks accordingly
 

2. Timed Test: This is a situation where a number of questions is set with time for each question to appear. At the end of the test the summary of the performance is displayed.

 

To Start The Timed Test

  • Click the Timed Test button
  • Fill in the participant, Question category, marks per question, timer and number of questions.
  • Click Start Button to start the test.
  • The Summary table is shown after the last question.

The Overview of The Quiz Platform

 
A: QUIZ BOARD: This is where the questions are displayed.
 
B: ANSWER TABS: The answers are displayed in their respective options. You select an answer by clicking the letter for the option. Then you get the question “Is that your final answer?” You answer appropriately.
 
C: SCORE BOARD: This is where the participants scores are displayed. The scores sum up automatically. In case of quiz competition. You can click the “Input Scores” tab to add score manually.
 
D: TIMER (START AND STOP): This shows the Count Down Time. For Quiz Competition, you will have to click the Start button after you select the question. For the Timed Test, the Timer starts automatically.
 
E: QUESTION SELECTOR: You click the drop-down arrow to select question of your choice when working with Quiz Competition.
 
F: QUIZ STARTER TAB: You click this tab to start the Quiz. You then fill the dialog box accordingly.
 
G: TIMED TEST STARTER TAB: You click this tab to start the Timed Test. You then fill the dialog box accordingly.
 
H: HIDE OPTIONS TAB: You double-Click this tab to hide options. Click once to show options again.
 
I: SUMMARY TAB: Click tab to show summary table. Summary table shows the questions you have answered and their correct answers. It also shows you the number of questions you got right. If you are 100% right “Stand up for the Champion” is sung.
 
J: TEST PROGRESS BAR: This shows the progress of your test, especially the timed test.
 
K: NAME OF CURRENT PARTICIPANT: Shows the name of the current participant as inputted by you.
 
L: CURRENT QUESTION: This shows the number of the current question on the quiz board.
 
M: QUESTIONS BANK AND HOME PAGE TAB: You click the Question Bank tab to go to the questions Bank Page. Clicking the Home Page tab takes you to the Home/Instruction Page.
 

N: CURRENT CATEGORY: This displays the current category.

 

Number Bond App 2- With 10 Timed Questions

 

INTRODUCTION

This is an updated version of ‘My Number Bond App.’ This version includes the 10 timed self graded questions with instant result. All the functionalities of the previous version remains. To get details of the other component Go to My Number Bond App 1

My Number Bond App 2

In order to make the teaching and learning of this important fundamental Mathematical concept easy to understand, Prime Academy Resource made this Microsoft Excel App called “My Number Bond.”
 
NUMBER BOND 1 VIDEO

NUMBER BOND 2 VIDEO

 

How Does My Number Bond App Works?

When you load the Workbook you have this interface:
 
 
You are required to update the setting form to suit your needs.

The Setting Form

 


Here you set the following:

  • DECIMAL PLACE: The decimal place tab has 3 options
    • None
    • 1 Decimal Place and
    • 2 Decimal Place
You click the drop down arrow to select your preference.
 
  • MATH OPERATION: This tab has the 4 basic operations.
  • +
  • *
  • ÷
Select the operation of your choice
 
  • NUMBER SUM: This is the total sum you want to work with. You can choose any number of your choice
  • MISSING NUMBER: Here you select which number you want as the missing value. The first or the second.
 
  • SET TIMER: Here you set the time for your 10 random question. Each question will last the time you choose. 


When you are done with the setting. You Click UPDATE. This will effect your changes.

The Main Game

After you’ve set up your preferences then you are good to go with this awesome App.
Depending on your setting you have the interface to work with. When you click the start button the 10 random questions starts depending on the time you set on the setting tab. After the 10 questions, you have your result immediately.
 

 
 
 

When you calculate and have your answer for the missing value you click on the NUMBER PAD to insert your answer in the missing box.

 

The Number Pad

With the Number Pad, you can type in your answer, delete your answer and make corrections. The help of the Number Pad makes the App more interactive in the classroom. The students can use it on the Interactive White Board (SMART BOARD). They will not need the computer keyboard to type their answer, they will use the Number Pad in the App. REMEMBER TO CLICK ENTER AFTER AFTER YOU TYPE YOUR ANSWER!!!
 
NOTE: You can also use the computer keyboard to type in your answer then you press ENTER.
 

Summary Table

The summary table comes up automatically after your 10th question. Here you have the details of the correct answers to your questions and the number of questions you got right.
 

Conclusion

This is an upgrade to the previous version. In this version the 10 self graded random question is added with timing of your choice. Feel free to read more on the previous version for detail explanation on each component of the App.

 Thank you. Please Subscribe, Share and Comment.

Number Bond App 1.0

HAPPY TEACHERS' DAY PROMO. Share and Get 100% Discount

Simply share this and get the required discount instantly. Offer closes soon.

INTRODUCTION

Number bond is a mental picture of the relationship between a number and the parts that combine to make it. The concept of number bonds is very basic, an important foundation for understanding how numbers work. A whole thing is made up of parts. If you know the parts, you can put them together (add) to find the whole. If you know the whole and one of the parts, you take away the part you know (subtract) to find the other part.
Number bonds let children see the inverse relationship between addition and subtraction. Subtraction is not a totally different thing from addition; they are mirror images. To subtract means to figure out how much more you would have to add to get the whole thing.
Children start to learn about number bonds in the Foundation stage, when they might be given a number, such as 5, and then asked to select two groups of objects that will add up to that number.
 
Number bonds in Year 1
 
Children are expected to know number bonds to 10 and number bonds to 20.
 
Number bonds in Year 2
 
Children by now need to be very confident with their number bonds to 20. They need to be able to work out number bonds to 100. They also need to be confident with the corresponding subtraction facts (for example: 20 – 13 = 7).
 
Number bonds in Key Stage 2
 
In Key Stage 2, children move onto being able to work out number bonds to 1000 (e.g. 450 and 550) and number bonds to 1 (e.g. 0.8 and 0.2).
 
Teachers teach number bonds in a variety of ways. When learning number bonds to 5 or 10 in Key Stage 1 it is always good to use pictorial representation, so a teacher might show rows of blocks shaded like this to make the concept clear.

NUMBER FACTS

Number facts are basic addition, subtraction, multiplication and division calculations that children should learn to recall instantly with no working out (in other words, they need to learn them off by heart).
 
Number facts are sometimes referred to as number bonds (addition and subtraction) and times tables with related division facts (multiplication and division).

My Number Bond App

In order to make the teaching and learning of this important fundamental Mathematical concept easy to understand, Prime Academy Resource made this Microsoft Excel App called “My Number Bond.”
 
 
 

 

CLICK FOR UPDATED NUMBER BOND WITH TIMER AND 10 TIMED QUESTIONS

 
 

How Does My Number Bond App Works?

When you load the Workbook you have this interface:
 
 
You are required to update the setting form to suit your needs.

The Setting Form


Here you set the following:
  • DECIMAL PLACE: The decimal place tab has 3 options
    • None
    • 1 Decimal Place and
    • 2 Decimal Place
You click the drop down arrow to select your preference.
 
 
  • MATH OPERATION: This tab has the 4 basic operations.
    • +
    • *
    • ÷
Select the operation of your choice
 
 
  • NUMBER SUM: This is the total sum you want to work with. You can choose any number of your choice
  • MISSING NUMBER: Here you select which number you want as the missing value. The first or the second.
 
When you are done with the setting. You Click UPDATE. This will effect your changes.

The Main Game

After you’ve set up your preferences then you are good to go with this awesome App.
Depending on your setting you have the interface to work with.

 

When you calculate and have your answer for the missing value you click on the NUMBER PAD to insert your answer in the missing box.

The Number Pad

With the Number Pad, you can type in your answer, delete your answer and make corrections. The help of the Number Pad makes the App more interactive in the classroom. The students can use it on the Interactive White Board (SMART BOARD). They will not need the computer keyboard to type their answer, they will use the Number Pad in the App.
 
NOTE: Always close the Number Pad(click the X button at the top right corner of the Number Pad) when your done typing your answer, OR click the enter button on the Number Pad to remove it.

The Reset Button

The Reset button gives you a different question any time you click it. So, to have a new question, you click the Reset Button.

When Your Answer is Right

When Your Answer is Right

You can continually change your answer until you get the right answer.

 

 

CLICK FOR UPDATED NUMBER BOND WITH TIMER AND 10 TIMED QUESTIONS

 
 

Summary Table

In case you get stuck on a question or you just want to see the summary of the questions you’ve answered, you click any of the two stars:
 
Then, you’ll have the summary table:

 

The table shows the detail of the questions you have attempted since the last time you clicked the SETTING button. The table shows the questions, the answers and the tip for solving the questions.

Conclusion

To make students understand the basics of number facts and relationship:

 

Don’t “Drill.” Play Games!


Here are a few number bond games to enjoy with your children:

  • Throw two dice and tell how many more you would need to make 10.

        (On the rare throws of 11 or 12, the answer is a negative number.)

  • Throw 3 dice and tell how many more it takes to make 20.
  • One player names any number 0-100, and the other tells how many more it takes to make 100.
  • You could also play the last game with math cards[take out the jokers and face cards, leaving just ace (1) through 10], turning up one for the tens place and one for the ones, to make a two digit number.
  • 10’s Concentration — Turn all the math cards face down on the table. On her turn, each player turns up two cards. If they add up to 10, she gets to keep them and try again. If one of the cards is a 10, she gets to keep it and turn up another card. Whoever takes the most cards, wins.

 Thank you. Please Subscribe, Share and Comment.

Excel Formula Expert 2

Excel Formulas in View

If you’re new to Excel, you’ll soon find that it’s more than just a grid in which you enter numbers in columns or rows. Sure, you can use Excel to find totals for a column or row of numbers, but you can also calculate a mortgage payment, solve math or engineering problems, or find a best case scenario based on variable numbers that you plug in.
 
Excel does this by using formulas in cells. A formula performs calculations or other actions on the data in your worksheet. A formula always starts with an equal sign (=), which can be followed by numbers, math operators (like a + or – sign for addition or subtraction), and built-in Excel functions, which can really expand the power of a formula.
 
For example, the following formula multiplies 2 by 3 and then adds 5 to that result to come up with the answer, 11.
 

=2*3+5

 
Here are some additional examples of formulas that you can enter in a worksheet.
 
  • =A1+A2+A3 Adds the values in cells A1, A2, and A3.
  • =SUM(A1:A10) Uses the SUM function to return sum of the values in A1 through A10.
  • =TODAY() Returns the current date.
  • =UPPER(“hello”) Converts the text “hello” to “HELLO” by using the UPPER function.
  • =IF(A1>0) Uses the IF function to test the cell A1 to determine if it contains a value greater than 0.

The parts of an Excel formula

A formula can also contain any or all of the following: functions, references, operators, and constants.
 

Parts of a formula

 
 
1. Functions: The PI() function returns the value of pi: 3.142…
 
2. References: A2 returns the value in cell A2.
 
3. Constants: Numbers or text values entered directly into a formula, such as 2.
 
4. Operators: The ^ (caret) operator raises a number to a power, and the * (asterisk) operator multiplies numbers.

Using constants in Excel formulas

A constant is a value that is not calculated; it always stays the same. For example, the date 10/9/2008, the number 210, and the text “Quarterly Earnings” are all constants. An expression or a value resulting from an expression is not a constant. If you use constants in a formula instead of references to cells (for example, =30+70+110), the result changes only if you modify the formula. In general, it’s best to place constants in individual cells where they can be easily changed if needed, then reference those cells in formulas.
 

Using calculation operators in Excel formulas

Operators specify the type of calculation that you want to perform on the elements of a formula. Excel follows general mathematical rules for calculations, which is Parentheses, Exponents, Multiplication and Division, and Addition and Subtraction, or the acronym PEMDAS (Please Excuse My Dear Aunt Sally). Using parentheses allows you to change that calculation order.
 
Types of operators. There are four different types of calculation operators: arithmetic, comparison, text concatenation, and reference.

 

Arithmetic operators

To perform basic mathematical operations, such as addition, subtraction, multiplication, or division; combine numbers; and produce numeric results, use the following arithmetic operators.

 

Comparison operators

You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value—either TRUE or FALSE.

 

Text concatenation operator

Use the ampersand (&) to concatenate (join) one or more text strings to produce a single piece of text.

 
 
 

Reference operators

Combine ranges of cells for calculations with the following operators.
 
 

The order in which Excel performs operations in formulas

In some cases, the order in which a calculation is performed can affect the return value of the formula, so it’s important to understand how the order is determined and how you can change the order to obtain the results you want.
 

Calculation order

Formulas calculate values in a specific order. A formula in Excel always begins with an equal sign (=). Excel interprets the characters that follow the equal sign as a formula. Following the equal sign are the elements to be calculated (the operands), such as constants or cell references. These are separated by calculation operators. Excel calculates the formula from left to right, according to a specific order for each operator in the formula.
 

Operator precedence in Excel formulas

If you combine several operators in a single formula, Excel performs the operations in the order shown below. If a formula contains operators with the same precedence—for example, if a formula contains both a multiplication and division operator—Excel evaluates the operators from left to right.
 

Using parentheses in Excel formulas

To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula produces 11 because Excel performs multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3
In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21.
=(5+2)*3
In the following example, the parentheses that enclose the first part of the formula force Excel to calculate B4+25 first and then divide the result by the sum of the values in cells D5, E5, and F5.
=(B4+25)/SUM(D5:F5)

Using functions and nested functions in Excel formulas

 
Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. Functions can be used to perform simple or complex calculations. You can find all of Excel’s functions on the Formulas tab on the Ribbon:
 

Excel function syntax

The following example of the ROUND function rounding off a number in cell A10 illustrates a function’s syntax.
1. Structure. The structure of a function begins with an equal sign (=), followed by the function name, an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis.
 
2. Function name. For a list of available functions, click a cell and press SHIFT+F3, which will launch the Insert Function dialog.
 
3. Arguments. Arguments can be numbers, text, logical values such as TRUE or FALSE, arrays, error values such as #N/A, or cell references. The argument you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions.
 
4. Argument tooltip. A tooltip with the syntax and arguments appears as you type the function. For example, type =ROUND( and the tooltip appears. Tooltips appear only for built-in functions.
 
NOTE: You don’t need to type functions in all caps, like =ROUND, as Excel will automatically capitalize the function name for you once you press enter. If you misspell a function name, like =SUME(A1:A10) instead of =SUM(A1:A10), then Excel will return a #NAME? error.
 

Entering Excel functions

When you create a formula that contains a function, you can use the Insert Function dialog box to help you enter worksheet functions. Once you select a function from the Insert Function dialog Excel will launch a function wizard, which displays the name of the function, each of its arguments, a description of the function and each argument, the current result of the function, and the current result of the entire formula.
 
To make it easier to create and edit formulas and minimize typing and syntax errors, use Formula AutoComplete. After you type an = (equal sign) and beginning letters of a function, Excel displays a dynamic drop-down list of valid functions, arguments, and names that match those letters. You can then select one from the drop-down list and Excel will enter it for you.
 

Nesting Excel functions

In certain cases, you may need to use a function as one of the arguments of another function. For example, the following formula uses a nested AVERAGE function and compares the result with the value 50.
 
1. The AVERAGE and SUM functions are nested within the IF function.
Valid returns When a nested function is used as an argument, the nested function must return the same type of value that the argument uses. For example, if the argument returns a TRUE or FALSE value, the nested function must return a TRUE or FALSE value. If the function doesn’t, Excel displays a #VALUE! error value.
 
Nesting level limits A formula can contain up to seven levels of nested functions. When one function (we’ll call this Function B) is used as an argument in another function (we’ll call this Function A), Function B acts as a second-level function. For example, the AVERAGE function and the SUM function are both second-level functions if they are used as arguments of the IF function. A function nested within the nested AVERAGE function is then a third-level function, and so on.
 

Using References in Excel Formulas

 
A reference identifies a cell or a range of cells on a worksheet, and tells Excel where to look for the values or data you want to use in a formula. You can use references to use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, and to other workbooks. References to cells in other workbooks are called links or external references.

The A1 reference style

By default, Excel uses the A1 reference style, which refers to columns with letters (A through XFD, for a total of 16,384 columns) and refers to rows with numbers (1 through 1,048,576). These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number. For example, B2 refers to the cell at the intersection of column B and row 2.
 

Making a reference to a cell or a range of cells on another worksheet in the same workbook

In the following example, the AVERAGE function calculates the average value for the range B1:B10 on the worksheet named Marketing in the same workbook.
 
1. Refers to the worksheet named Marketing
2. Refers to the range of cells from B1 to B10
3. The exclamation point (!) Separates the worksheet reference from the cell range reference
 
NOTE: If the referenced worksheet has spaces or numbers in it, then you need to add apostrophes (‘) before and after the worksheet name, like =’123′!A1 or =’January Revenue’!A1.
 

The difference between Absolute, Relative and Mixed References

Relative references:

A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy or fill the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy or fill a relative reference in cell B2 to cell B3, it automatically adjusts from =A1 to =A2.
 
Copied formula with relative reference
 
 

 

Absolute references:

An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy or fill the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, so you may need to switch them to absolute references. For example, if you copy or fill an absolute reference in cell B2 to cell B3, it stays the same in both cells: =$A$1.
 
Copied formula with absolute reference 
 

Mixed references:

A mixed reference has either an absolute column and relative row, or absolute row and relative column. An absolute column reference takes the form $A1, $B1, and so on. An absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, and the absolute reference does not change. If you copy or fill the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. For example, if you copy or fill a mixed reference from cell A2 to B3, it adjusts from =A$1 to =B$1.
 
Copied formula with mixed reference
 
 

The 3-D Reference Style

Conveniently referencing multiple worksheets: If you want to analyze data in the same cell or range of cells on multiple worksheets within a workbook, use a 3-D reference. A 3-D reference includes the cell or range reference, preceded by a range of worksheet names. Excel uses any worksheets stored between the starting and ending names of the reference.
For example, =SUM(Sheet2:Sheet13!B5) adds all the values contained in cell B5 on all the worksheets between and including Sheet 2 and Sheet 13.
 
  • You can use 3-D references to refer to cells on other sheets, to define names, and to create formulas by using the following functions: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA, and VARPA.
  • 3-D references cannot be used in array formulas.
  • 3-D references cannot be used with the intersection operator (a single space) or in formulas that use implicit intersection.
What occurs when you move, copy, insert, or delete worksheets: The following examples explain what happens when you move, copy, insert, or delete worksheets that are included in a 3-D reference. The examples use the formula =SUM(Sheet2:Sheet6!A2:A5) to add cells A2 through A5 on worksheets 2 through 6.
  • Insert or copy If you insert or copy sheets between Sheet2 and Sheet6 (the endpoints in this example), Excel includes all values in cells A2 through A5 from the added sheets in the calculations.
  • Delete If you delete sheets between Sheet2 and Sheet6, Excel removes their values from the calculation.
  • Move If you move sheets from between Sheet2 and Sheet6 to a location outside the referenced sheet range, Excel removes their values from the calculation.
  • Move an endpoint If you move Sheet2 or Sheet6 to another location in the same workbook, Excel adjusts the calculation to accommodate the new range of sheets between them.
  • Delete an endpoint If you delete Sheet2 or Sheet6, Excel adjusts the calculation to accommodate the range of sheets between them.
 

The R1C1 Reference Style

 
You can also use a reference style where both the rows and the columns on the worksheet are numbered. The R1C1 reference style is useful for computing row and column positions in macros. In the R1C1 style, Excel indicates the location of a cell with an “R” followed by a row number and a “C” followed by a column number.
 
When you record a macro, Excel records some commands by using the R1C1 reference style. For example, if you record a command, such as clicking the AutoSum button to insert a formula that adds a range of cells, Excel records the formula by using R1C1 style, not A1 style, references.
 
You can turn the R1C1 reference style on or off by setting or clearing the R1C1 reference style check box under the Working with formulas section in the Formulas category of the Options dialog box. To display this dialog box, click the File tab.
 

Using names in Excel formulas

You can create defined names to represent cells, ranges of cells, formulas, constants, or Excel tables. A name is a meaningful shorthand that makes it easier to understand the purpose of a cell reference, constant, formula, or table, each of which may be difficult to comprehend at first glance. The following information shows common examples of names and how using them in formulas can improve clarity and make formulas easier to understand.
Example 1
 
Example 2
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
 
NOTE: In the formulas in columns C and D, the defined name “Sales” is substituted for the reference to (range) A9:A13 and the name “SalesInfo” is substituted for the range A9:B13. If you don’t create these names in your test workbook, then the formulas in D2:D3 will return the #NAME? error.
 
 

Types of names

There are several types of names that you can create and use.
 
Defined name: A name that represents a cell, range of cells, formula, or constant value. You can create your own defined name. Also, Excel sometimes creates a defined name for you, such as when you set a print area.
 
Table name: A name for an Excel table, which is a collection of data about a particular subject that is stored in records (rows) and fields (columns). Excel creates a default Excel table name of “Table1”, “Table2”, and so on, each time you insert an Excel table, but you can change these names to make them more meaningful.
 

Creating and entering names

You create a name by using the:
  • Name box on the formula bar: This is best used for creating a workbook level name for a selected range.
  • Create a name from selection: You can conveniently create names from existing row and column labels by using a selection of cells in the worksheet.
  • New Name dialog box: This is best used for when you want more flexibility in creating names, such as specifying a local worksheet level scope or creating a name comment.
NOTE: By default, names use absolute cell references.
 
You can enter a name by:
  • Typing: Typing the name, for example, as an argument to a formula.
  • Using Formula AutoComplete: Use the Formula AutoComplete drop-down list, where valid names are automatically listed for you.
  • Selecting from the Use in Formula command: Select a defined name from a list available from the Use in Formula command in the Defined Names group on the Formula tab.
 

Using Array Formulas and Array Constants in Excel

An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. Some of the built-in functions are array formulas, and must be entered as arrays to get the correct results.
 
Array constants can be used in place of references when you don’t want to enter each constant value in a separate cell on the worksheet.
 
Using an array formula to calculate single and multiple results
 
NOTE: When you enter an array formula, Excel automatically inserts the formula between { } (braces). If you try to enter the braces yourself, Excel will display your formula as text.
  • Array formula that produces a single result: This type of array formula can simplify a worksheet model by replacing several different formulas with a single array formula.
For example, the following calculates the total value of an array of stock prices and shares, without using a row of cells to calculate and display the individual values for each stock.
 
When you enter the formula ={SUM(B2:D2*B3:D3)} as an array formula, it multiples the Shares and Price for each stock, and then adds the results of those calculations together.
 
  • Array formula that produces multiple results: Some worksheet functions return arrays of values, or require an array of values as an argument. To calculate multiple results with an array formula, you must enter the array into a range of cells that has the same number of rows and columns as the array arguments.
For example, given a series of three sales figures (in column B) for a series of three months (in column A), the TREND function determines the straight-line values for the sales figures. To display all the results of the formula, it is entered into three cells in column C (C1:C3).
 
 
When you enter the formula =TREND(B1:B3,A1:A3) as an array formula, it produces three separate results (22196, 17079, and 11962), based on the three sales figures and the three months.
 

Using Array Constants

In an ordinary formula, you can enter a reference to a cell containing a value, or the value itself, also called a constant. Similarly, in an array formula you can enter a reference to an array, or enter the array of values contained within the cells, also called an array constant. Array formulas accept constants in the same way that non-array formulas do, but you must enter the array constants in a certain format.
 
Array constants can contain numbers, text, logical values such as TRUE or FALSE, or error values such as #N/A. Different types of values can be in the same array constant — for example, {1,3,4;TRUE,FALSE,TRUE}. Numbers in array constants can be in integer, decimal, or scientific format. Text must be enclosed in double quotation marks — for example, “Tuesday”.
 
Array constants cannot contain cell references, columns or rows of unequal length, formulas, or the special characters $ (dollar sign), parentheses, or % (percent sign).
 
When you format array constants, make sure you:
  • Enclose them in braces ( { } ).
  • Separate values in different columns by using commas (,). For example, to represent the values 10, 20, 30, and 40, you enter {10,20,30,40}. This array constant is known as a 1-by-4 array and is equivalent to a 1-row-by-4-column reference.
  • Separate values in different rows by using semicolons (;). For example, to represent the values 10, 20, 30, and 40 in one row and 50, 60, 70, and 80 in the row immediately below, you enter a 2-by-4 array constant: {10,20,30,40;50,60,70,80}.
 

Delete a Formula

When you delete a formula, the resulting values of the formula is also deleted. However, you can remove just the formula and leave the resulting value of the formula displayed in the cell.
To delete formulas along with their resulting values, do the following:
  • Select the cell or range of cells that contains the formula.
  • Press DELETE.
To delete formulas without removing their resulting values, do the following:
  • Select the cell or range of cells that contains the formula.
If the formula is an array formula, select the range of cells that contains the array formula.
 
How to select a range of cells that contains the array formula
  1. Click a cell in the array formula.
  2. On the Home tab, in the Editing group, click Find & Select, and then click Go To.
  3. Click Special.
  4. Click Current array.
  • On the Home tab, in the Clipboard group, click Copy .
 
Keyboard shortcut; You can also press CTRL+C.
  • On the Home tab, in the Clipboard group, click the arrow below Paste , and then click Paste Values.

Avoid Common Errors when Creating Formulas

The following table summarizes some of the most common mistakes you can make when entering a formula and how to avoid formula errors:
 

Excel Formula Expert 1

 
 
 

What is Excel Formula ? 

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things that can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=). 
 
This Article is written for the people who does not know about Excel formula. This article answers following questions about Excel Formulas:
 
 
1. What is Excel Formula?
2. How to Use Excel Formula – a step by step tutorial.
3. How an Excel Formula works in Excel?
4. How to refer a Cell in Excel Formula?
5. What is Cell Address?
 

How to Use Excel Formula – Step by Step 

1. Select the Cell where you want to place your formula. 
2. Type the Equal (=) sign. 
 
Note: After typing = sign if you Click on Any other Cell then the Address of that Cell will be automatically placed there. So be careful while writing a formula in Excel. While writing formula DO NOT select any other cell, unless you want to refer that Cell in to your formula. Therefore you can refer a cell in to your formula in two ways: 
 
i) By typing the Address of that Cell (like A1, B8 etc) 
 
ii) By Selecting that Cell while writing your formula. 
 
3. Type the address of the cell to include in the formula or Select that Cell. 
 
4. Type the Formula as per the Syntax of that Formula 
 
5. Press Enter or Press Tab Key. (Again after writing the Complete formula, do not click somewhere else, instead you need to press Enter or Tab key Only)
6. And now you are done. 
 
 
Let’s take an example of Simple Formula in Excel. 
 
In Cell A1, I have value as 10 and in Cell B1, I have value 5. I want sum of these two Cell values in Cell C1. 
 
1. Click on Cell C1
2. Type Equal to Sign (=)
3. Now Take your mouse at Cell A1. Automatically the Cell Address A1 will be typed in Cell C1.
4. Now Type Plus Sign ( + )
5. Now Click on the Cell B1. (Same as previously this time B1 Cell Address will be typed)
6. Now It will look as same as shown in below: 
 
7. Now press Enter
8. Value of Cell A1 (i.e. 10) and Value of Cell B1 (i.e. 5) will be added and the Result will be shown in Cell C1 (Where formula is written). 
 
 
Note: In the above Example, you can see that the Value of Cell is referred by the Address of that Cell. It means in Excel Formula if you want to Refer the Value of Cell then you can refer it by passing the Address of that Cell.
Address of Cell is formed by Column Name and Row Number. It means if you want a Cell which is in Column J and Row 3 then the Address of the Cell will be J3.

The Power of Excel Formulas

One of the most powerful features in Excel is the ability to calculate numerical information using formulas. Just like a calculator, Excel can add, subtract, multiply, and divide. In this lesson, we’ll show you how to use cell references to create simple formulas.

Mathematical operators

Excel uses standard operators for formulas, such as a plus sign for addition (+), a minus sign for subtraction (-), an asterisk for multiplication (*), a forward slash for division (/), and a caret (^) for exponents.
 
All formulas in Excel must begin with an equals sign (=). This is because the cell contains, or is equal to, the formula and the value it calculates.

Understanding cell references

While you can create simple formulas in Excel manually (for example, =2+2 or =5*5), most of the time you will use cell addresses to create a formula. This is known as making a cell reference. Using cell references will ensure that your formulas are always accurate because you can change the value of referenced cells without having to rewrite the formula.
 

By combining a mathematical operator with cell references, you can create a variety of simple formulas in Excel. Formulas can also include a combination of cell references and numbers, as in the examples below:

To create a formula:

In our example below, we’ll use a simple formula and cell references to calculate a budget.
 

1. Select the cell that will contain the formula. In our example, we’ll select cell B3.

 

2. Type the equals sign (=). Notice how it appears in both the cell and the formula bar.

3. Type the cell address of the cell you want to reference first in the formula: cell B1 in our example. A blue border will appear around the referenced cell.

4. Type the mathematical operator you want to use. In our example, we’ll type the addition sign (+).
 

5. Type the cell address of the cell you want to reference second in the formula: cell B2 in our example. A red border will appear around the referenced cell.

6. Press Enter on your keyboard. The formula will be calculated, and the value will be displayed in the cell.

If the result of a formula is too large to be displayed in a cell, it may appear as pound signs (#######) instead of a value. This means the column is not wide enough to display the cell content. Simply increase the column width to show the cell content.

Modifying values with cell references

The true advantage of cell references is that they allow you to update data in your worksheet without having to rewrite formulas. In the example below, we’ve modified the value of cell B1 from $1,200 to $1,800. The formula in B3 will automatically recalculate and display the new value in cell B3.

Excel will not always tell you if your formula contains an error, so it’s up to you to check all of your formulas. 

To create a formula using the point-and-click method:

Rather than typing cell addresses manually, you can point and click on the cells you want to include in your formula. This method can save a lot of time and effort when creating formulas. In our example below, we’ll create a formula to calculate the cost of ordering several boxes of plastic silverware.
 

1. Select the cell that will contain the formula. In our example, we’ll select cell D3.

2. Type the equals sign (=).

3. Select the cell you want to reference first in the formula: cell B3 in our example. The cell address will appear in the formula, and a dashed blue line will appear around the referenced cell.

4. Type the mathematical operator you want to use. In our example, we’ll type the multiplication sign (*).
 
5. Select the cell you want to reference second in the formula: cell C3 in our example. The cell address will appear in the formula, and a dashed red line will appear around the referenced cell.

6. Press Enter on your keyboard. The formula will be calculated, and the value will be displayed in the cell.

Formulas can also be copied to adjacent cells with the fill handle, which can save a lot of time and effort if you need to perform the same calculation multiple times in a worksheet. 
 
 

To edit a formula:

Sometimes you may want to modify an existing formula. In the example below, we’ve entered an incorrect cell address in our formula, so we’ll need to correct it.
 

1. Select the cell containing the formula you want to edit. In our example, we’ll select cell B3.

2. Click the formula bar to edit the formula. You can also double
-click the cell to view and edit the formula directly within the cell.

3. A border will appear around any referenced cells. In our example, we’ll change the second part of the formula to reference cell B2 instead of cell C2.

4. When you’re done, press Enter on your keyboard or click the checkmark in the formula bar.
 
 
5. The formula will be updated, and the new value will be displayed in the cell.
 
 
If you change your mind, you can press the Esc key on your keyboard to avoid accidentally making changes to your formula.
 
To show all of the formulas in a spreadsheet, you can hold the Ctrl key and press ` (grave accent). The grave accent key is usually located in the top-left corner of the keyboard. You can press Ctrl+` again to switch back to the normal view.

Working With Excel In The Cloud- An Intro To Onedrive

 
 

EXCEL AND ONE DRIVE

 
 
You might have heard the phrase “working in the cloud” and wonder what that means, and how that affects the way you work with Excel. It’s really all about providing easy access to your data via a browser—to anyone, anywhere, at anytime. No need for other people to have Excel installed to view, edit, or collaborate on that data.
 
By using Excel Online, a browser-based extension of Excel, you will be able to do all that. You can even use it to create a new workbook on a computer that doesn’t have Excel installed. 
 
You can view and edit workbooks in a browser in one of two ways: 
 
· By uploading or creating new workbooks on OneDrive, which is a free Web-based file storage and sharing service. 
 
· By saving workbooks to a SharePoint site where Office Online is installed. 
 

What is Excel Online? 

Excel Online is a browser-based application that you can use to view and edit Excel workbooks in your browser. Workbooks and worksheets look the same in Excel Online as they do in Excel, and Excel Online has the familiar look and feel of Excel. Many Excel features are available in Excel Online, but not all features are supported or work exactly the same way. To make changes beyond what you can do in the browser, Excel Online includes a way for you to open the workbook in Excel, and then save it back to the server. 

Excel Online makes it easy for you to: 
 
· Extend Excel on the Web Use Excel features and functionality you are familiar with, in a Web environment. 
 
· Work anywhere A browser is all you need to access your workbooks. 
 
· Work together Your teammates can work with you on projects regardless of which version of Excel they have. 
Using OneDrive to access Excel Online 
 
To use Excel Online on OneDrive, you need a Microsoft account. Use that ID to sign in and then go to OneDrive to access Excel Online. To work on existing Excel workbooks in Excel Online, you can upload those workbooks in OneDrive. 
Create a OneDrive ID 
 
1. Go to http://OneDrive.live.com. 
 
2. Click Sign up. 
Upload an existing workbook 
 
To upload one or more workbooks for use in Excel Online, do the following: 
 
1. Sign into OneDrive. 
 
2. Under Files, click the folder where you want to upload the workbooks. 
 
Folders that display a lock icon are private folders; folders can also be shared folders or public (internet) folders. 
 
NOTE: By clicking Create and then Folder, you can create a new folder where you can upload your workbooks. 
 
3. Click Upload to add the workbooks you want to open in Excel Online. 
Start a new workbook in Excel Online 
 
Instead of uploading a workbook from your computer, you can add a new, blank workbook to a OneDrive folder directly in the browser. 
 
1. Sign in to OneDrive. 
 
2. Click the folder where you want to add a new workbook. 
 
3. Click Create, and then click Excel workbook. 
 
Excel Online opens in edit mode. 
 
NOTE: There’s no need to save your changes. Excel Online saves your workbook automatically while you work on it. If you make changes you don’t want to keep, use the Undo command (or press CTRL+Z). 
View worksheet data in Excel Online 
 
When you open a workbook in view mode in Excel Online, you can view and interact with the worksheet data to help you interpret it. For example, you can sort and filter the data in columns or expand PivotTables to see additional details and data trends. To make changes to the data, you must open the workbook in edit mode. 
 
1. Sign in to OneDrive. 
 
2. Click the folder that contains the workbook you want to view. 
 
3. Click the workbook that you want to explore. 
Edit worksheet data in Excel Online 
 
When you work on an Excel Online workbook in edit mode, you can change the data, enter or edit formulas, and apply basic formatting to draw attention to the data. 
 
1. Sign in to OneDrive. 
 
2. Click the folder that contains the workbook you want to edit. 
 
3. Click the workbook you want to change. 
 
Excel Online opens in edit mode. 
 
To make advanced editing changes to the worksheet that exceed the capabilities of Excel Online, do the following: 
 
1. Click Open in Excel. 
 
2. In Excel, make the editing changes you want. 
 
3. On the File tab, click Save. 
 
The workbook is automatically saved in the OneDrive folder. 
 
Use Excel Online to collaborate on worksheet data with other people 
 
To collaborate with other people on the same workbook at the same time, you can share a workbook with them. This feature is useful when you have a worksheet that requires input, such as a group project or a signup sheet. No more sending a workbook around via e-mail, or waiting for someone else to check it back in on the server. 
 
1. Sign in to OneDrive. 
 
2. Click the folder that contains the workbook you want to share. 
 
NOTE: Other people will be able to access everything in the folder you select because you cannot provide permission to individual workbooks only. To keep specific workbooks private, use one or more folders for private workbooks, and create a separate folder for the workbooks you want to share. 
 
3. Click Share. 
 
4. Invite the people you want to share the file with. 
 
5. Click Share. 
 
6. Back in the folder, click the workbook. 
 
After users click the link you sent them and start working on the worksheet in edit mode, you can see the changes that they make. 
 
NOTE: If changes are made to the same cell by different people (including you), the last change will displayed in the cell. 

Collaboration Becomes Easy

Many of the features in Office are geared toward saving and sharing files online. OneDrive is Microsoft’s online storage space you can use to save, edit, and share your documents and other files. You can access OneDrive from your computer, smartphone, or any of the devices you use.
Once you have a microsoft account, you will be able to sign in to Office. Just click Sign in in the upper right corner of the excel window.
 

Benefits of using OneDrive

Once you’re signed in to your Microsoft account, here are a few of the things you’ll be able to do with OneDrive:
 
· Access your files anywhere: When you save your files to OneDrive, you’ll be able to access them from any computer, tablet, or smartphone that has an Internet connection. You’ll also be able to create new documents from OneDrive.
 
· Back up your files: Saving files to OneDrive gives them an extra layer of protection. Even if something happens to your computer, OneDrive will keep your files safe and accessible.
 
· Share files: It’s easy to share your OneDrive files with friends and coworkers. You can choose whether they can edit or simply read files. This option is great for collaboration because multiple people can edit a document at the same time (this is also known as co-authoring).

Saving and Opening Files

When you’re signed in to your Microsoft account, OneDrive will appear as an option whenever you save or open a file. You still have the option of saving files to your computer. However, saving files to your OneDrive allows you to access them from any other computer, and it also allows you to share files with friends and coworkers.
 
For example, when you click Save As, you can select either OneDrive or This PC as the save location.

Why Excel?

Welcome To The World Of Analysis With Excel…

Microsoft Excel was released in 1985 and has grown to become arguably the most important computer program in workplaces around the world. Whether you are budgeting, organizing client sales lists, or need to plan an office social gathering, Excel is a powerful tool that has become entrenched in business processes worldwide. 

Why Excel?

Among the computer programs which exist, Microsoft Excel is one of the most important because of the key role it plays in many sectors. It is the most used spreadsheet program in many business activities, classwork and even personal data organisation. Since 1985, it has played a vital role in performing formula based arithmetic and calculations, and other activities that may require mathematical calculations. Many businesses, personal and institutional enterprises have embraced the use of Excel because of its utility and the ability to serve as a visual basic for different applications. 

Brief History of MS Excel 

 Some Areas of Excel Functionalities

The importance of MS Excel can be seen in the different departmental units it is used as follows:

 

Finance and Accounting 

 
If you walk through the finance or accounting department at any major corporate office, you will see computer screens filled with Excel spreadsheets outlining financial results, budgets, forecasts, and plans used to make big business decisions. 
This is the area of business with the biggest reliance and benefit from Excel spreadsheets. Advanced formulas in Excel can turn manual processes that took weeks to complete in the 1980s into something that takes only a few minutes today. 
 
Most users know that Excel can add, subtract, multiply, and divide, but it can do much more with advanced IF functions when coupled with VLOOKUP, INDEX-MATCH-MATCH, and pivot tables.

Marketing and Product Management

While marketing and product professionals look to their finance teams to do the heavy lifting for financial analysis, using spreadsheets to list customer and sales targets can help you manage your sales force and plan future marketing plans based on past results. 
 
Using a pivot table, users can quickly and easily summarize customer and sales data by category with a quick drag-and-drop. All parts of business can benefit from strong Excel knowledge, and marketing functions are not exempt.
 

Human Resources Planning 

 
While database systems like Oracle (ORCL), SAP (SAP), and Quickbooks (INTU) can be used to manage payroll and employee information, exporting that data into Excel allows users to discover trends, summarize expenses and hours by pay period, month, or year, and better understand how your workforce is spread out by function or pay level. 
 
HR professionals can use Excel to take a giant spreadsheet full of employee data and understand exactly where the costs are coming from and how to best plan and control them for the future. 

Education

 
A good use of Excel in schools and educational institutions can automate classroom activities, like games, quizzes and tests. More importantly, it can be used for computation and analysis of test scores, logs, attendance and so on.
 
For example, Excel based App like SimpleSchool Grading and Analysis App can compute, analyse and generate instant report sheets for students by just inputting the students scores.
 

Data Organization

Even non-financial businesses employ Excel spreadsheets. The ability to organize data in Excel tables and set up tools for updating, organizing, and displaying the data makes the program popular for administrative purposes.
 
Comprehensive, easy-to-reference Excel tables allow administrators to check a single statistic in a process, such as service fault issues in a product, against a report of trends for the same service faults. Through these comparisons, issues can be identified and solutions developed.

Programming

 
Excel supports Microsoft’s Visual Basic for Applications programming language for creating macros that increase efficiency and ease of use by automating or simplifying complex functions.
 
People who are proficient at creating Excel macros are much in demand at organizations of all types.

Graphing

 
Excel also produces a variety of charts and graphs for representing income statements and other statistical data visually. You save time by making the charts in the spreadsheet where the data resides.
 
Visual presentation of the data drives home your points more effectively than columns of numbers. Being able to graph within Excel also frees up some capital that otherwise would have to be invested in another graphing program.

You Can Do Anything With a Spreadsheet

 
Using Excel for business has almost no limits for applications. Here are some examples:
When planning a team outing to a baseball game, you can use Excel to track the RSVP list and costs.
Excel creates revenue growth models for new products based on new customer forecasts. 
 
When planning an editorial calendar for a website, you can list out dates and topics in a spreadsheet.
When creating a budget for a small product, you can list expense categories in a spreadsheet, update it monthly and create a chart to show how close the product is to budget across each category.
You can calculate customer discounts based on monthly purchase volume by product.
Users can summarize customer revenue by product to find areas where to build a stronger customer relationships.
 
This is a very short list to give you an idea of the diverse uses for Excel.

The Bottom Line

Excel is not going anywhere, and businesses will continue to use Excel as a primary tool for diverse functions and applications ranging from IT projects to company picnics.
 
A working knowledge of Excel is vital for most office based professionals today, and stronger Excel skills can open the door to promotion and leadership opportunities. Excel is a powerful tool but cannot function alone. It takes a savvy computer user to take advantage of everything Excel has to offer to provide the best results for their company.