Formulas with Floor.math and Ceiling.math Function
If you are a sawmill and yoiu have a peice of lumber that is 105 feet long.
You require 10ft lengths. In this calculation you would use the Floor.math function
The result would be 10
If you were a carpenter and required would to cover 105 feet
You would use ceiling.math
The result would be 11
You are only able to purchase lumber in 10 foot lengths so you require 11 pieces.
How to Use Vlookup and Iferror instead of complicated if function
- We have a month of days and we would like to indicate that certain days are significant.
- We will install a symbol next to the signicant day
- Create a table that contains the days that we would like to mark
- Name the list of days as "day"
- The first attempt is to use If vlookup
=IF(VLOOKUP(C2, day, 1,FALSE),"##")
- c2 - being the date, day - being the range of data, 1 - indicating the first column, FALSE - exact match
- The formula works correctly but gives you error message
- To correct this we will use the function iferror
=IFERROR(IF(VLOOKUP(C2, day, 1,FALSE),"##"), "")
Pivot tables - A simple explanation
- Pivot table allow you to analyze large lists of data
- Select the data that you would like to analyze
- Click the Insert tab - Pivot table
- Select a separate sheet from the dialog box
- The new sheet will be displayed
- click the box next to fields that you would like to analyze
- The resulting table will be displayed
- You are able to select and unselect to obtain the table you desire
|The indirect Function|
|The Aggregate Function|
|Matching one column or the Other Index/match|