Formula Friday - IF

One of the formulas I tend to use quite often in Excel is the "IF" formula.  The "IF" formula can be very helpful when trying to sort out your data.  The "IF" formula is very flexible as it lets you put in an argument to test and lets you determine what result is shown if the argument is true or false.  In this post I am going to give an example of a simple "IF" formula.  In the future I will give an example of using an "IF" formula, combined with other logical formula(s) to show what great things can be done with "IF"!

To use this formula:

-Click on the cell where you want to see the logical test results

-Enter =IF([logical test],[true value to display],[false value to display])

-Note: If you want to use a value as your true or false result, you will need to put "" around the value, i.e. "blue"

-Press Enter

-Now you will see the result

Example:

-In this example I have a list of individuals who have donated money to a charity.  I want to break the list down into 2 groups based on the dollar amount given.  Those individuals who gave more $100 will be in the Elite group.  The individuals who gave $100 or less will be in the Standard group.

 

-Click on Cell G2

-In cell G2, enter =IF(F2>100,"Elite","Standard")

 

1. In this case I am looking at the value in column F to determine which group the individual belongs in, so I use column F in my logical test.  I am using F2>100, because anything where the value in column F is greater than 100, I want column G to show Elite.

2. If my statement is true (the value in F is greater than 100), then the cell in column G will return "Elite"

3. If my statement is false (the value in F is less than or equal to 100), then the cell in column G will return "Standard"

-Press Enter

-Now I see the result of "Elite" in cell G2 since the Contribution amount in cell F2 was greater than 100

 

-To finish this example, I want to copy the formula to the remainder of the contributors to figure out the groupings.  Therefore, I need to copy this formula down to cells G3 through G10.

-Copy cell G2

-Highlight cells G3 through G10

-Paste the formula

-Now each of these cells will have the same formula and will display the result in each cell

-Cells with formulas

 

-Results

 

Excel ya later!

Formula Friday - LEN (cell character length)

Do you need to know how many characters are in a certain cell in Excel?  Well, Excel has an easy formula you can use to count the characters or see the length.  The formula is LEN.  An example of when this can come in really handy is when you are filling in a form or uploading data to a form that has specific character limits.

To use this formula:

-Click on the cell where you want to see the character count (length) results

-Enter LEN([cell you want evaluated])

-Note: Spaces will also be counted as a character with this formula

-Press Enter

-Now you will see the number of characters in the result cell

Example:

-In this example I want to see how many characters are in each of the cells in the Address column.  The form that I am uploaded my data to has a character limit of 20 for the address field, so I want to make sure that my cells do not exceed this count.

 

-Click on cell G2

 

-Enter =LEN(C2)

-Press Enter

-Now you will see the number of characters in cell G2 (12 characters)

-From here I am going to copy this formula down to cells G3 through G10.

-Copy cell G2

-Highlight cells G3 through G10

-Paste the formula

-Now each of these cells will have the same formula and will display the characters in each cell

-Cells G6 and G10 have more than 20 characters, so now I know I need to make some changes to those cells

Excel ya later!

 

Formula Friday - COUNTA (Count non-blank cells)

An easy way to count cells that have data in Excel is by using the 'COUNTA' formula.  This formula will count cells that are not blank, whether they are numeric or anything else.  This is different from the COUNT formula that only counts cells if they have a number in them.

To use this formula:

-Click on the cell where you want to see the count results

-Enter COUNTA(Number1:Number2)

-The range of cells to be evaluated can be either consecutive or non-consecutive

-Like in other formulas, to evaluate consecutive cells, the cell range will use a ':' between cells

-i.e. COUNTA(B1:B15)

-To evaluate non-consecutive cells, the cells will be separated by a ','

-i.e. COUNTA(B1,B5,B8)

-Or, a combination can also be used

-i.e. COUNTA(B1:B5,B9)

-Press Enter

-Now you will see the number of non-blank cells in the result cell

Example:

-In this scenario, I want to count the number of months where the fruit or vegetable was tracked (has an "X" in the cell).

-I want the results to be displayed in row 17

-For this example, I will just focus on counting the Bananas to start with

-In cell B17, enter =COUNTA(B5:B16)

-Press Enter

-You will now see the result of 4 as there are 4 months that have an 'X" in the cell (January, June, August & November)

-Now I can copy this formula across to cells C17 through M17 to get the results for the other Fruits/Vegetables

-Results

 

Excel ya later!

Formula Friday - Today and Now (Auto Populate the Date!)

Excel has built in two easy formulas that will automatically display the current date or current date and time in your spreadsheet.  Each of these formulas will recalculate each time your spreadsheet is either reopened or refreshed. 

1. TODAY

-This first option is to use the TODAY formula.  This formula will display the current date.

-To use this formula, enter 'TODAY()' in the cell where you want the date displayed

-Press Enter

-You will now see the date

-Example 1:

-In this example, I want to have the date auto populate in cell A2 each time the report is opened

-In cell A2 enter 'TODAY()'

-Press Enter

-The date will now appear in cell A2

 

2. NOW

-The second option is to use the NOW formula.  This formula will display the current date and time.

-To use this formula, enter 'NOW()' in the cell where you want the date and time displayed

-Press Enter

-You will now see the date and time

-Example 2:

-In this example, I want to have the date and time auto populate in cell A2 each time the report is opened

-In cell A2 enter 'NOW()' and press Enter

-The date and time will now appear in cell A2

 

Reminder: Each of these formulas will recalculate each time the spreadsheet is reopened or the sheet is recalculated.

Excel ya later!

Formula Friday - Average

Similar to the Sum formula, using the Average formula in Excel is fairly quick and easy.  Just like the sum formula, the average can be used to average both consecutive and non-consecutive numbers.

To use the formula for consecutive numbers:

-Type '=average(Number1:Number2)'

-Press Enter

-Example 1:

-In this example I want to average the total units for Sunday through Saturday in cell B9

-In cell B9 type '=average(B2:B8)'

-Press Enter

 

-Now you will see the result of 5

 

-To see the formula, click back into cell B9 and you will see the formula in the Formula Bar

 

To use the formula for non-consecutive numbers:

-Type '=average(Number1,Number2)'

-Press Enter

-Example 2:

-In this example I want to average the total units for Sunday and Saturday in cell B9

-In cell B9 type '=average(B2,B8)'

-Press Enter

 

-Now you will see the result of 5

 

-To see the formula, click back into cell B9 and you will see the formula in the Formula Bar

 

To use the formula for consecutive and non-consecutive numbers:

-Example 3:

-In this example I want to average the units for Friday, Saturday and Sunday in cell B9

-In cell B9 type '=average(B2,B7:B8)'

-Press Enter

 

NOTE: The difference between the consecutive and non-consecutive numbers is the difference of using either ' : ' or ' , '.  For consecutive numbers, use the ' : ' between the first and last number. For non-consecutive numbers, use a ' , ' between the numbers.

-Now you will see the result of 4

 

-To see the formula, click back into cell B9 and you will see the formula in the Formula Bar

 

As I explained with the Sum formula, there are multiple ways to insert the average formula into the spreadsheet.

1. Type the formula in as described above

2. Use the "Insert Icon" function and type in 'Average' (Follow the same steps as in the Sum formula post)

 

 

3. On the Home tab, in the Editing section, click the AutoSum function drop down menu

-Click Average

4. On the Formulas tab, in the Function Library Section, click on the AutoSum function drop down menu

-Click Average

 

The 3rd and 4th options work best for summing consecutive cells. That being said, you can make adjustments to the formula to sum non-consecutive cells or a combination of both.

Excel ya later!

Formula Friday - Random Number Generator

Do you need to quickly produce a random number?  Excel has two simple formulas that will quickly generate a random number. 

1. RAND
• The first option is to use the RAND formula.  This formula will generate a number greater than or equal to 0 and less than 1. 
• To use this formula, enter '=RAND()' in the cell where you want the random number

• Press Enter
• You will now see a random number

 

• Note: Each time the spreadsheet is recalculated, the number will change

2. RANDBETWEEN
• The second option is to use the RANDBETWEEN formula.  With this formula, a random number will be generated between 2 numbers that you identify.
• Note: The result from this formula will be an integer
• To use this formula, enter 'RANDBETWEEN(Number1,Number2)
• Number1 is the lowest number
• Number2 is the highest number
• Press Enter
• You will now see a random number
• Note: Each time the spreadsheet is recalculated, the number will change
• Example 1
• I am looking for a number between 1 and 20
• In cell A1, enter RANDBETWEEN(1,20)

• Press Enter
• An integer between 1 and 20 is generated

 

 

Once you have the formula in place, if you want to regenerate the random number, you can press F9 or click "Calculate Now" on the Formula tab in the Calculation section.

 

 

As I explained with the Sum formula, there are multiple ways to insert the formulas into the spreadsheet.

1. Type the formula in as described above
2. Use the "Insert Icon" function and type in either RAND or RANDBETWEEN

 

3. On the Formulas tab, click on the Math & Trig drop down in the Function Library section and scroll down to find either RAND or RANDBETWEEN

 

Example 2

• In this example I want a dollar amount between 1 and 10.  In this example, I am going to use a combination of the two formulas above as I want a number with decimals.  If I only wanted an integer, then I would just use the RANDBETWEEN formula.
• Insert  =RANDBETWEEN(1,10)+RAND() in the cell

• Press Enter
• Now Excel will generate a number between 1 and 10 and a number between 0 and 1 and add them together

 

Excel ya later!

Formula Friday - Sum

Using the sum function in Excel is a convenient and quick way to add small or large data sets.  The function can be used to sum consecutive numbers or non-consecutive numbers.  There are at least 4 ways to insert the formula into your spreadsheet:

1. Type the formula into the cell that you want to show the summed result in:

• For consecutive numbers
• =sum(Number1:Number2)
• Example 1:
• In this example I want to sum the total units of Sunday through Saturday in cell B9

• In order to do this, in cell B9 type '=SUM(B2:B8)' and press Enter
• Once I hit Enter I get the result of 38
• 
  To see the formula, click back into cell B9 and you will see the formula in the Formula Bar
• 
• For non-consecutive numbers
• =sum(Number1,Number2)
• Example 2
• In this example I want to sum only the units for Sunday and Saturday in cell B9
• 
  In order to do this, in cell B9 type '=SUM(B2,B8)' and press Enter
• Once I hit Enter I get the result of 10
• 
  To see the formula, click back into cell B9 and you will see the formula in the Formula Bar
• 
•  For a combination of consecutive and non-consecutive numbers
• Example 3
• In this example I want to sum only the units for Friday, Saturday and Sunday in cell B9
• 
In order to do this, in cell B9 type '=SUM(B2,B7:B8)' and press Enter
NOTE: The difference between the consecutive and non-consecutive numbers is the difference of using either ' : ' or ' , '.  For consecutive numbers, use the ' : ' between the first and last number. For non-consecutive numbers, use a ' , ' between the numbers.
Once I hit Enter I get the result of 13

To see the formula, click back into cell B9 and you will see the formula in the Formula Bar
 

2. The second option is to click on the "Insert Function" icon next to the Formula Bar

• 
  Once the icon is clicked, the Insert Function window will pop-up
• 
1. Type in the word "sum" in the "Search for a function:" box
2. Click the "Go" button
3. Select "Sum" in the "Select a function:" section
4. Click the "OK" button
• 
•  Next, the Function Arguments window will pop up
• There are 2 options of how to enter the cell data into this window
1. Type in the cell data
• Below is an screenshot of what Example 3 from above would look like
2. 
   

   

   Use the cell selector icon to choose the cells you want to sum
• 
  Click the icon
• The window will minimize
• Click on the cell or consecutive cells you want to sum
• Click on the icon on the right once you have finished
• 
  Repeat these steps for any additional non-consecutive cell(s) in Number2, Number3, etc.
• Click OK

The 3rd and 4th options work best for summing consecutive cells. That being said, you can make adjustments to the formula to sum non-consecutive cells or a combination of both.

 

3. The 3rd option is to use the AutoSum function on the Home tab

• Click on the cell that you want to display the summed result (which is usually best if it is adjacent to the data you want summed)
• Click on the Home tab
• Click on the AutoSum function in the Editing section
• Excel will automatically add in the Sum formula and sum the adjacent cells
• In this example, I want the summed total in cell C9
• While in C9, I clicked on the AutoSum function on the Home Menu
• 
  Excel automatically entered the sum formula in C9 and assumed I wanted to sum the adjacent cells
• If I am ok with the formula, I click Enter
• If I want to make changes to the formula, I click into the cell to make changes, then press Enter

4. Finally, the 4th option I have is to use the AutoSum function on the Formulas tab in the Function Library

•  Follow the same steps as you would in Option 3

 

Excel ya later!