Week 2: Excel Continued.
And/Or /Not
If I say take a book AND a pen. You would take both objects.
If I say take a book OR a pen. You would choose which object you would take.
If I say take a book but NOT a pen. What would you do? Take the book only!
These conditional functions are used in excel too, when creating formulas.
Let's start with AND
1. Open the following file:
If I say take a book OR a pen. You would choose which object you would take.
If I say take a book but NOT a pen. What would you do? Take the book only!
These conditional functions are used in excel too, when creating formulas.
Let's start with AND
1. Open the following file:
andornot.xlsx |
2. In Column D - we want to display a TRUE if the Car is Ford and the Colour is Black.
Enter the following expression in cell D2:
=AND(A2="Ford", B2="Black")
Which cells appear as False? Why?
3. In the E Column - we want to display a TRUE if the Car is a Ford or Black
Try to see if you can work out the expression for this.
Your results should be:
Enter the following expression in cell D2:
=AND(A2="Ford", B2="Black")
Which cells appear as False? Why?
3. In the E Column - we want to display a TRUE if the Car is a Ford or Black
Try to see if you can work out the expression for this.
Your results should be:
Why?
4. In column F - we want to display any car that is not Black. Try to see if you can work out the expression for this.
Your results should be:
4. In column F - we want to display any car that is not Black. Try to see if you can work out the expression for this.
Your results should be:
Why?
Exercise 5: [Needs to be submitted]
Open the following Excel file:
ex5.xlsx |
Each week you have a budget of $100.
1. For each Month, using an AND function find out if you stayed under Budget for each week.
Your results should look like this in the Budget Column:
1. For each Month, using an AND function find out if you stayed under Budget for each week.
Your results should look like this in the Budget Column:
Tip: Use the <= symbol to work this out.
2. In the Budget2 Column, using both and IF statement and an AND statement, display the words "Over Budget" or "Under Budget"
3. In the Budget3 Column, using an IF statement and a NOT statement, display "Over Budget" if the total value is over $400 and display "Under Budget" if the total value is not over $400.
2. In the Budget2 Column, using both and IF statement and an AND statement, display the words "Over Budget" or "Under Budget"
3. In the Budget3 Column, using an IF statement and a NOT statement, display "Over Budget" if the total value is over $400 and display "Under Budget" if the total value is not over $400.
Save your files and Ex5YourName.xlsx and submit.