View a Video explaining the Array

Working with Arrays in Excel

Arrays group a number of cells and treat them as one value.

CTRL-Shift- Enter

These combination of keys when used on a formula will install curly brackets on the formula to indicate that the cells in this formula are an array
In the example we have the parking cost for the first three weeks of a month.
The customer has offered to pay for the fees for Monday only.

{=Sum(if(B2:B22="Mon", C2:C22, 0))}

  • Type in the formula without the curly brackets
  • Prior to pressing enter, press CTRL-Shift- Enter
  • Curly brackets will be entered around the formula and the values will be treated as an array

If statements and Nested If statements

View Youtube video on if statements and nested if statements

  • The basic statement
  • =IF(D5=5, "good", "oops")
  • Explanation: If Cell D5 = 5 then enter good; if it is not enter oops

    What can be done with the if statement

  • =IF(D5=5, IF(F5=6, "still good", "oops"))
  • Explanation:If D5 = 5, and if it passes that test; then it checks to see if F5 =6;
  • If it pass both of those tests then it enters "still good"
  • if either one of the tests fail "oops" is entered
  • Entering the the second If immediately after the first test makes it equivalent to an "and" function.
  • Both D5 must equal 5 and F5 must equal 6 for you to receive a "still good"

    Changing the location of the second If statement

  • =IF(D5=5, "good", if(F5=6, "Still good", "oops"))
  • Explanation:If D5 passes the test then you will receive a "good"
  • If not; the F5 test is run and if it passes you will receive a "Still good"
  • You will receive a "oops" if the second test fails as well
  • Placing the second IF in this location makes it equivalent to an "or" function.

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