Excel SUM Function

The SUM Function is an expedient way to add the values within a column or row or even non-adjacent cells. SUM is found within the Functions Tab under Math & Trig, scroll down the menu and select SUM. AutoSum is also located as its own button in the Functions Tab (highlighted).

Excel SUM Function

The third location for the SUM Function is in the Home Tab in the editing box. The fact that Excel has the SUM function in so many locations gives a hint as to how often this function is used.if you want to read cell locking in excel you can read it at https://sgrdimsr.org/lock-cells-excel/

Excel SUM Function

The SUM Function is written as =SUM(number1,number2…) the numbers can be manually entered (4), they can be a cell (A2), or they can be a range (A3:A6) or a combination of all three (4,A2,A3:A6).

SUM

Looking at the above, it show the four ways to easily use the SUM Function at the end of each row to total each expense

  1. Individually – In G2 enter the SUM Function and manually enter all numbers within Row 2, close the parentheses and hit enter.
  2. Cell by Cell – In G3 the SUM Function is entered and each numeric cell is selected in Row 3 separated by commas
  3. Range – G4 shows the Sum of a range of cells. Using a range of cells is best if additional columns will be added because Excel will automatically adjust the Range to reflect any new columns. Individually entering the data and using the cell by cell method will not include new columns in their formulas.
  4. AutoSum – When a range of data is selected as we did in Row 6, the Quick Sum box will appear. Select Totals and click Sum. The first sum within the Quick Sum box shows the
Continue Reading

Subscript/Superscript in Excel

Introduction

Adding subscripts to Excel can be quite a simple matter of a font setting, but you might come across limitations when adding subscripts to cells containing numbers or formulas. You can work surrounding this limitation by converting important input data to text before adding the subscripts. There is no subscript button for you really to quickly format text as a subscript in Excel as you can easily format a letter as a subscript in MS Word with the subscript button. And it may be a little hard to format text as a Subscript in Excel 2010.

The subscript is the same but with letters attached to the bottom. Like with chemical formulas: co2, H2, or when mentioning number bases (octal, decimal, etc.) in mathematics. In this short article, I’ll tell you to format text as subscripts or superscript in Microsoft Excel easily.

Formatting the text as Subscript goes the following:

  1. On a new spreadsheet, write the text as usual, with regular sized letters, and press Enter. In the following example, I used the writing as “co2”.

Subscript/Superscript in Excel

2. Now double click inside that cell, and carefully choose the characters to become superscript. In the example I have selected the “2” character in the input text co2. And Right click as part of your selection, and choose “Format cells…”

Subscript/Superscript in Excel

3. Further, you can see the Format Cells window. Check the “Subscript” box and click OK.

Subscript/Superscript in Excel4. Finally click outside the cell, and you’re done! The character “2” of the co2 appears to the bottom. Check out the screenshot below.

Subscript/Superscript in Excel

To Find out Subscript option in the Format Cells dialog box using short cut keys, go through the following easy steps:

At first, Select characters in a cell carefully to become subscript as in the last example to make. And then open …

Continue Reading

Mean in Excel

Mean in ExcelIn Excel, the Mean is referred to as the Average. To find the mean of a set of numbers you add all numbers within the group and divide the total by the count of those numbers. In the business sector, the mean is the most commonly used measure to find the average of a set of values. Take Sales for example, it’s not uncommon to use the average sales from a previous year to predict future years’ sales.  However, the mean is only as accurate as the data being measured.If there are outliers in either direction they will seriously distort the average pulling it more to the side with an outlier.

The formula or syntax to find the Mean with Excel is =AVERAGE(number1,number2…) you can manually enter the numbers into the formula or you may select a range of cells that have the numbers within them. The formula can also be found in the FORMULAS Tab within the Ribbon, select More Functions and AVERAGE is in the Statistical Menu.click here to read an informative post about sum function in excel.

In the following example, we are evaluating our top ten salespeople on their quarterly sales for the year.

Mean in Excel

Mean in ExcelFind the Average in a Row:

To find Amy A’s average sales for the year we could enter =AVERAGE(2500,3850,3500,5500) or =AVERAGE(B2:E2) both formulas would give us the same result of $3,837.50

Find the Average in a Column:

To calculate the Quarter 1 Sales (Column B) enter =AVERAGE(B2:B10) to get $4,200.00

Find the Average in a Range:

Calculating the year’s average sales across all salespeople enter =AVERAGE(B2:E10) to get $3,655.56

Mean in ExcelFind the Average in Non-Adjacent Cells:

To find the Average 1st Quarter Sales across the North District enter =AVERAGE(C2,C4,C8) to get $4,433.33

Find the Average of a Range and Cell:

Let’s say …

Continue Reading

IF Function in Excel

The most commonly used logical equation, the IF Function has two results based on a single logical test. It checks the logical test and if that is TRUE it yields one value but if it is FALSE it has a completely different value. The IF Function can be located in the FORMULAS Tab under Logical.  The syntax or formula for the IF Function is =IF(logical_test,value_if_true,value_if_false)

IF Function in Excel

The logical_test can be in many formats

  • Text (looking to see if the cell contains a word or series of text data)
  • Nested IF Functions
  • Logical Operators (Greater than, Less Than or Equal To)
  • OR Functions
  • Time and Date Functions

There are so many ways to use the IF Function and it is one of the most powerful Functions to use in Excel.

Tip: If one of the value_if elements are left blank [IF(logical_test,,value_if_false)] Excel will return a 0 as the value if the logical_test is TRUE, however, if you enter quotation marks Excel will leave the cell blank for all TRUE logical_tests.read more about cell locking in excel at https://sgrdimsr.org/lock-cells-excel/

IF Function in Excel

Text Logical_test

In a text logical_test, Excel searches for a specific word or other text and provides a value if the cell has that text and another if the cell doesn’t have that text. In the following example, we are selling a new software program. There are six orders submitted but more are coming in each day. Rather than fill out the entire worksheet we utilize the IF Function to auto-fill as orders are entered. The first set of data we want pre-filled is the price. The software program can be downloaded online for $150.00 or a desktop version can be purchased for $250.00.

  1. In cell B3, enter =IF(B2=”Online”,150,250), saying if the Item is downloaded online return a value of $150.00 if not
Continue Reading

How to Lock Cells in Excel

Microsoft Excel gives you an option to lock cells to guard them against any unwanted or unplanned editing. When you choose to protect a worksheet in Excel, all cells go locked by default, which means it can’t be edited by any one now.

In addition to this, if you need to lock only specific cells in a worksheet, you can do so using this amazing function. For this, you will have to unlock all the cells first, then lock only particular cells and ranges before protecting the worksheet.

Along with this, the function also gives you an option to enable specific users to edit your protected worksheet.

Let’s understand this function by the example given below.

Here are steps to protect cells in the Excel 2010

Step 1

To enable Protect Sheet, first go to Review Menu of your spread sheet, then click on Protect Sheet Command.

How to Lock Cells in Excel

As soon as we click on Protect Sheet Command, Excel pop ups a dialog box called Protect Sheet dialog box. In this box, you can choose various options that you want to be available when the worksheet protection is enabled. Even if you do not choose any of these options, Excel itself chooses the Protect Worksheet and Contents of Locked Cells check box by default.

Step 2

Choose any of the check boxes listed in the Allow All Users of This Worksheet To list box that you wish to be functional when the worksheet protection is operational. These options can be any of the options given in the list such as Format Cells or Insert Columns

The first two check boxes i.e. ‘Locked Cells’ and ‘ Unlocked Cells’ are by default selected. However, you can deselect either or both of the check boxes as per your requirement.

Step 3

Protect your sheet by …

Continue Reading