Formulas with Floor.math and Ceiling.math Function

View Floor.math and Ceiling.math

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

Use Iferror with Vlookup

  • 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

View instructions how to create a pivot table.

  • 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

Related Videos

Goal Seek
Iferror function
The indirect Function
The Aggregate Function
Matching one column or the Other Index/match

Sign up for our newsletter and receive a free calendar pop-up for entering dates

* indicates required