During this lecture, we will discuss how the “IF” function in Excel works, as well as conditional statements in Python. We will explore these concepts using an example from the game Transport Tycoon, also known as OpenTTD.
Lecture recording:
Bus or Train?
Imagine we have two growing cities in OpenTTD that we want to connect to facilitate passenger transport. Our goal is to decide which means of transportation is most suitable depending on the number of passengers traveling between these two cities. First, we create a bus connection between the cities. In our game, each bus can carry a maximum of 35 passengers.
But what if demand grows? Buses will no longer be sufficient. Therefore, we build a railway connection. Our train has 3 carriages, each capable of carrying 40 passengers, for a total of 120 passengers per trip. This setup is ideal for a medium number of passengers.
Excel: IF function
We will translate this scenario into Excel to decide which means of transportation to use based on the number of passengers. Suppose we have cell A2 where we enter the number of passengers. We want Excel to select “Bus” if the number of passengers is less than or equal to 35, and “Train” otherwise.
The formula we will use is:
=IF(A2<=35, "Bus", "Train")
The formula is entered into a cell, and the result is displayed depending on different input values. This simple IF function checks the condition A2<=35. If it is met, it returns "Bus"; otherwise, it returns "Train".
Python: if-else Statement
We will achieve the same logic using Python. Here is the code:
passengers = int(input("Enter the number of passengers: ")) if passengers <= 35: print("Choose Bus for travel.") else: print("Choose Train for travel.")
This script asks for the number of passengers, then uses an if-else statement to decide on the means of transportation.
The source code is also available on GitHub.
Bus, Train, or Plane?
As our cities grow, the demand may exceed the capacity of trains. Therefore, we introduce an airline connection. Planes in our game can carry up to 300 passengers.
Excel: nested IF function
By adding the plane option, we need to update our Excel formula to account for this third option.
The extended formula is:
=IF(A2<=35, "Bus", IF(A2<=120, "Train", "Plane"))
Here is how it works:
- If the value in cell A2 is less than or equal to 35, it returns "Bus".
- Otherwise, it checks if the value in cell A2 is less than or equal to 120. If so, it returns "Train".
- If neither condition is met, it defaults to "Plane".
Python: if-elif-else Statement
Now let’s update our Python script to include the plane option.
Here is the updated code:
passengers = int(input("Enter the number of passengers: ")) if passengers <= 35: print("Choose Bus for travel.") elif passengers <= 120: print("Choose Train for travel.") else: print("Choose Plane for travel.")
This script now uses an elif (else if) statement to check multiple conditions:
- If the number of passengers is less than or equal to 35, choose "Bus".
- Otherwise, if the number of passengers is less than or equal to 120, choose "Train".
- Otherwise, choose "Plane".
The source code is also available on GitHub.
Summary
We have seen how to use the IF function in Excel and conditional statements in Python to make decisions based on data—in our case, the number of passengers. Try experimenting with these concepts in your own projects or even in games like Transport Tycoon.
This lecture is also available in other languages.