The IF Formula and other Logical operators

Any of the logical functions found in the FORMULA Tab can also be used in conjunction with the IF Function. For our Company, there were two discount codes for \$25.00 off in two different advertisements. To show the Coupon Discount use the OR Function within the IF Statement because we are checking for Coupon Code “SAVE25” or “25OFF”.

1. In Cell B7 enter =IF(OR(B6=”SAVE25″,B6=”25OFF”),”-\$25.00″,0)
2. The logical_test is saying if B6 equals SAVE25 OR 25OFF, then return a value of -\$25.00 but if not then enter 0.
3. Copy the formula across Row 7.

Note: When nesting logical functions inside each other it’s always best to work backwards. First, enter the OR Function and make sure that it is working, nest that within the IF statement afterwards.

Date and Time

The Date and Time Functions can be nested within IF functions just like the logical functions. In this example, the company has set a goal to send out the Desktop version within two days of the customer placing the order.

1. Start with the DAYS function to count how many days are between the Ship Date and the Order Date. Enter =DAYS(B10,B9) into B11 and you should get a value of 0 because the Online version is downloaded instantly.
2. Nest the DAYS Function into an IF Function by entering =IF(DAYS(B10,B9)<=2, “Pass”,”Fail) this function is saying if the number of days between the ship date and the order date are less than two, return a value of Pass, if not return a value of Fail.
3. For our example, only the Desktop version will actually be shipped out, the IF function is used to first see if the Item is Online or Desktop. Just after the equal sign on the DAYS function enter IF(B2=”Desktop), then copy and paste the IF Function from Step 2 as the value_if_true, “N/A” and close out all parentheses. Now if the Item is Desktop it will perform the DAYS Function to yield a Pass or Fail, however, if the Item is Online it will yield an N/A.
4. =IF(B2=”Desktop”,IF(DAYS(B10,B9)<=2,”Pass”,”Fail”),”N/A”)
5. Copy the formula across Row 11 and all blank Items will also

Excel lookup function

Excel has three functions to look up information within the worksheet or other worksheets, LOOKUP, VLOOKUP and HLOOKUP.  All three can be found in the FORMULA Tab in the Ribbon under Lookup & Reference as highlighted.

The LOOKUP Function should be used when you are searching a single row or column for one piece of data in order to retrieve another piece of information within the same position. For example, you know the stock code but want to know what the price of the stock is. You’d use LOOKUP to locate the stock code and retrieve the price.

The VLOOKUP or Vertical Lookup should be used when you need to find data within the worksheet by row. Similar to LOOKUP you would use this to search by stock code to find the price by searching across the row. HLOOKUP or Horizontal Lookup is used when you want to search down columns for information, it starts by searching the first row for the value and then moves down the column to find the second value. Let’s look at the three lookup functions to see which is best during certain circumstances.read more information about logical operators by clicking here

LOOKUP Function

When LOOKUP is selected from the Lookup & Reference Button a dialog box will appear asking which argument you will be using Vector or Array.

Vector is recommended when using the LOOKUP Function. If you would like to use Array it is strongly recommended you use the VLOOKUP or HLOOKUP Functions instead. The Vector form searches a specific vector (either one row or one column range) for a value and then returns a value from that same position in a different vector.  The formula for the function is

=LOOKUP(lookup_value,lookup_vector,return_vector)

Searching down Columns

Example 1: Find the Code for “Property”

Lookup Value = Property

Lookup Vector = Column A

Return Vector = Column B

=LOOKUP(“Property”,A2:A20,B2:B20)

Example 2: What type of Account is Code 600

Lookup Value = 600

Lookup Vector = Column B

Return Vector = Column C

=LOOKUP(600,B2:B20,C2:C20)

Example 1: Who didn’t have any sales during Quarter 1?

Lookup Value = …

How to Subtract in Excel 2013

Excel has numerous formulas that allow users to perform different calculations and save their time. Subtract is one of the major and most used formulas in Excel, which helps you to subtract numbers in a range and cell.

In our today’s tutorial, we will introduce to you a brief overview of the Excel subtraction, and make you learn how to use this formula in Microsoft Excel 2013.

So let’s understand this amazing formula with some simple examples.

Like, previous versions of Microsoft Office, Excel 2013 does not support a SUBTRACT function as you might expect. However, in Office 2013, you get two options for subtracting cells from one another. Either you can use a mathematical equation or SUM function to apply subtraction formula. Whatever you feel you are more comfortable with.

1st Method – Subtraction Equation

Whenever we think to use the simplest way to subtract in Excel, equation is the first method that comes to mind.

Step 1

First, write two values in two different cells. Now type the equal sign (=) in the empty cell where you want to display the result. Now click on the cell containing a value from which you want to subtract. As soon as you click on the cell, its location will be displayed in your equation. See the snapshot given below.

Step 2

Next step is to add minus sign (-) to your equation. Now click on the cell containing the value that you want to subtract. Excel will automatically add its location to the equation as shown in the image given below.

Step 3

Now press Enter key, and get your result. See the following snapshot.

2nd Method- Sum Function

Along with adding numbers, Sum Function can also be used for subtracting numbers. The function works with both ranges and individuals cell. Thus using sum function you can deal with many cells at once.

Step 1

Similar to equation method, you need to type the equal sign (=) in a blank cell. Then, click the cell you are looking to subtract from.

Step 2

Now type a minus sign (-), …

Freeze Panes

When you are working with large spreadsheets in Excel then it is very useful to have the headings at the top of the columns and the left of the rows to remain in place so you can see what data you are looking at or entering. Freezing panes allows you to keep this information in view at all times and there are several options that you have when using this feature.

Freezing The Top Row And First Column

1. Click on the View tab which can be found in the ribbon at the top of the screen.

2. In the center of the ribbon, there is an option for Freeze Panes. This will open a drop down menu where you will find an option for Freeze Top Row and Freeze First Column which you should click on. You can click on just one of these options or both of them, depending on what you need for your spreadsheet.read more about formulas in excel at http://dair2.com/formula-logical-operators

3. Once you have done this then a thick black line should appear below the top row or the first column. You can check if it has worked by scrolling down or across and checking whether the top row or the left hand column stays in place.

Freezing User Defined Columns And Rows

You can choose which columns and rows you want to freeze other than the one at the top and on the left. To do this you need to choose an active cell. You are then able to freeze all columns to the left of the cell and all rows above it.

1. Click on the cell that you want to be the active cell. Remember all everything above and to the left of this cell will be frozen.

2. Click on the View tab of the ribbon and select the option for Freeze Panes which will open a drop down menu. From here you will be able to select both columns and rows.

3. A thick black line will appear on the row above and on the column to the left of …

Date Format in Excel

Excel has a total of sixteen date formatting styles all of which can be found by right clicking the cell that the date is in and selecting Format Cells from the menu and clicking Date in the Categories. A short date and long date shortcut are also located in the Home Tab, in the Number Formatting Box they can be selected from the drop-down menu.

Also, by selecting the bottom right hand corner of the Number Formatting Box (highlighted) the Format

Cells dialog box will appear.

A custom date format allows users to display a date anyway they want.

16 Styles of Date Formatting

1. Day of the week, Month, Days, Year
• Monday, January 1, 2018
2. Numerical Month / Days
• 1/1
3. Numerical Month / Days / Two-digit Year
• 1/1/18
4. Two-Digit Numerical Month / Two-digit Days / Two-digit Year
• 01/01/18
5. Days Abbreviated Month
• 1-Jan
6. Days Abbreviated Month Two-digit Year
• 1-Jan-18
7. Two-digit Days Abbreviated Month Two-digit Year
• 01-Jan-18
8. Abbreviated Month Two-digit Year
• Jan-18
9. Month Two-digit Year
• January-18
10. Month Days , Year
• January 1, 2018
11. Numerical Month / Days / Two-digit Year Time
• 1/1/18 12:00 AM
12. Numerical Month / Days / Two-digit Year Military Time
• 1/1/18 0:00
13. Initial Month
• J
14. Initial Month Two-digit Year
• J-18
15. Numerical Month / Days / Year
• 1/1/2018
16. Days Abbreviated Month Year
• 1-Jan-2018

Default Formatting

To change the default date format, open the computer’s Control Panel. Under Clock, Language and Region, select change date, time and number formats. In this window, you can select the formatting for Short Date, Long Date, Short Time, Long Time and change the First Day of the Week. At the bottom, you can view the Examples of what will be displayed based on your formatting selections.

Custom Formatting

You can create custom date formats within Excel by first selecting the cell you want to format and opening the format cells window.

Select Date from the Category Menu and chose the date format that most closely resembles the format you are creating.

Now select Custom from the Category Menu and you will be …