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”.
- In Cell B7 enter =IF(OR(B6=”SAVE25″,B6=”25OFF”),”-$25.00″,0)
- The logical_test is saying if B6 equals SAVE25 OR 25OFF, then return a value of -$25.00 but if not then enter 0.
- 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.
- 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.
- 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.
- 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.
- Copy the formula across Row 11 and all blank Items will also
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
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
Searching down Columns
Example 1: Find the Code for “Property”
Lookup Value = Property
Lookup Vector = Column A
Return Vector = Column B
Example 2: What type of Account is Code 600
Lookup Value = 600
Lookup Vector = Column B
Return Vector = Column C
Example 1: Who didn’t have any sales during Quarter 1?
Lookup Value = …
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 …
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
- Day of the week, Month, Days, Year
- Numerical Month / Days
- Numerical Month / Days / Two-digit Year
- Two-Digit Numerical Month / Two-digit Days / Two-digit Year
- Days – Abbreviated Month
- Days – Abbreviated Month – Two-digit Year
- Two-digit Days – Abbreviated Month – Two-digit Year
- Abbreviated Month – Two-digit Year
- Month – Two-digit Year
- Month Days , Year
- Numerical Month / Days / Two-digit Year Time
- Numerical Month / Days / Two-digit Year Military Time
- Initial Month
- Initial Month – Two-digit Year
- Numerical Month / Days / Year
- Days – Abbreviated Month – Year
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.
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 …