The power of spreadsheets cannot be overstated. Even with the growing number of new business tools and apps available, Microsoft Excel sheets remain an indispensable tool for day-to-day operations. The use of Excel is widespread in the workplace and will continue to be, at least for the near future. This post provides some quick Excel tips and basic formulas and functions to help you save time.
Keyboard Shortcuts
| Ctrl + Down/Up Arrow | Moves to the top or bottom cell of the current column |
| Ctrl + Left/Right Arrow | Moves to the cell furthest left or right in the current row |
| Ctrl + Shift + Down/Up Arrow | Selects all the cells above or below the current cell |
| Shift + F11 | Creates a new blank worksheet within your workbook |
| F2 | Opens the cell for editing in the formula bar |
| Ctrl + Home | Navigates to cell A1 |
| Ctrl + End | Navigates to the last cell that contains data |
| Alt + = | Sums the cells above the current cell |
| Ctrl + Shift + $ | Formats numbers within highlighted range into currency |
| Ctrl + Shift + % | Formats numbers within highlighted range into percentage |
| Ctrl + Shift + ; | Inserts current time |
| Ctrl + ; | Inserts current date |
The Blind Date Conundrum: In Excel, dates are stored as numbers starting from “0” onwards. The “0” date was arbitrarily chosen as January 0, 1900. Every integer value added is a full day, while the decimal portion of the number refers to the time.
Important Symbols to Know
The Dollar Sign ($): A simple but commonly forgotten tool is the dollar sign. When used within your formula, the dollar sign will make sure the row and/or column value will not change if you copy the formula.
=$A1 will keep A static.
=A$1 will keep 1 static.
=$A$1 will keep both A and 1 static.
The Ampersand Sign (&): The Ampersand sign is the quickest way to concatenate strings (join two or more strings together into one) within Excel. It is the simpler alternative to the concatenate function.
Basic Functions and Formulas: The Building Blocks
IF Function
The IF function is one of the most fundamental and widely used building blocks for Excel. It is a logical formula that looks at a value in a sheet and will provide one of two results, depending on whether the condition is met or not. For example, the function could produce a “YES” or “NO” result or a “TRUE” or “FALSE.”
Formula for an IF statement:
=IF(“condition”, “action if true”, “action if false”)
AND, OR, NOT, ISERROR Functions
AND, OR, and NOT are a set of functions that are often used when more complex rules are required. For example, they are often used in the condition section of an IF statement.
- AND: Returns “TRUE” if all conditions within it are met.
- OR: Returns “TRUE” if at least one condition is met.
- NOT: Returns “TRUE” if the logical statement within is false.
- ISERROR: Used as a failsafe when the logical rules could return an error message. If an error is seen, then the formula will return “TRUE.”
LEFT, RIGHT, LEN
LEFT, RIGHT and LEN are three basic string manipulation formulas. If you have worked with MyGeotab fleet management reports, you have used or seen them used when working with the manipulation of groups.
- LEFT: Returns the X leftmost character from a string.
- RIGHT: Returns the X rightmost character from a string.
- LEN: Returns the length of a string.
Formula for LEFT, RIGHT, LEN:
=LEFT(“STRING”,X) =RIGHT(“STRING”,X) =LEN(“STRING”)
For example, if we used the formula =RIGHT(“Geotab”,3) we will obtain the word “tab.”
How to Use the Basic Functions Within MyGeotab Reports
Let’s take a look at one of the default dashboard reports within MyGeotab called the Basic Driver Score:
Column K, which is labelled Idling % of Driving Time, uses a simple IF statement: =IF(E16>0,L16/E16,0).
This formula checks if the device had trips above 5 minutes. If this condition is met, then it divides the idling duration by the trip duration to find the percentage of time the driver spent idling the vehicle.
Basic Excel Math Functions
In this section, learn about some of the basic mathematical functions available in Excel and how they directly apply to the MyGeotab environment.
Min, Max and Everything in Between
These functions are the most common statistical function is excel. They are used to find outliers, averages, and other values.
MAX: Returns the largest value in a dataset (cell range).
=MAX($A$1:$A$10)
MIN: Returns the smallest value in a data set (cell range).
=MIN($A$1:$A$10)
MEDIAN: Returns the value that is right in the middle of the dataset. For example, from a list of numbers, like 1,5,8, the MEDIAN function would return the value 5.
=MEDIAN($A$1:$A$10)
MODE: Returns the most common value within a dataset.
=MODE.SNGL($A$1:$A$10)
Almost Max and Almost Min
Sometimes you don’t want the MAX or the MIN. Thankfully there’s a formula for that! LARGE(RANGE,K) and SMALL(RANGE,K) will give you the Kth largest or smallest value within a dataset (cell range).
How to Apply Formulas in MyGeotab
To quickly see the most common firmware version, Geotab GO device type, or vehicle make:
Use the =MODE.SNGL() formula within the Watchdog report.
To find the most efficient driver in your fleet:
Use the MIN formula within the Fuel Usage report to find the vehicle with the lowest fuel consumed.
The inverse also holds true.
To find the least efficient driver:
Simply use the MAX formula.
Basic Calculations
Here are some basic formulas:
SUM
This basic addition formula within Excel allows you to add all the values within a range.
=SUM($A$1:$A$10)
AVERAGE
Formula that returns the arithmetic mean of all the values within a range. If you want it to include text and conditionals within the mean, then you can use AVERAGEA().
=AVERAGE($A$1:$A$10)
COUNT
As the name gives away, this formula will return the total number of cells that contain a number. If you would simply like to return the count of cells that contain any kind of text then you would use COUNTA().
=COUNT($A$1:$A$10)
MOD and INT Functions
MOD will return the remainder after dividing the first number by the second. As an example, =MOD(10,3) would return 1.
=MOD(DIVIDEND,DIVISOR)
INT will return the integer portion of a number, as an example =INT(10.3) would return 10. This function is a great way to split numbers.
=INT(NUMBER)
How to Use SUM, AVERAGE and COUNT in MyGeotab
SUM, AVERAGE and COUNT can be used to provide a quick overview of values in a MyGeotab custom report without delving into PivotTables. Examples of use:
- Total distance travelled from a trip report
- Average number of infractions or exceptions recorded in a Risk Management report
- Total count of devices in the Vehicles report
MOD & INT should most often be used when looking at dates. As mentioned previously, dates in Excel are stored as numbers with the date portion being the integer and the time the decimal portion.
How to Use MOD and INT
What does that have to do with MOD & INT? By using =INT(DATETIME) you can quite easily retrieve only the date from a DateTime value and thus when comparing values you do not run into the issue of them not matching due to the time being different.
And by using =MOD(DATETIME,1) you are able to extract only the time portion of the DateTime. These two formulas can and should be used in almost every single MyGeotab Report.
Conditional Mathematical Formulas
Formulas can help you evaluate performance. What if you want to know the total mileage driven by your fleet outside of work hours? Count the number of instances in which a driver surpassed 80 mph or 130 kmh? How about total driving duration during work hours for only a subset of your fleet? Luckily for you, Excel has all of that and more covered with conditional mathematical formulas.
SUMIF: The Conditional Value Conundrum
The SUMIF is a formula that blends the IF statement above with the SUM function. It calculates the sum of a range, only if certain criteria is met. For example, let’s assume you want to calculate the total miles driven for trips that were longer than 50 miles.
To do this, you would select the range that has the distances, then add the minimum speed condition as the second argument. See the example below:
=SUMIF(E9:E11,”>”&50)
But what if the criteria range and the sum range are not the same cells much like would be the case with the first scenario? What if you want to know the total mileage driven by your fleet outside of work hours?
Excel allows you to add a third argument to the formula that would be used as the sum range.
In the formula below, the range L9:L11 refers to the cells that specify whether the Trip started during work hours or not while the E9:E11 range refers to the trip distances much like the previous example.
=SUMIF(L9:L11,FALSE,E9:E11)
The formula above basically says, “If it’s true that the trip was driven outside of work hours, then calculate the sum of miles.” The range L9:L11 refers to the cells that specify whether the Trip started during work hours or not. The E9:E11 range refers to the trip distances much like the previous example.
COUNTIF: The Selective Picking Formulation
The COUNTIF formula lets you count specific cells — depending on an established criteria. This is different than SUM. A COUNTIF allows you to gather the total number of incidents, where a SUM totals numbers.
For example, let’s say you wanted to know the number of times a driver went over 140 kph. You could set up a formula like the one below, where F9:F11 refers to the speed for each row. The result would be the number of times the incident occurred.
=COUNTIF(F9:F11,”>&140)
SUMIFS and COUNTIFS: The Multiple Criteria Paradigm
So far, the formulas discussed work when there is only one criteria to meet. What happens when you need to set multiple requirements? This is when SUMIFS and COUNTIFS come into play. These formulas are an extension of the basic SUMIFS and COUNTIFS by allowing the specification of multiple criteria and only the rows that meet all the desired criteria will be taken into account.
The syntax for SUMIFS is:
=SUMIFS(sum_range,criteria_range1,criteria1, [criteria_range2,criteria2], …)
While the syntax for COUNTIFS is:
=COUNTIFS,(criteria_range1,criteria1, [criteria_range2,criteria2], …)
In the above syntax examples, the fields with square brackets are optional. It is also important to note that you must at the very least have one criteria set but you can have any number of criteria.
Now that we know how to write these formulas, we can fulfill the requirement mentioned previously, which was to obtain the total driving duration during work hours for only a subset of your fleet. To illustrate: let’s assume that we want to get the total driving duration for the vehicles that are part of the Trucks group in MyGeotab.
In the formula below, H9:H11 refers to the cells that contain each trip duration, this is what will be summed when all the criteria is met. The cells C9:C11 refer to the vehicle’s groups, here we check against Trucks, and finally the cells L9:L11 refer to the work hours, much like in an earlier example.
=SUMIFS(H9:H11,C9:C11,”Trucks”,L9:L11,TRUE)
There are many other ways you can manipulate strings. Here is a good resource to help immerse yourself in all things Excel: Mr. Excel.
Summary
In fleet management especially, custom fleet management reporting is key to unlocking more value from your data. Getting to know your data better can help you achieve those fleet safety, compliance, or productivity goals.
In this post, I’ve barely scratched the surface. Excel has so much more to offer. There are hundreds of functions that you can use in Excel. Combine that with the ability to create your own custom formulas, Excel becomes a powerful management tool.
Author: Mauricio, Engineering Support Manager – Geotab Inc
Download our industry-related brochures:
Construction Vertical Brochure| Courier and Delivery Vertical Brochure | Field Sales & Services Vertical Brochure | Food and Beverage Vertical Brochure | Government Vertical Brochure | Mobility Transport Vertical Brochure | Oil Gas & Mining Vertical Brochure | Police and First Responder Vertical Brochure | Towing Vertical Brochure | Transportation and logistic Vertical Brochure | Utilities Vertical Brochure
